From: | "Pavel Stehule" <pavel(dot)stehule(at)gmail(dot)com> |
---|---|
To: | "Cedric Boudin" <cedric(at)dreamgnu(dot)com> |
Cc: | pgsql-general(at)postgresql(dot)org |
Subject: | Re: scrollable cursor in functions |
Date: | 2007-11-30 14:43:00 |
Message-ID: | 162867790711300643n2bb9fa6bvaea1cdac2c307be0@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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(at)dreamgnu(dot)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
>
From | Date | Subject | |
---|---|---|---|
Next Message | Ivan Sergio Borgonovo | 2007-11-30 14:53:01 | Re: PostgresSQL vs Ingress |
Previous Message | Ivan Sergio Borgonovo | 2007-11-30 14:38:33 | Re: postgresql table inheritance |