scrollable cursor in functions

From: Cedric Boudin <cedric(at)dreamgnu(dot)com>
To: pgsql-general(at)postgresql(dot)org
Subject: scrollable cursor in functions
Date: 2007-11-30 14:26:02
Message-ID: 47501D7A.9030802@dreamgnu.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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

**

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Albe Laurenz 2007-11-30 14:26:29 Re: psql lo_export documentation
Previous Message Martijn van Oosterhout 2007-11-30 14:20:57 Re: postgresql table inheritance