Received: from candle.pha.pa.us (216-55-132-35.dsl.san-diego.abac.net [216.55.132.35]) by postgresql.org (Postfix) with ESMTP id 126CB474E6D for ; Mon, 8 Apr 2002 23:34:15 -0400 (EDT) Received: (from pgman@localhost) by candle.pha.pa.us (8.11.6/8.10.1) id g393YFE14191; Mon, 8 Apr 2002 23:34:15 -0400 (EDT) From: Bruce Momjian Message-Id: <200204090334.g393YFE14191@candle.pha.pa.us> Subject: Re: [GENERAL] PL/pgsql return resultset/cursor? In-Reply-To: <3C9CA201.6D10A1E9@phc.net> To: Richard Emberson Date: Mon, 8 Apr 2002 23:34:15 -0400 (EDT) Cc: PostgreSQL-documentation X-Mailer: ELM [version 2.4ME+ PL97 (25)] MIME-Version: 1.0 Content-Transfer-Encoding: 7bit Content-Type: text/plain; charset=US-ASCII X-Archive-Number: 200204/7 X-Sequence-Number: 1332 I have added an FAQ item and improved the documentation. Seems the official current docs aren't being built regularly, (last April 3), so I pointed to my web copy in the FAQ: 4.25) How do I return multiple rows or columns from a function? You can return result sets from PL/pgSQL functions using refcursors. See http://candle.pha.pa.us/main/writings/pgsql/sgml/plpgsql-cursors.html, section 23.7.3.3. --------------------------------------------------------------------------- Richard Emberson wrote: > Thanks for the example. Whoever maintains the documentation ought to put such > an > example in the refcursor section. > > Richard > > > Bruce Momjian wrote: > > > Jan Wieck wrote: > > > Richard Emberson wrote: > > > > > > > > Is it possible to return a result-set or cursor from a PL/pgsql > > > > procedure, like > > > > > > > > CREATE OR REPLACE FUNCTION foo() > > > > RETURNS AS ' > > > > BEGIN > > > > RETURN SELECT * from FOO; > > > > END; > > > > ' LANGUAGE 'plpgsql'; > > > > > > > > If you open a cursor in a procedure, it gets closed when the procedure > > > > exits, right? > > > > > > Cursors get closed in PostgreSQL when you close them or when > > > the transaction ends. > > > > > > Look at the refcursor data type (new in v7.2) and use > > > transactions. > > > > Here is a sample: > > > > create table aa(a int, b int, c int); > > create function f() returns refcursor as ' > > declare > > r refcursor; > > begin > > open r for select * from aa; > > return r; > > end;' language 'plpgsql'; > > > > -- > > Bruce Momjian | http://candle.pha.pa.us > > pgman@candle.pha.pa.us | (610) 853-3000 > > + If your life is a hard drive, | 830 Blythe Avenue > > + Christ can be your backup. | Drexel Hill, Pennsylvania 19026 > > > > ---------------------------(end of broadcast)--------------------------- > > TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org > > -- Bruce Momjian | http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 853-3000 + If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup. | Drexel Hill, Pennsylvania 19026