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
