Thread: Updating cursors

Hi,

Ive a SELECT cursor which I want to update/delete but postgresql does not
support these:

UPDATE myTable SET myColumn = 'myValue' WHERE CURRENT OF myCursor
DELETE myTable WHERE CURRENT OF myCursor

Does there exist any workaround?
Or is my syntax wrong?

One workaround would be to get the row id and to be able to update it.
Something like this:

... a row is selected and fetched ...

int i = GetRowId( ); // C function that reads a unique row id.
ExecuteSQL( "UPDATE myTable SET myColumn = 'myValue' WHERE @ROWID = %d",
i );
ExecuteSQL( "DELETE myTable WHERE @ROWID = %d", i );
// ExecuteSQL(...) is a C function that parses and executes the string.

// Jarmo

PS. I hope Im sending this mail to the right address, if not Im sorry DS.

> -----Original Message-----
> From: Jarmo Paavilainen
>
> Hi,
>
> Ive a SELECT cursor which I want to update/delete but postgresql does not
> support these:
>
> UPDATE myTable SET myColumn = 'myValue' WHERE CURRENT OF myCursor
> DELETE myTable WHERE CURRENT OF myCursor
>
> Does there exist any workaround?
> Or is my syntax wrong?
>
> One workaround would be to get the row id and to be able to update it.
> Something like this:
>
> ... a row is selected and fetched ...
>

You may be able to use CTID.
You could get CTIDs by using SELECT statements like
    select CTID,* from myTable;
and update using CTID
     update myTable set ..=.. where CTID=..;

Note that CTIDs aren't of int type.

Regards.

Hiroshi Inoue

Jarmo Paavilainen wrote:
> Hi,
>
> Ive a SELECT cursor which I want to update/delete but postgresql does not
> support these:
>
> UPDATE myTable SET myColumn = 'myValue' WHERE CURRENT OF myCursor
> DELETE myTable WHERE CURRENT OF myCursor

    PostgreSQL  does  not have the concept of updateable cursors.
    And that isn't planned either.

> Does there exist any workaround?
> Or is my syntax wrong?
>
> One workaround would be to get the row id and to be able to update it.
> Something like this:
>
> ... a row is selected and fetched ...
>
> int i = GetRowId( ); // C function that reads a unique row id.
> ExecuteSQL( "UPDATE myTable SET myColumn = 'myValue' WHERE @ROWID = %d",
> i );
> ExecuteSQL( "DELETE myTable WHERE @ROWID = %d", i );
> // ExecuteSQL(...) is a C function that parses and executes the string.

    Right. That'd work and the row ID you're looking for is  it's
    OID.  Up to now, all tables have a system attribute OID, that
    you can explicitly SELECT for such a purpose. Don't forget to
    create an INDEX on the OID if you go and do

        UPDATE yourTable SET yourColumn = 'yourValue'
            WHERE oid = <oid_of_yourRow>;

    Also  don't  forget  that  in  a  concurrent  environment you
    probably want to SELECT ... FOR UPDATE the rows in the  first
    place.

> // Jarmo
>
> PS. I hope Im sending this mail to the right address, if not Im sorry DS.

    pgsql-sql would've been.


Jan

--

#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me.                                  #
#================================================== JanWieck@Yahoo.com #