Re: cursor and for update

On 03/28/06 01:35, Maciej Piekielniak wrote:

>Hello ,
>
>I try to translate my old functions from oracle but don't understand what is wrong.
>
>create or replace function uporzadkuj_reguly(text,text) RETURNS integer AS
>'
>DECLARE
> tabela ALIAS FOR $1;
> lancuch ALIAS FOR $2;
> ret integer:=0;
> licznik integer:=1;
> rekord firewall%ROWTYPE;
>
> reguly CURSOR FOR SELECT * from firewall ORDER BY id_firewall WHERE tabela=tabela and lancuch=lancuch for UPDATE;
>BEGIN
> for i in reguly LOOP
>   UPDATE firewall SET id_firewall=licznik WHERE CURRENT OF reguly;
>  licznik:=licznik+1;
> END LOOP;
>
> return ret;
>END;'
>LANGUAGE 'plpgsql';  
>
>  
>
Fist, what's the error you get?

Second, what does "rekord" do? You don't seem to use it.

Third, can you quote the language? I always say plpgsql without the
quotes, but I'm not sure if using quotes is not allowed (and I'm too
lazy to check :)).

Also, why don't you have a "RETURNS VOID" function, which you can end
with "RETURN;"

But what you might be stuck on is that you have a column and variable
with the same name. Try to give the variables "tabela" and "lancunch"
different names.

Lastly, if you use postgres 8, you can quote your function with $$
instead of ' (meaning ...$$ AS DECLARE BEGIN END;$$...), so that you
won't get the ugly syntax highligting that most editors will give you
when quoting with '.