Re: How to update rows from a cursor in PostgreSQL

Tom Lane wrote:
> Ruben <ruben12(at)superguai(dot)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.

However, it should be noted that this may fail, especially if you work on
views. I've been bitten by this.

Emmanuel Charpentier

--
Emmanuel Charpentier