2009/9/28 Mike Christensen <mike@xxxxxxxxxxxxx>: > 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.. > Maybe next year. I found some sources, so I hope so I could to finish my prototype, that can do it. Regards Pavel this exists only in prototype http://okbob.blogspot.com/2007/11/stacked-recordset-multirecordset.html > 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 > -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
