Re: avoid the creating the type for setof - Mailing list pgsql-sql

John Fabiani wrote on 30.12.2011 15:26:
> Hi,
> I recall somewhere I saw a simple plpgsql function that returned a table with
> more than one record that did not use a defined type or a temp table ( at
> least I think I did).  Is it possible to create such a function that will
> return more than one record and not require a record type or temp table?
>
> something like
>
> return setof record as
> return query select ...
>
> return

Sure:

create or replace function  returns table (f1 text, f2 integer)
as
$$   select col1, col2 from some table;
$$
language sql;

If you are using PL/pgSQL you need to use "RETURN QUERY SELECT ..." inside the function.

For more details see the examples in the manual:
http://www.postgresql.org/docs/current/static/sql-createfunction.html
http://www.postgresql.org/docs/current/static/plpgsql-control-structures.html#PLPGSQL-STATEMENTS-RETURNING

Thomas