[GENERAL] scrollable cursor in functions

From: Cedric Boudin <cedric(at)dreamgnu.com>
Date: Fri Nov 30 2007 - 09:26:02 EST

Dear members of the list,

on a server 8.1.9 I try to do this:
++++++++++
create or replace function dummy() returns void as
$$
DECLARE liahona SCROLL CURSOR with hold FOR SELECT * FROM album ; BEGIN
--DECLARE liahona SCROLL CURSOR with hold FOR SELECT * FROM album ;

• Set up a cursor:
• Fetch the first 5 rows in the cursor liahona: FETCH FORWARD 5 FROM liahona;

FETCH PRIOR FROM liahona;

• Close the cursor and end the transaction: CLOSE liahona; COMMIT;

end;
$$ language plpgsql;

---

I do get:
++++++++++

ERROR: syntax error at or near "CURSOR" CONTEXT: invalid type name "SCROLL CURSOR with hold FOR SELECT * FROM album"
compile of PL/pgSQL function "dummy" near line 1

---

If I put the cursor declaration in the begin->end block it does not matter. If I do:
+++++++++++
BEGIN work;

DECLARE liahona SCROLL CURSOR with hold FOR SELECT * FROM album ;
-- Set up a cursor:

• Fetch the first 5 rows in the cursor liahona: FETCH FORWARD 5 FROM liahona;

FETCH PRIOR FROM liahona;

• Close the cursor and end the transaction: CLOSE liahona; COMMIT WORK;

  ---

  I do get the expected results. Thus I conclude that the scrollable cursors are enabled on the server. I've seen some posts in the mailing list archives about some related problems like *BUG #2970 -Are scrollable cursor forbidden in *PL/pgSQL? I did not see such a restriction explicitly in the doc. -If not, am I doing something wrong? -If not, is it a bug?

have a nice day

cedric

**

---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to

       choose an index scan if your joining column's datatypes do not
       match

Received on Fri Nov 30 09:27:04 2007