Thread: FETCH ALL positions cursor strangely?

I'm using cursors a fair amount in an app I'm doing now and one bit of
PostgreSQL behavior has puzzled me for a while.

"FETCH n FROM cursor" returns n rows and positions the cursor n rows
ahead of its current position. That makes sense to me.  However, if
there are n rows in the query, "FETCH ALL FROM cursor" will return n
rows, but seemingly positions the cursor n+1 rows ahead of its current
position, as demonstrated in the following transcript.

This doesn't seem to be documented in the MOVE or FETCH references; is
it intentional?  If so, what's the rationale?  It's easy enough to work
around, but it seems like a bug to me.

Thanks,
Bill Gribble

Welcome to psql 7.3.2, the PostgreSQL interactive terminal.

Type:  \copyright for distribution terms
       \h for help with SQL commands
       \? for help on internal slash commands
       \g or terminate with semicolon to execute query
       \q to quit

equitest=# select version();
                                version
------------------------------------------------------------------------
 PostgreSQL 7.3.2 on i386-pc-linux-gnu, compiled by GCC gcc (GCC) 3.2.3
(1 row)

equitest=# create table t(foo int4);
CREATE TABLE
equitest=# insert into t values (1);
INSERT 23482866 1
equitest=# insert into t values (2);
INSERT 23482867 1
equitest=# select * from t
equitest-#
equitest=# select * from t ;
 foo
-----
   1
   2
(2 rows)

equitest=# begin ;
BEGIN
equitest=# declare c cursor for select * from t;
DECLARE CURSOR
equitest=# fetch 2 from c;
 foo
-----
   1
   2
(2 rows)

equitest=# move backward 2 in c;
MOVE 1
equitest=# fetch 2 from c;
 foo
-----
   1
   2
(2 rows)

equitest=# move backward 2 in c;
MOVE 1
equitest=# fetch all from c;
 foo
-----
   1
   2
(2 rows)

equitest=# move backward 2 in c;
MOVE 2
equitest=# fetch all from c;
 foo
-----
   2
(1 row)

equitest=# move backward 3 in c;
MOVE 2
equitest=# fetch all from c;
 foo
-----
   1
   2
(2 rows)

Bill Gribble <grib@linuxdevel.com> writes:
> "FETCH n FROM cursor" returns n rows and positions the cursor n rows
> ahead of its current position. That makes sense to me.  However, if
> there are n rows in the query, "FETCH ALL FROM cursor" will return n
> rows, but seemingly positions the cursor n+1 rows ahead of its current
> position, as demonstrated in the following transcript.

The cursor can be positioned before the first row, on some row, or after
the last row.  FETCH always steps one position in the target
direction before beginning to return rows.  FETCH ALL is always going
to leave you positioned after the last row (not on the last row, as you
end up if you FETCH exactly as many rows as there are).

I'm not sure how well this is explained in the docs, but the behavior is
sensible given that model, and I believe it is per SQL spec.

If you'd like to suggest better wording for the docs, feel free ...

            regards, tom lane