Thread: Function, that returns set of 2 tables columns

Hi *

  I need to write a function, that returns a set of all columns from 2
  tables.

  e.g. I create such a function:

    CREATE OR REPLACE FUNCTION func(val_ int8)
      RETURNS SETOF record AS
    $BODY$
    DECLARE
        i record;
    BEGIN

    for i in select * from "Table1", "Table2"
    loop
       return next i;
    end loop;

    return;

    END;
    $BODY$
      LANGUAGE 'plpgsql' VOLATILE;
    ALTER FUNCTION func(val_ int8) OWNER TO postgres;

  But when I try to call it like this:

    SELECT * FROM func1(10);

  I have an error:

      ERROR:  a column definition list is required for functions returning
      "record"

  This two tables have about 20 columns together, so I don't want list
  them each I call this function. Can I achieve this in some other
  (right) way?

  P.S. I don't want to create a specific type for this purpose, but
  if there is no other way, I should.

Thanks in advance.

--
Best regards,
 Dmitriy Chumack           mailto:saint.d.a@gmail.com

am  Tue, dem 13.02.2007, um 15:46:19 +0200 mailte Dmitriy Chumack folgendes:
> Hi *
>
>   I need to write a function, that returns a set of all columns from 2
>   tables.
>
>   e.g. I create such a function:
>
>     CREATE OR REPLACE FUNCTION func(val_ int8)
>       RETURNS SETOF record AS
>     $BODY$
>     DECLARE
>         i record;
>     BEGIN
>
>     for i in select * from "Table1", "Table2"

Use something like:

select * from table1 union all select * from table2


Andreas
--
Andreas Kretschmer
Kontakt:  Heynitz: 035242/47150,   D1: 0160/7141639 (mehr: -> Header)
GnuPG-ID:   0x3FFF606C, privat 0x7F4584DA   http://wwwkeys.de.pgp.net

You need to specify and cast explicitly from your calling statement: SELECT * FROM func1(10) AS (col1 smallint, col2 bigint, col3 date);

Tuesday, February 13, 2007, 3:55:36 PM, Andreas Kretschmer:

> am  Tue, dem 13.02.2007, um 15:46:19 +0200 mailte Dmitriy Chumack folgendes:
>> Hi *
>>
>>   I need to write a function, that returns a set of all columns from 2
>>   tables.
>>
>>   e.g. I create such a function:
>>
>>     CREATE OR REPLACE FUNCTION func(val_ int8)
>>       RETURNS SETOF record AS
>>     $BODY$
>>     DECLARE
>>         i record;
>>     BEGIN
>>
>>     for i in select * from "Table1", "Table2"

> Use something like:

> select * from table1 union all select * from table2


> Andreas


But how this solve my problem? I don't need standalone select, I need
this select to be wrapped in function, and function has to return some
type.


--
Best regards,
 Dmitriy                            mailto:saint.d.a@gmail.com

am  Tue, dem 13.02.2007, um 16:09:16 +0200 mailte Dmitriy Chumack folgendes:
> Tuesday, February 13, 2007, 3:55:36 PM, Andreas Kretschmer:
>
> > am  Tue, dem 13.02.2007, um 15:46:19 +0200 mailte Dmitriy Chumack folgendes:
> >> Hi *
> >>
> >>   I need to write a function, that returns a set of all columns from 2
> >>   tables.
> >>
> >>   e.g. I create such a function:
> >>
> >>     CREATE OR REPLACE FUNCTION func(val_ int8)
> >>       RETURNS SETOF record AS
> >>     $BODY$
> >>     DECLARE
> >>         i record;
> >>     BEGIN
> >>
> >>     for i in select * from "Table1", "Table2"
>
> > Use something like:
>
> > select * from table1 union all select * from table2
>
>
> > Andreas
>
>
> But how this solve my problem? I don't need standalone select, I need
> this select to be wrapped in function, and function has to return some
> type.

Do you need the columns from both tables in one single column? Then you
have to join this tables, i don't know how you want to join this tables.

What i meant was:

you can use this:

for i in select * from "Table1" union all select * from "Table2" loop
  -- do something
end loop;

within a pl/pgsql-function.



Andreas
--
Andreas Kretschmer
Kontakt:  Heynitz: 035242/47150,   D1: 0160/7141639 (mehr: -> Header)
GnuPG-ID:   0x3FFF606C, privat 0x7F4584DA   http://wwwkeys.de.pgp.net

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

On 02/13/07 07:46, Dmitriy Chumack wrote:
> Hi *
>
>   I need to write a function, that returns a set of all columns from 2
>   tables.
[snip]
>     for i in select * from "Table1", "Table2"
>     loop
>        return next i;
>     end loop;
[snip]
>
>   This two tables have about 20 columns together, so I don't want list
>   them each I call this function. Can I achieve this in some other
>   (right) way?
>
>   P.S. I don't want to create a specific type for this purpose, but
>   if there is no other way, I should.

What do these tables join on?

Creating a view might work.

CREATE VIEW V_SIDE_BY_SIDE AS
    SELECT T1.*, T2.*
    FROM TABLE1 T1,
         TABLE2 T2
    WHERE T1.PK = T2.PK;

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.6 (GNU/Linux)

iD8DBQFF0ck4S9HxQb37XmcRAjPEAJ9qaHwa5mbKiFVqcfEZbPXIX7GmIwCgzvvs
zCPM45xZ590kv2xXnIoSsqM=
=NkbJ
-----END PGP SIGNATURE-----

>Creating a view might work.
Yeah, if all you use is SELECT, you should probably use a view, then you don't need to specify the output columns in the calling statement, only a WHERE clause.
Othrewise, it's the same thing:
SELECT * FROM myview WHERE field1=10;

Views are transparant in postgresql, so you need not worry that at first your backend will load the whole view and then filter your results (performance-wise).