PL/pgsql return resultset/cursor?

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 <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.

Jan

--

#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me. #
#================================================== JanWieck@Yahoo.com #

_________________________________________________________
Do You Yahoo!?
Get your free @yahoo.com address at http://mail.yahoo.com

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 <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

Does anyone know how to return more than one value from pgsql script?
In the archieve I've found some email about it. Somebody suggested to create
a temporary table?
Could any one make a small smaple script for me? I have postgesql 7.1.

Kind Regards,
Yuri