Re: looping through query to update column

[Jean-Christophe Roux <jcxxr@xxxxxxxxx>]

Thanks for the "ctid" trick. The code below worked fine
    for rec in select * from fromemail_trades loop
        update fromemail_trades set recordid = row where ctid = rec.ctid;
        row := row -1;
    end loop;
The first line is a little different from your's:
    FOR row IN SELECT ctid, * FROM table FOR UPDATE LOOP

How important is it to specify ctid in the select and to add 'for update'?
Thanks again
JCR
 

----- Original Message ----
From: Albe Laurenz <all@xxxxxxxxxxxxxxxxx>
To: pgsql-general@xxxxxxxxxxxxxx
Sent: Friday, October 13, 2006 6:24:16 AM
Subject: Re: [GENERAL] looping through query to update column

Rafal Pietrak wrote:
>> You might use 'ctid' to identify the row if you have no suitable
>
> How should I use 'ctid'? Like in the case, when I've selected
> something by means of SELECT ... FOR UPDATE?

You lock the table (with LOCK) or the row you're working on
(with SELECT FOR UPDATE) so that nobody else can change it while
you are working on it.

You need something like ctid if your table has the fundamental flaw
of lacking a primary key.

Sample:

FOR row IN SELECT ctid, * FROM table FOR UPDATE LOOP
   UPDATE table SET column=value WHERE ctid=row.ctid;
   ...
END LOOP;

If your table has a primary key, use that instead and please
forget about the ctid.

Yours,
Laurenz Albe

---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
       choose an index scan if your joining column's datatypes do not
       match