From: | Mike Christensen <mike(at)kitchenpc(dot)com> |
---|---|
To: | Merlin Moncure <mmoncure(at)gmail(dot)com> |
Cc: | Thom Brown <thombrown(at)gmail(dot)com>, pgsql-general(at)postgresql(dot)org |
Subject: | Re: Functions returning multiple rowsets |
Date: | 2009-09-28 19:31:55 |
Message-ID: | 7aa638e00909281231s4a57cb65i6f8e3aa58cc8616@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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(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
>
From | Date | Subject | |
---|---|---|---|
Next Message | Pavel Stehule | 2009-09-28 19:42:42 | Re: Functions returning multiple rowsets |
Previous Message | Martin Gainty | 2009-09-28 19:10:06 | Re: computed values in plpgsql |