From: | Merlin Moncure <mmoncure(at)gmail(dot)com> |
---|---|
To: | Thom Brown <thombrown(at)gmail(dot)com> |
Cc: | pgsql-general(at)postgresql(dot)org |
Subject: | Re: Functions returning multiple rowsets |
Date: | 2009-09-28 18:17:24 |
Message-ID: | b42b73150909281117s57ccf24dqac8e122c504c8015@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Vasiliy G Tolstov | 2009-09-28 18:30:31 | Re: postgresql error |
Previous Message | Merlin Moncure | 2009-09-28 18:05:16 | Re: Functions returning multiple rowsets |