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 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
