returning ref cursor

Started by Ravi Katkaralmost 16 years ago2 messagesgeneral
Jump to latest
#1Ravi Katkar
Ravi.Katkar@infor.com

Please let me know what's wrong with below code

CREATE LANGUAGE plpgsql;
create or replace FUNCTION test_cu( p_cursor REFCURSOR) returns REFCURSOR
AS $procedure$
BEGIN
open p_cursor FOR
select * from test;

RETURN p_cursor;
END; $procedure$
LANGUAGE plpgsql;

create or replace FUNCTION test_call()
RETURNS VOID
AS $procedure$
DECLARE
c_cursor REFCURSOR;
r_emp test%rowtype;
BEGIN
PERFORM test_cu(c_cursor);
loop
fetch c_cursor into r_emp;
exit when NOT FOUND;
RAISE NOTICE '%',r_emp.aaa;
end loop;
close c_cursor;
RETURN;
END; $procedure$
LANGUAGE plpgsql;

SELECT test_call();

When I execute above code I got below error

ERROR: cursor variable "c_cursor" is null
CONTEXT: PL/pgSQL function "test_call" line 7 at FETCH

********** Error **********

ERROR: cursor variable "c_cursor" is null
SQL state: 22004
Context: PL/pgSQL function "test_call" line 7 at FETCH

Thanks,
Ravi Katkar

#2Merlin Moncure
mmoncure@gmail.com
In reply to: Ravi Katkar (#1)
Re: returning ref cursor

On Mon, May 24, 2010 at 1:44 AM, Ravi Katkar <Ravi.Katkar@infor.com> wrote:

Please let me know what’s wrong with below code
create or replace FUNCTION test_call()

RETURNS VOID

   AS $procedure$

   DECLARE

   c_cursor  REFCURSOR;

give your cursor a name:
c_cursor REFCURSOR default 'a_name';

this name is also how you will use the refcursor from non-plpgsql
fetches later in the transaction if you needed to (in this case you
would likely return the cursor name from the function).

merlin