Обсуждение: 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 sometabvalues(3); begin work; declare c1 scroll cursor for select * from sometab; fetch next from c1; fetch next from c1; fetch next from c1; fetchnext 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 CURSORa ---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.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? regards, tom lane
"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!
Gregory Stark <stark@enterprisedb.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