Re: cursor and for update

On 03/28/06 11:13, Maciej Piekielniak wrote:

>create or replace function uporzadkuj_reguly(text,text) RETURNS VOID AS
>'
>DECLARE
> licznik integer:=1;
>
> reguly CURSOR FOR SELECT * from firewall ORDER BY id_firewall WHERE
> tabela=$1 and lancuch=$2 for UPDATE;
>BEGIN
> for i in reguly LOOP
> UPDATE firewall SET id_firewall=licznik WHERE CURRENT OF reguly;
> licznik:=licznik+1;
> END LOOP;
>
> return;
>END;'
>LANGUAGE 'plpgsql';
>
Looping in postgres goes differently, at least, it does in version 8. I
use loops like this (there are more ways, but I think what you're doing
is wrong):

FOR row IN (query) LOOP

END LOOP;

Where "row" is a variable of type RECORD. I'm not sure how this works
with CURSOR, or if postgres 7.4 has different loop constructs, but you
might want to look into that, in the documentation. It has a section on
loops for plpgsql.