Postgres Pro
Downloads
Home   >   mailing lists

Thread: avoid the creating the type for setof

avoid the creating the type for setof

From
John Fabiani
Date:
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


Johnf

Re: avoid the creating the type for setof

From
Thomas Kellerer
Date:
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

Re: avoid the creating the type for setof

From
Adrian Klaver
Date:
On Friday, December 30, 2011 6:26:19 am John Fabiani wrote:
> 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 ...

http://www.postgresql.org/docs/9.0/interactive/plpgsql-declarations.html#PLPGSQL-DECLARATION-PARAMETERS

39.3.1. Declaring Function Parameters

Search for RETURNS TABLE

> 
> return
> 
> 
> Johnf

-- 
Adrian Klaver
adrian.klaver@gmail.com

Re: avoid the creating the type for setof

From
John Fabiani
Date:
On Friday, December 30, 2011 06:26:19 AM John Fabiani wrote:
> 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
> 
> 
> Johnf

Thanks all - I knew I had seen it in the past.

Johnf