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

Re: Proposal: real procedures again (8.4)

From: David Fetter <david(at)fetter(dot)org>
To: 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>,Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com>,PostgreSQL-development <pgsql-hackers(at)postgresql(dot)org>
Subject: Re: Proposal: real procedures again (8.4)
Date: 2007-10-29 23:42:54
Message-ID: 20071029234254.GN14638@fetter.org (view raw or flat)
Thread:
Lists: pgsql-hackers
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;

Cheers,
David.
-- 
David Fetter <david(at)fetter(dot)org> http://fetter.org/
Phone: +1 415 235 3778  AIM: dfetter666  Yahoo!: dfetter
Skype: davidfetter      XMPP: david(dot)fetter(at)gmail(dot)com

Remember to vote!
Consider donating to Postgres: http://www.postgresql.org/about/donate

In response to

Responses

pgsql-hackers by date

Next:From: Meredith L. PattersonDate: 2007-10-30 02:02:09
Subject: install-strip causes dyld errors on OS X
Previous:From: Simon RiggsDate: 2007-10-29 23:03:46
Subject: Recovery of Multi-stage WAL actions

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