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)