0

I need to test a psql-function (sp_create_refcursors), which returns 3 cursors. Specifically, I need to see what data each of the 3 cursors "contains".

The following script contains the boilerplate-code to perform the test:

DO $$                    

BEGIN                    

 select sp_create_refcursors(); 

 //At this point I somehow need to get the names of the cursors returned by function sp_create_refcursors()

 FETCH ALL IN "? 1";
 FETCH ALL IN "?? 2";
 FETCH ALL IN "??? 3";

END;                     
$$;

The problem is that I don't know the names of the cursors returned by function sp_create_refcursors(), i.e. I don't know what to use instead of "?", "??" and "???".

I know that - in principle - this problem could be solved by redesigning the function and passing all cursor names as parameters to get predefined cursor names (see http://www.sqlines.com/postgresql/how-to/return_result_set_from_stored_procedure).

However, the function is off-limits to me, i.e. it is exogenous to what I am doing.

Hence, I need an alternative method to get the names of the three cursors returned by the function. - How can I do this?

Thomas_SO
  • 535
  • 3
  • 13

1 Answers1

1

You can cast a refcursor to text to get its name.

Here is a small self-contained example.

This function returns two refcursors:

CREATE FUNCTION retref(
   OUT c1 refcursor,
   OUT c2 refcursor
) LANGUAGE plpgsql AS
$$DECLARE
   xyz CURSOR FOR SELECT 42;
   abc CURSOR FOR SELECT 'value';
BEGIN
   OPEN xyz;
   OPEN abc;
   c1 := xyz;
   c2 := abc;
END;$$;

This is how I use it:

BEGIN;

WITH x AS (
   SELECT * FROM retref()
)
SELECT c1::text, c2::text
FROM x;

 c1  | c2  
-----+-----
 xyz | abc
(1 row)

FETCH ALL FROM xyz;

 ?column? 
----------
       42
(1 row)

FETCH ALL FROM abc;

 ?column? 
----------
 value
(1 row)

COMMIT;
Laurenz Albe
  • 129,316
  • 15
  • 96
  • 132