Search Postgresql Archives

Re: looping through query to update column

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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


[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux