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