One thing I like about Microsoft SQL is you can write a sproc that does: SELECT * FROM TableA SELECT * FROM TableB And in .NET, you'll have a DataSet object with two DataTables, one for each table. Do either of the techniques outlined below provided this functionality, though I suppose in .NET you'd be using the NpgSql adapter instead.. Mike On Mon, Sep 28, 2009 at 11:17 AM, Merlin Moncure <mmoncure@xxxxxxxxx> wrote: > 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 > -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
