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