From: | Kevin Wooten <kdubb(at)me(dot)com> |
---|---|
To: | pgsql-general(at)postgresql(dot)org |
Subject: | Scrolling/Updating Cursors |
Date: | 2013-11-20 01:33:54 |
Message-ID: | CD436A08-9CCC-4964-866C-1A8F17707CBB@me.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Peter Eisentraut | 2013-11-20 03:03:31 | Re: Clang 3.3 Analyzer Results |
Previous Message | Edson Richter | 2013-11-20 00:56:48 | Re: Postgres as In-Memory Database? |