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(at)Yahoo(dot)com #