looping through query to update column

Jean-Christophe Roux wrote:

I am trying to loop through a table to update one column

create or replace function foo() returns integer as $$
declare
rec RECORD;
row integer := 0;
begin
for rec in select * from table loop
update rec set recordid = row;
row++;
end loop;
return 0;
end;
$$ language plpgsql

In pgadmin, I am getting the following error message, but
that does not help me much:
ERROR: syntax error at or near "$1" at character 9
QUERY: update $1 set recordid = $2
CONTEXT: SQL statement in PL/PgSQL function "foo" near line 6

You cannot UPDATE a record, you can only UPDATE a table.

So it should be something like
UPDATE table SET recordid = row WHERE primarykey = rec.primarykey

You might use 'ctid' to identify the row if you have no suitable
primary key (you do have one, don't you?), but beware that ctid
can change suddenly and unexpectedly when somebody else modifies
the row. To protect against that, you can either LOCK the table or
SELECT ... FOR UPDATE.

Yours,
Laurenz Albe

On Fri, 2006-10-13 at 09:23 +0200, Albe Laurenz 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?

--
-R

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

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@adv.magwien.gv.at>
To: pgsql-general@postgresql.org
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

On 10/13/06, Jean-Christophe Roux <jcxxr@yahoo.com> wrote:

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'?

it's not. also, without a where clause you are better off just locking
the table (lock table...). also, the above loop is better achieved
via a single query.

merlin

On 2006-10-13, "Albe Laurenz" <all@adv.magwien.gv.at> wrote:

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.

Looping over rows unnecessarily is a mistake.

You can add a SERIAL column to a table using ALTER TABLE, which will
automatically number the existing rows; this is a better way to fix a
lack of a primary key than messing around with ctids.

For a one-off update, use a temporary sequence:

create temporary sequence foo;
update table set recordid = nextval('foo');

--
Andrew, Supernews
http://www.supernews.com - individual and corporate NNTP services