Re: Function, that returns set of 2 tables columns

["Willy-Bas Loos" <willybas@xxxxxxxxx>]

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

On 2/13/07, Dmitriy Chumack <saint.d.a@xxxxxxxxx> wrote:

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@xxxxxxxxx


---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
       choose an index scan if your joining column's datatypes do not
       match