On Mon, Sep 28, 2009 at 2:05 PM, Merlin Moncure <mmoncure@xxxxxxxxx> wrote: > On Mon, Sep 28, 2009 at 12:49 PM, Thom Brown <thombrown@xxxxxxxxx> wrote: >> Hi, >> >> Is it possible to create a function using 'SQL' as language which could >> return multiple rowsets, such as "SELECT * FROM TABLE1; SELECT * FROM >> TABLE2;" where both results are returned in the output? I know this can be >> done in stored procedures in other RBDMS but can this be done in a function? > > you have a couple of approaches: > *) declare refcursors inside the function and references them later in > the transaction > *) make temp tables > *) arrays: > create function two_sets(_foos out foo[], _bars out bar[]) returns record as > $$ > select array(select foo from foo), array(select bar from bar); > $$ language sql; > > with s as (select * from two_sets()), > foo as (select unnest(_foos) from s), > bar as (select unnest(_bars) from s) > select > (select count(*) from foo) as no_foos, > (select count(*) from bar) as no_bars; I should mention the query above only works in 8.4+. the array approach generally only works as of 8.3 and has limits (don't return billion records). Also, it's not good style (IMO) to name 'with' expressions same as actual tables: with s as (select * from two_sets()), f as (select unnest(_foos) from s), b as (select unnest(_bars) from s) select (select count(*) from f) as no_foos, (select count(*) from b) as no_bars; is cleaner. merlin -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
