Re: Proposal: real procedures again (8.4)

2007/10/30, David Fetter <david@fetter.org>:
> On Mon, Oct 29, 2007 at 07:32:11PM -0300, Alvaro Herrera wrote:
> > Gregory Stark wrote:
> > > "Hannu Krosing" <hannu@skype.net> writes:
> > >
> > > > What I was referring to, was a "code cleanup" of libpq several
> > > > years ago, when someone (maybe Bruce IIRC) removed ability to
> > > > accept multiple recordsets from backend altogether, on the basis
> > > > that it is not used anyway.
> > >
> > > You can still receive multiple record sets just fine using libpq.
> > > psql doesn't handle them but they're there. When I was doing the
> > > concurrent psql patch I also had it handling multiple record sets.
> > >
> > > Something else you may be thinking of, I don't think it's legal to
> > > do queries like "select 1 ; select 2" in the new protocol. That
> > > was legal in the old protocol.
> >
> > I think the cool thing that Josh Berkus wants is
> >
> > return query select a, b, c from foo;
> > return query select d, e, f from bar;
> >
> > in a plpgsql function, and getting two result sets (I'm fuzzy about the
> > exact syntax but you get the idea).  Can this be done at all?
>
> Based on the example in TFM for PL/PgSQL:
>
> BEGIN;
> CREATE TABLE foo(foo_id SERIAL PRIMARY KEY, foo_text TEXT);
> CREATE TABLE bar(bar_id SERIAL PRIMARY KEY, bar_text TEXT);
> INSERT INTO foo(foo_text) VALUES ('a'),('b'),('c'),('d');
> INSERT INTO bar(bar_text) VALUES ('e'),('f'),('g'),('h');
> CREATE FUNCTION wtf(refcursor, refcursor)
> RETURNS SETOF refcursor
> LANGUAGE plpgsql
> AS $$
> BEGIN
>     OPEN $1 FOR SELECT * FROM foo;
>     RETURN NEXT $1;
>     OPEN $2 FOR SELECT * FROM bar;
>     RETURN NEXT $2;
> END;
> $$;
> SELECT * FROM wtf('a','b');
> FETCH all FROM a;
> FETCH all FROM b;
> ROLLBACK;
>

disavantages
* it is transaction based, so you have to wait for first set untill
function is completed (it's avantage too, but you havn't choice now)

* too much lines, for simple task