Skip site navigation (1) Skip section navigation (2)

Re: Proposal: real procedures again (8.4)

From: "Pavel Stehule" <pavel(dot)stehule(at)gmail(dot)com>
To: "David Fetter" <david(at)fetter(dot)org>
Cc: "Gregory Stark" <stark(at)enterprisedb(dot)com>, "Hannu Krosing" <hannu(at)skype(dot)net>, "Josh Berkus" <Josh(dot)Berkus(at)sun(dot)com>, "Merlin Moncure" <mmoncure(at)gmail(dot)com>, "Tom Lane" <tgl(at)sss(dot)pgh(dot)pa(dot)us>, PostgreSQL-development <pgsql-hackers(at)postgresql(dot)org>
Subject: Re: Proposal: real procedures again (8.4)
Date: 2007-10-30 08:48:24
Message-ID: 162867790710300148g7d6c17c3jdee6fefb68031cae@mail.gmail.com (view raw or flat)
Thread:
Lists: pgsql-hackers
2007/10/30, David Fetter <david(at)fetter(dot)org>:
> On Mon, Oct 29, 2007 at 07:32:11PM -0300, Alvaro Herrera wrote:
> > Gregory Stark wrote:
> > > "Hannu Krosing" <hannu(at)skype(dot)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

In response to

Responses

pgsql-hackers by date

Next:From: David FetterDate: 2007-10-30 13:15:42
Subject: Re: Proposal: real procedures again (8.4)
Previous:From: Pavel StehuleDate: 2007-10-30 08:40:56
Subject: Re: Proposal: real procedures again (8.4)

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group