FETCH ALL positions cursor strangely?

From: Bill Gribble <grib(at)linuxdevel(dot)com>
To: pgsql-general(at)postgresql(dot)org
Subject: FETCH ALL positions cursor strangely?
Date: 2003-05-08 15:32:04
Message-ID: 1052407923.1322.1.camel@oc-demo.internal.billgribble.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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)

Responses

Browse pgsql-general by date

  From Date Subject
Next Message scott.marlowe 2003-05-08 15:39:14 Re: Creating functions and triggers
Previous Message Stephan Szabo 2003-05-08 15:13:36 Re: indices - used by which user ?