Re: Scroll cursor oddity...

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

Mike Aubury <mike(dot)aubury(at)aubit(dot)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?

regards, tom lane

"Tom Lane" <tgl(at)sss(dot)pgh(dot)pa(dot)us> writes:

> Mike Aubury <mike(dot)aubury(at)aubit(dot)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!

Gregory Stark <stark(at)enterprisedb(dot)com> 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