Re: Updating cursors

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 #