Re: [GENERAL] scrollable cursor in functions

From: Pavel Stehule <pavel.stehule(at)gmail.com>
Date: Fri Nov 30 2007 - 09:43:00 EST

Hello

8.1.x doesn't support scrollable cursors in plpgsql. Its supported only in 8.3.

Regards
Pavel Stehule

On 30/11/2007, Cedric Boudin <cedric@dreamgnu.com> wrote:
> 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
>

---------------------------(end of broadcast)---------------------------

TIP 3: Have you checked our extensive FAQ?

               http://www.postgresql.org/docs/faq Received on Fri Nov 30 09:43:58 2007