Обсуждение: avoid the creating the type for setof
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
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
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
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