Thread: cursor and for update
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 ' DECLAREtabela 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; BEGINfor i in reguly LOOP UPDATE firewall SET id_firewall=licznik WHERE CURRENT OF reguly; licznik:=licznik+1;END LOOP; return ret; END;' LANGUAGE 'plpgsql'; -- Best regards,Maciej mailto:piechcio@isb.com.pl
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 '.
Hello Wiebe, Tuesday, March 28, 2006, 2:42:20 AM, you wrote: >> WC> Fist, what's the error you get? ERROR: missing ".." at end of SQL expression CONTEXT: compile of PL/pgSQL function "uporzadkuj_reguly" near line 7 WC> Second, what does "rekord" do? You don't seem to use it. Yes, i don't need it. WC> Third, can you quote the language? I always say plpgsql without the WC> quotes, but I'm not sure if using quotes is not allowed (and I'm too WC> lazy to check :)). WC> Also, why don't you have a "RETURNS VOID" function, which you can end WC> with "RETURN;" OK WC> But what you might be stuck on is that you have a column and variable WC> with the same name. Try to give the variables "tabela" and "lancunch" WC> different names. OK WC> Lastly, if you use postgres 8, you can quote your function with $$ WC> instead of ' (meaning ...$$ AS DECLARE BEGIN END;$$...), so that you WC> won't get the ugly syntax highligting that most editors will give you WC> when quoting with '. I have postgres 7.4 create or replace function uporzadkuj_reguly(text,text) RETURNS VOID AS ' DECLARElicznik integer:=1; reguly CURSOR FOR SELECT * from firewall ORDER BY id_firewall WHEREtabela=$1 and lancuch=$2 for UPDATE; BEGINfor i in reguly LOOP UPDATE firewall SET id_firewall=licznik WHERE CURRENT OF reguly; licznik:=licznik+1;END LOOP; return; END;' LANGUAGE 'plpgsql'; -- Best regards,Maciej mailto:piechcio@isb.com.pl
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.