Re: How to update rows from a cursor in PostgreSQL

From: Ruben <ruben12(at)superguai(dot)com>
To: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
Cc: pgsql-general(at)postgresql(dot)org
Subject: Re: How to update rows from a cursor in PostgreSQL
Date: 2003-02-23 12:27:31
Message-ID: 3E58BE33.4060007@superguai.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

Thanks a lot Tom:

Shouldn't it be better then to use "oid" instead of ctid?

Ruben.

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.

In response to

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Oliver Elphick 2003-02-23 13:26:25 Re: troubles with postgresql
Previous Message Thomas Beutin 2003-02-23 11:03:20 Re: troubles with postgresql