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@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
