Functions returning multiple rowsets

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

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

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

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

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.