Postgresql: Get content of cursors returned by function

Question

A psql-function (sp_some_function) returns 2 cursors:

create or replace function sp_some_function() RETURNS SETOF REFCURSOR
as 
$BODY$

  declare 

    c_one   refcursor                   := 'one'         ;
    c_two   refcursor                   := 'two'         ;

  begin

      open c_one   for
        select *
          from TABLE_1;

     return next c_one   ;

     open c_two   for
        select *
          from TABLE_2;

     return next c_two   ;

  return;
end
$BODY$
LANGUAGE PLPGSQL

I want to see what data each of the 2 cursors "contains".

To this end I wrote the following script:

DO $$                    

BEGIN                    

 select sp_some_function(); 


 FETCH ALL IN "one";
 FETCH ALL IN "two";


END;                     
$$;

Running the script results in the following error message:

ERROR:  "one" is not a known variable

I also tried the following:

DO $$                    

BEGIN                    

 select sp_some_function(); 


 FETCH ALL IN c_one;
 FETCH ALL IN c_two;


END;                     
$$;

This resulted in the following error message:

ERROR:  c_one is not a known variable

Finally, I tried the following:

create or replace function sp_some_function() RETURNS SETOF REFCURSOR
as 
$BODY$

  declare 

    c_one   refcursor                   := 'one'         ;
    c_two   refcursor                   := 'two'         ;

  begin

      open c_one   for
        select *
          from TABLE_1;

     FETCH ALL IN c_one;

     return next c_one   ;

     open c_two   for
        select *
          from TABLE_2;



     FETCH ALL IN c_two;  

     return next c_two   ;

  return;
end
$BODY$
LANGUAGE PLPGSQL

This didn't work either.

... How can I get the content of the two cursors?

did you manage to find a answare to this? – Bruno Ferreira Apr 08 '22 at 08:27 — Bruno Ferreira, Apr 08 '22 at 08:27

Laurenz Albe · Answer 1 · 2019-08-08T14:46:03.447

1

Your PL/pgSQL code is wrong.

You cannot use SELECT without INTO in PL/pgSQL.

The best thing for you is to do something like this (untested):

DECLARE
   c refcursor;
   a_row record;
BEGIN
   FOR c IN
      SELECT sp_some_function()
   LOOP
      LOOP
         FETCH c INTO a_row;
         EXIT IF NOT FOUND;
         /* do something with the result row */
      END LOOP;
   END LOOP;
END;

edited Aug 08 '19 at 14:46

answered Aug 08 '19 at 14:01

Laurenz Albe

209,280
17
206
263

Thank you for your answer ... unfortunately, these are unbound cursors so I'm getting the following error message: "cursor FOR loop must use a bound cursor variable" – Thomas_SO Aug 08 '19 at 14:24
Right, the cursor is already opened. I have updated the answer. – Laurenz Albe Aug 08 '19 at 14:46

score -1 · Answer 2 · answered Aug 08 '19 at 13:47

-1

Please follow the below method to get the output

execute the below function

BEGIN;        -- begin Transaction             

 select sp_some_function();

here you will get the refcusor value copy that and then fetch the data from it.. for eg:

 FETCH ALL IN "refcursor_value1";  -- Execute it seperately to see the result
 FETCH ALL IN "refcursor_value2";  -- Execute it seperately to see the result


END;  -- end Transaction

answered Aug 08 '19 at 13:47

Ajay

764
4
12

Unfortunately, this doesn't work ... I'm not getting the refcursor value. – Thomas_SO Aug 08 '19 at 13:53
The question is about PL/pgSQL. – Laurenz Albe Aug 08 '19 at 13:57

Postgresql: Get content of cursors returned by function

2 Answers2