Re: FETCH FORWARD 0 and "cursor can only scan forward" error - Mailing list pgsql-general

>________________________________
> From: Giuseppe Broccolo <giuseppe.broccolo@2ndquadrant.it>
>To: pgsql-general@postgresql.org
>Sent: Wednesday, September 4, 2013 6:26 PM
>Subject: Re: [GENERAL] 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
andthe fetching the actual record the error "cursor can only scan forward" is returned. I don't know if I'm doing
somethingwrong but I don't think I'm going backward with cursor. Here is the code that demonstrate it  BEGIN; DECLARE
CUR_1NO SCROLL CURSOR WITHOUT HOLD FOR SELECT * FROM GENERATE_SERIES(1, 2); FETCH FORWARD 0 FROM CUR_1; -- 0 MOVE
FORWARDFROM 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
toend. This sample could be rewritten using FETCH FORWARD 1 ... without using MOVE but I'm interested with solution
whichthrows 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.
>


Thanks for reply,
yes I know that when specifying "FORWARD 0" that no move is done and actual record is returned. But my question is why
itis forbidden by "NO SCROLL"? Does it mean that FORWARD 0 is "marked" as nonsequential operation (does it move back or
why?)aswritten in documentation that NO SCROLL cursor "... cannot be used to retrieve rows in a nonsequential
fashion..."?Now when looking at the documentation from FETCH command "...The cursor should be declared with the SCROLL
optionif one intends to use any variants of FETCH other than FETCH NEXT or FETCH FORWARD with a *positive count*.",
thereis stated that only positive count could be used with NO SCROLL. But why not also 0? 
Because how I get it is that when I make FETCH FORWARD  1 cursor will move to the next record, and returns it. When I
thencall FETCH FORWARD 0 it should only return the actual row cursor is at, no move or whatsoever. Or am I mistaken
somehow?


Thank you   


>
>
>Giuseppe.
>
>--
Giuseppe Broccolo - 2ndQuadrant Italy
PostgreSQL Training, Services and Support giuseppe.broccolo@2ndQuadrant.it | www.2ndQuadrant.it
>


Trigve