return X number of refcursors from a function

Derek Liang wrote:

I tried to use the following code to retrieve the content of table1 4
times (in my application, the total number of refcursors that will be
returned is determined by the data in the database). I am getting the
error message says "ERROR: cursor "<unnamed portal 2>" already in
use".

Thank you in advance!

dl

--Start of the code
--DROP FUNCTION myfunc(int);

CREATE FUNCTION myfunc(int) RETURNS SETOF refcursor AS $$
DECLARE i int;
r refcursor;
BEGIN
i := $1;
WHILE i>0 LOOP
RAISE NOTICE 'loop count %;', i;
i := i-1;

OPEN r FOR SELECT * FROM table1;
RETURN NEXT r;
END LOOP;

RETURN;
END;
$$ LANGUAGE plpgsql;

BEGIN;
SELECT * FROM myfunc(4);
COMMIT;

The problem is that the name of the cursor "r" remains the same
throughout the execution of your function, while the name of a
cursor is unique per session.

You can name a cursor by assigning a string to the refcursor variable.

Your function will work if you add for example the following line
immediately before the "OPEN" statement:

r := 'cursor' || ($1 - i);

Yours,
Laurenz Albe