[HACKERS] 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 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Scroll cursor oddity...
Mike Aubury [EMAIL PROTECTED] 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 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Scroll cursor oddity...
Tom Lane [EMAIL PROTECTED] writes: Mike Aubury [EMAIL PROTECTED] 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! -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Scroll cursor oddity...
Gregory Stark [EMAIL PROTECTED] 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 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
