Re: [GENERAL] PL/pgsql return resultset/cursor?
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, likeCREATE OR REPLACE FUNCTION foo()
RETURNS <WHAT_TYPE> 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 19026Import Notes
Reply to msg id not found: 3C9CA201.6D10A1E9@phc.net
Peter Eisentraut wrote:
Bruce Momjian writes:
I have added an FAQ item and improved the documentation. Seems the
official current docs aren't being built regularly, (last April 3),Probably because the markup is temporarily broken.
Oh, is that why. I just fixed it.
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.Please do not publish links to the development docs.
How do I get around that? I would have to duplicate the doc information
in the FAQ; seems ugly.
--
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 19026Import Notes
Reply to msg id not found: Pine.LNX.4.30.0204090015440.685-100000@peter.localdomain | Resolved by subject fallback
Bruce Momjian writes:
I have added an FAQ item and improved the documentation. Seems the
official current docs aren't being built regularly, (last April 3),
Probably because the markup is temporarily broken.
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.
Please do not publish links to the development docs.
--
Peter Eisentraut peter_e@gmx.net
There is updated version of Russian translation FAQ in attachment.
With best wishes,
Victor Vislobokov
Perm, Russia
Attachments:
Applied. Thanks.
---------------------------------------------------------------------------
Viktor Vislobokov wrote:
There is updated version of Russian translation FAQ in attachment.
With best wishes,
Victor Vislobokov
Perm, Russia
[ application/x-gzip is not supported, skipping... ]
--
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