Обсуждение: returning ref cursor

Поиск
Список
Период
Сортировка

returning ref cursor

От
Ravi Katkar
Дата:

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

 

 

 

 

Re: returning ref cursor

От
Merlin Moncure
Дата:
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