Re: FETCH FORWARD 0 and "cursor can only scan forward" error

Hi Trigve,

Il 04/09/2013 15:06, Trigve Siver ha scritto:
> Hi,
> I'm on PostgreSQL 9.2.2 and trying to use no scroll cursor for some data fetch. But when moving cursor ahead one record and the fetching the actual record the error "cursor can only scan forward" is returned. I don't know if I'm doing something wrong but I don't think I'm going backward with cursor. Here is the code that demonstrate it
>
> BEGIN;
>
> DECLARE CUR_1 NO SCROLL CURSOR WITHOUT HOLD FOR SELECT * FROM GENERATE_SERIES(1, 2);
>
> FETCH FORWARD 0 FROM CUR_1; -- 0
>
> MOVE FORWARD FROM CUR_1;
>
> FETCH FORWARD 0 FROM CUR_1; -- 1
>
> ABORT;
>
> The line marked as "-- 0" is working OK, the line marked as "-- 1" is throwing error:
>
> ERROR: cursor can only scan forward
> HINT: Declare it with SCROLL option to enable backward scan.
>
> ********** Error **********
>
> ERROR: cursor can only scan forward
> SQL state: 55000
> Hint: Declare it with SCROLL option to enable backward scan.
>
> I want to iterate all records with cursor from beginning to end. This sample could be rewritten using FETCH FORWARD 1 ... without using MOVE but I'm interested with solution which throws error.

When you fetch a record you move inherently the cursor to the next
position relative to the last fetched record. Consider this example:

postgres=# BEGIN;
BEGIN
postgres=# DECLARE CUR_1 CURSOR WITHOUT HOLD FOR SELECT * FROM
GENERATE_SERIES(1, 10);
DECLARE CURSOR
postgres=# FETCH FORWARD 1 FROM CUR_1;
generate_series
-----------------
1
(1 row)

postgres=# FETCH FORWARD 1 FROM CUR_1;
generate_series
-----------------
2
(1 row)

postgres=# FETCH FORWARD 0 FROM CUR_1;
generate_series
-----------------
2
(1 row)

If you specify "FORWARD 0" you move ahead of zero places instead of one;
therefore you obtain the same record *that was yet fetched* by the
previous FETCH statement. If the cursor is declared with the NO ROLL
options, this operation is forbidden, and an error is raised, as in your
case.

Giuseppe.

--
Giuseppe Broccolo - 2ndQuadrant Italy
PostgreSQL Training, Services and Support
giuseppe(dot)broccolo(at)2ndQuadrant(dot)it | www.2ndQuadrant.it