Re: Functions returning multiple rowsets

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(at)gmail(dot)com> wrote:
>> On Mon, Sep 28, 2009 at 2:05 PM, Merlin Moncure
>> <mmoncure(at)gmail(dot)com> wrote:
>>> On Mon, Sep 28, 2009 at 12:49 PM, Thom Brown <thombrown(at)gmail(dot)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(at)postgresql(dot)org)
>> To make changes to your subscription:
>> http://www.postgresql.org/mailpref/pgsql-general
>>
>
> --
> Sent via pgsql-general mailing list (pgsql-general(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>