Обсуждение: return X number of refcursors from a function

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;

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