Re: Functions returning multiple rowsets

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