pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedcursor and for update
4+ messages / 2 participants
[nested] [flat]
* cursor and for update
@ 2006-03-27 23:35 Maciej Piekielniak <piechcio@isb.com.pl>
0 siblings, 1 reply; 4+ messages in thread
From: Maciej Piekielniak @ 2006-03-27 23:35 UTC (permalink / raw)
To: pgsql-sql
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';
--
Best regards,
Maciej mailto:piechcio@isb.com.pl
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: cursor and for update
@ 2006-03-28 00:42 Wiebe Cazemier <halfgaar@gmail.com>
parent: Maciej Piekielniak <piechcio@isb.com.pl>
0 siblings, 1 reply; 4+ messages in thread
From: Wiebe Cazemier @ 2006-03-28 00:42 UTC (permalink / raw)
To: Maciej Piekielniak <piechcio@isb.com.pl>; +Cc: pgsql-sql
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 '.
Attachments:
signature.asc (application/pgp-signature, 257B) download
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: cursor and for update
@ 2006-03-28 09:13 Maciej Piekielniak <piechcio@isb.com.pl>
parent: Wiebe Cazemier <halfgaar@gmail.com>
0 siblings, 1 reply; 4+ messages in thread
From: Maciej Piekielniak @ 2006-03-28 09:13 UTC (permalink / raw)
To: pgsql-sql
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
'
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';
--
Best regards,
Maciej mailto:piechcio@isb.com.pl
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: cursor and for update
@ 2006-03-28 09:59 Wiebe Cazemier <halfgaar@gmail.com>
parent: Maciej Piekielniak <piechcio@isb.com.pl>
0 siblings, 0 replies; 4+ messages in thread
From: Wiebe Cazemier @ 2006-03-28 09:59 UTC (permalink / raw)
To: Maciej Piekielniak <piechcio@isb.com.pl>; +Cc: pgsql-sql
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.
Attachments:
signature.asc (application/pgp-signature, 257B) download
^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2006-03-28 09:59 UTC | newest]
Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2006-03-27 23:35 cursor and for update Maciej Piekielniak <piechcio@isb.com.pl>
2006-03-28 00:42 ` Wiebe Cazemier <halfgaar@gmail.com>
2006-03-28 09:13 ` Maciej Piekielniak <piechcio@isb.com.pl>
2006-03-28 09:59 ` Wiebe Cazemier <halfgaar@gmail.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox