Обсуждение: Functions returning multiple rowsets
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?
Thanks
Thom
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?
Thanks
Thom
Hello 2009/9/28 Thom Brown <thombrown@gmail.com>: > 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? > it is possible, but not directly. You can returns setof refcursors see http://www.postgresql.org/docs/8.2/static/plpgsql-cursors.html regards Pavel Stehule > Thanks > > Thom >
On Mon, Sep 28, 2009 at 12:49 PM, Thom Brown <thombrown@gmail.com> 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; merlin
On Mon, Sep 28, 2009 at 2:05 PM, Merlin Moncure <mmoncure@gmail.com> wrote: > On Mon, Sep 28, 2009 at 12:49 PM, Thom Brown <thombrown@gmail.com> 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
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@gmail.com> wrote: > On Mon, Sep 28, 2009 at 2:05 PM, Merlin Moncure <mmoncure@gmail.com> wrote: >> On Mon, Sep 28, 2009 at 12:49 PM, Thom Brown <thombrown@gmail.com> 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@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-general >
2009/9/28 Mike Christensen <mike@kitchenpc.com>: > 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@gmail.com> wrote: >> On Mon, Sep 28, 2009 at 2:05 PM, Merlin Moncure <mmoncure@gmail.com> wrote: >>> On Mon, Sep 28, 2009 at 12:49 PM, Thom Brown <thombrown@gmail.com> 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@postgresql.org) >> To make changes to your subscription: >> http://www.postgresql.org/mailpref/pgsql-general >> > > -- > Sent via pgsql-general mailing list (pgsql-general@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-general >
On Sep 28, 2009, at 3:31 PM, Mike Christensen wrote: > 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.. I use the NpgSql interface for just this type of transparent .NET stuff, and it works plenty fine for my uses. -Owen > > Mike > > On Mon, Sep 28, 2009 at 11:17 AM, Merlin Moncure > <mmoncure@gmail.com> wrote: >> On Mon, Sep 28, 2009 at 2:05 PM, Merlin Moncure >> <mmoncure@gmail.com> wrote: >>> On Mon, Sep 28, 2009 at 12:49 PM, Thom Brown <thombrown@gmail.com> >>> 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@postgresql.org) >> To make changes to your subscription: >> http://www.postgresql.org/mailpref/pgsql-general >> > > -- > Sent via pgsql-general mailing list (pgsql-general@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-general >
2009/9/28 Merlin Moncure <mmoncure@gmail.com>
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;
merlin
I can see this working as we will be using 8.4.1, although it does seem rather unintuitive and clumsy. I can see there's no straightforward way of achieving multiple result sets in the output. I would have hoped for something like "returns record[]" to denote an array of records or "returns setof table" where table would be a parent database object of every other table. I can work around this problem though, but I imagine it is something many people coming from MSSQL might be looking for.
As for seeking 2 result sets from code without any clever processing, you can just write as many queries as you want in PHP, and the results come out separate result sets in the result array. (e.g. $results[0] = first query, $results[1] = second query etc)
Thanks for the explanation Merlin.