Re: How to update rows from a cursor in PostgreSQL - Mailing list pgsql-general

Ruben <ruben12@superguai.com> writes:
> Since "FOR UPDATE" cursors are not supported in PostgreSQL, can I update
> the current row of table t1?

The usual hack for this is to select the table's "ctid" system column as
part of the cursor output, and then say

    UPDATE t1 SET ... WHERE ctid = 'what-you-got-from-the-cursor';

This is quite fast because the ctid is essentially a physical locator.
Note however that it will fail (do nothing) if someone else has already
updated the same row since your transaction started.  This may or may
not be what you want.  I think ODBC has some hack to find the ctid of
the latest version of the row.

            regards, tom lane