Re: Scrolling/Updating Cursors - Mailing list pgsql-general

From Kevin Wooten
Subject Re: Scrolling/Updating Cursors
Date
Msg-id B9E1B2F6-AE67-4290-88B1-4F82892FAA4A@me.com
Whole thread Raw
In response to Scrolling/Updating Cursors  (Kevin Wooten <kdubb@me.com>)
Responses Re: Scrolling/Updating Cursors
List pgsql-general
On Nov 19, 2013, at 6:33 PM, Kevin Wooten <kdubb@me.com> wrote:

> My apologies for posting what is almost certainly somewhat of a repeat question but I have searched and attempted
everythingI 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
rowsand 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
provideit if possible.  I thought just using a scrollable cursor with the isolation-level set to repeatable-read would
dothe 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
>


Should I take silence to mean this cannot be done?  Or should I send to -hackers?



pgsql-general by date:

Previous
From: Kevin Grittner
Date:
Subject: Re: Easiest way to CREATE EXTENSION when upgrading from 9.0 to 9.2?
Next
From: John Meyer
Date:
Subject: Re: Scrolling/Updating Cursors