Once the cursor is cached, any positioned update/delete is applied to its last record, regardless of the preceding fetch command.
Test case (using Python):
con.execute_immediate('recreate table ts(id int)')
con.commit()
con.execute_immediate('insert into ts (id) select row_number() over() from rdb$types rows 10')
con.commit()
cur = con.cursor()
cur.open('select id from ts for update')
cur.set_cursor_name('X')
for row in cur:
print_row(row)
cur.fetch_first()
print('Updating first record')
con.execute_immediate('update ts set id = -id where current of X')
con.commit()
cur = con.cursor()
cur.open('select id from ts for update')
cur.set_cursor_name('X')
for row in cur:
print_row(row)
cur.fetch_last()
print('Updating last record')
con.execute_immediate('update ts set id = -id where current of X')
con.commit()
cur = con.cursor()
cur.open('select id from ts for update')
cur.set_cursor_name('X')
for row in cur:
print_row(row)
cur.fetch_absolute(5)
print('Updating 5th record')
con.execute_immediate('update ts set id = -id where current of X')
con.commit()
cur = con.cursor()
cur.open('select id from ts')
for row in cur:
print_row(row)PSQL scrollable cursors work correctly with positioned updates:
recreate table ts(id int);
commit;
insert into ts (id) select row_number() over() from rdb$types rows 10;
commit;
set term ^;
execute block returns (id int)
as
declare c scroll cursor for (select id from ts for update);
begin
open c;
while (true) do
begin
fetch next from c;
if (row_count = 0) then break;
:id = c.id;
suspend;
end
fetch first from c;
update ts set id = -id where current of c;
fetch last from c;
update ts set id = -id where current of c;
fetch absolute 5 from c;
update ts set id = -id where current of c;
close c;
open c;
while (true) do
begin
fetch next from c;
if (row_count = 0) then break;
:id = c.id;
suspend;
end
close c;
end^
set term !^
Once the cursor is cached, any positioned update/delete is applied to its last record, regardless of the preceding fetch command.
Test case (using Python):
PSQL scrollable cursors work correctly with positioned updates: