Re: Bug #664: Strange cursor behaviour with particular

On Sun, 2002-05-12 at 17:58, Tom Lane wrote:
> pgsql-bugs@postgresql.org writes:
> > template1=> move -1 from foo;
> > MOVE 0
>
> Not sure what you expected this to do, but the response should have
> clued you that it didn't do anything.  I suspect you are looking
> for "MOVE BACKWARD 1 FROM foo" ...
>
>             regards, tom lane
>

Really? Though I agree the documentation doesn't seem to mention it, in
my experience, "move -<number> from <cursor>" is equivalent to "move
backward <number> from <cursor>", and the "MOVE 0" merely means that the
new cursor position is 0 (the first row).

Certainly, replacing "move -1 from foo" with "move backward 1 from foo"
here changes nothing:

-----------------------------
template1=> begin work;
BEGIN
template1=> declare foo cursor for select * from nodes_view where
node_id in (select id from nodes where parent in (2));
DECLARE
template1=> fetch 1 from foo;
 resource_id |     resource_creationDate     |
resource_modificationDate   | node_id | node_parent | node_name
-------------+-------------------------------+-------------------------------+---------+-------------+-----------
           4 | 2002-05-11 23:00:24.344979+02 | 2002-05-11
23:00:24.344979+02 |       4 |           2 | foo4
(1 row)

template1=> move backward 1 from foo;
MOVE 0
template1=> fetch 1 from foo;
 resource_id | resource_creationDate | resource_modificationDate |
node_id | node_parent | node_name
-------------+-----------------------+---------------------------+---------+-------------+-----------
(0 rows)

template1=>
--------------------------------------

It seems to me that the second fetch operation should retrieve the same
row as the first fetch.

I apologize if it is my understanding of cursors which is at fault here,
but this seems like a bug to me.

Using a different query on the same database schema things work fine,
though:

------------------------------------
template1=> declare foo cursor for select * from nodes where parent in
(2);
NOTICE:  Closing pre-existing portal "foo"
DECLARE
template1=> fetch 1 from foo;
 id | parent | name
----+--------+------
  4 |      2 | foo4
(1 row)

template1=> move backward 1 from foo;
MOVE 0
template1=> fetch 1 from foo;
 id | parent | name
----+--------+------
  4 |      2 | foo4
(1 row)

template1=>
--------------------------------------------------------------