Re: 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