Search Postgresql Archives

Scrolling/Updating Cursors

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



My apologies for posting what is almost certainly somewhat of a repeat question but I have searched and attempted everything I can think of and cannot figure it out myself.

The basic question is… Is it possible to get a scrollable cursor that, within a transaction, can insert/update/delete rows and see those changes?

Why you ask? Because I am implementing the JDBC 4.1 spec and it (optionally) provides this ability and I’d like to provide it if possible.  I thought just using a scrollable cursor with the isolation-level set to repeatable-read would do the trick but it still seems to see no changes (inserts/updates/deletes).

Effectively I want roughly this sequence of events to work.

CREATE TABLE test (val text);

BEGIN;  # Could include ISOLATION LEVEL REPEATABLE READ

DECLARE c1 CURSOR FOR SELECT * FROM test;

FETCH ABSOLUTE 1; 	# Returns No Row

INSERT INTO test VALUES (‘1');
INSERT INTO test VALUES (‘2');
INSERT INTO test VALUES (‘3');

FETCH ABSOLUTE 1;  # Returns “1”
FETCH NEXT:               # Returns “2"
DELETE FROM test WHERE CURRENT OF c1;  # Deletes “2”

FETCH ABSOLUTE 1;  # Returns “1”
FETCH NEXT;               # Returns “3”
UPDATE test SET val=‘4' WHERE CURRENT OF c1;
FETCH RELATIVE 0;    # Returns “4”

COMMIT;

Thanks!

-kw

-- 
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general





[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux