Does anyone know what the "correct" behaviour for a scroll cursor should be
when you've scrolled past the end ?

If you take this SQL for example :


create temp table sometab ( a integer);
insert into sometab values(1);
insert into sometab values(2);
insert into sometab values(3);
begin work;

declare c1 scroll cursor for select * from sometab;
fetch next from c1;
fetch next from c1;
fetch next from c1;
fetch next from c1;
fetch prior from c1;
fetch prior from c1;
fetch prior from c1;




The first 4 fetches work as expected and return 1,2,3, and the 4th fetch
returns no rows as its at the end of the list...

** But ** - when I do the fetch prior, I would have expected it to go back to
the '2' row, not the '3' row...

ie - under postgresql it appears we've scrolled *past* the last row and need
an additional fetch to get back to our last row..



For reference - heres what I get as output :


CREATE TABLE
INSERT 32429 1
INSERT 32430 1
INSERT 32431 1
BEGIN
DECLARE CURSOR
a
---
1
(1 row)

a
---
2
(1 row)

a
---
3
(1 row)

a
---
(0 rows)

a
---
3
(1 row)

a
---
2
(1 row)

a
---
1
(1 row)






TIA
--
Mike Aubury

Aubit Computing Ltd is registered in England and Wales, Number: 3112827
Registered Address : Clayton House,59 Piccadilly,Manchester,M1 2AQ

Search Discussions

  • Tom Lane at Apr 1, 2008 at 2:53 pm

    Mike Aubury writes:
    ie - under postgresql it appears we've scrolled *past* the last row and need
    an additional fetch to get back to our last row..
    Why do you find that surprising? It seems to me to be symmetrical with
    the case at the beginning of the table --- the cursor is initially
    positioned before the first row. Why shouldn't there be a corresponding
    state where it's positioned after the last row?

    regards, tom lane
  • Gregory Stark at Apr 1, 2008 at 7:20 pm

    "Tom Lane" <tgl@sss.pgh.pa.us> writes:

    Mike Aubury <mike.aubury@aubit.com> writes:
    ie - under postgresql it appears we've scrolled *past* the last row and need
    an additional fetch to get back to our last row..
    Why do you find that surprising? It seems to me to be symmetrical with
    the case at the beginning of the table --- the cursor is initially
    positioned before the first row. Why shouldn't there be a corresponding
    state where it's positioned after the last row?
    What's implied by that but perhaps not clear is that it's easier to think of
    cursors as being *between* rows rather than *on* rows. I'm not sure the
    standard entirely adopts that model however.

    --
    Gregory Stark
    EnterpriseDB http://www.enterprisedb.com
    Ask me about EnterpriseDB's PostGIS support!
  • Tom Lane at Apr 1, 2008 at 9:01 pm

    Gregory Stark writes:
    What's implied by that but perhaps not clear is that it's easier to think of
    cursors as being *between* rows rather than *on* rows. I'm not sure the
    standard entirely adopts that model however.
    That's an interesting way of thinking about it, but I think it fails
    when you consider UPDATE/DELETE WHERE CURRENT OF.

    regards, tom lane

Related Discussions

Discussion Navigation
viewthread | post
Discussion Overview
grouppgsql-hackers @
categoriespostgresql
postedApr 1, '08 at 1:41p
activeApr 1, '08 at 9:01p
posts4
users3
websitepostgresql.org...
irc#postgresql

People

Translate

site design / logo © 2022 Grokbase