Re: PL/pgSQL stored procedure returning multiple result sets (SELECTs)?

On Mon, Oct 13, 2008 at 8:09 AM, Vladimir Dzhuvinov <vd(at)valan(dot)net> wrote:
>>> I came across a blog post of yours (
>>> http://okbob.blogspot.com/2007/11/stacked-recordset-multirecordset.html
>>> ) as well as several list posts indicating that multiple result sets
>>> might be in the working. Should I check the situation again when 8.4 is
>>> released?
>
>> I have only very raw prototype, so I am sure, so this feature will not
>> be in 8.4, and I am not sure about 8.5. It's nice feature, but I am
>> not force to complete and clean code, and I am not able create patch.
>> If you would do it, I am, with pleasure, send you source code, that
>> allows multirecord sets.
>
> Yes, I'll be glad to examine your patch. At least to get an idea of
> what's involved in implementing multiple result sets.

Stored procedure support is a pretty complicated feature. They differ
with functions in two major areas:

*) input/output syntax. this is what you are dealing with
*) manual transaction management. stored procedures should allow you
emit 'BEGIN/COMMIT' and do things like vacuum.

IIRC, I don't think there was a consensus on the second point or if it
was ok to implement the syntax issues without worrying about
transactions.

I'll give you two other strategies for dealing with multiple result
sets in pl/pgsql:
*) temp tables: it's very easy to create/dump/drop temp tables and use
them in later transactions. previous to 8.3 though, doing it this way
was a pain because of plan invalidation issues.

*) arrays of composites (8.2+)
create table foo(a int, b int, c int);
create table bar(a text, b text, c text);

pl/sql:
create function foobar(foos out foo[], bars out bar[]) returns record as
$$
select (select array(select foo from foo)),
(select array(select bar from bar));
$$ language sql;

pl/pgsql:
create function foobar(foos out foo[], bars out bar[]) returns record as
$$
begin
foos := array(select foo from foo);
bars := array(select bar from bar);
return;
end;
$$ language plpgsql;

select foos[1].b from foobar();

Customize the above to taste. For example you may want to return the array dims.

By the way, if you are writing client side code in C, you may want to
look at libpqtypes (http://libpqtypes.esilo.com/)...it makes dealing
with arrays and composites on the client sides much easier. For 8.3
though it requires a patched libpq.

merlin