cursors in plpgsql function??
cursors in plpgsql function??
От:
"Kuti Attila" <attika@elender.hu>
Дата:
Hi! I have problem with declaring a cursor for a query in a plpgsql. I got compile error, and parse error at or near cursor. Here my function for a trigger: What can be the problem? Thanks a lot Attila create function p_kotogep() returns opaque as ' declare ajanlid ajanlati_konyv_p.ajanlat_id%TYPE; userid felhasznalo.user_id%TYPE; megidop timestamp; termekid termek.termek_id%TYPE; menny portfolio.szabad_db%TYPE; arr portfolio.szabad_db%TYPE; iran ajanlati_konyv_p.irany%TYPE; erv timestamp; hany int4; piar bool; aktar termek.arfolyam%TYPE; begin work; select count(ajanlat_id) into hany from ajanlati_konyv_p; -- SOMEWHERE HERE I GET ALWAYS ERRORdeclare ajanlatok cursor for select * from ajanlati_konyv_p; for i in 1..hany loop fetch next from ajanlatok into ajanlid, userid, megidop, termekid, menny, arr, piar, iran, erv; aktar= p_aktar(termekid); if setup_olv(1) then -- arjegyzovel jatszunk if iran = 1 then -- vetel if aktar <= arr then insert into kotes_naplo_p (vevo_id, elado_id, termek_id, ar, mennyiseg, szabad_valt) values (userid, 1, termekid, arr, menny, trktsg (menny, arr, 1) ); delete from ajanlati_konyv_p where ajanlati_id = ajanlid; else end if; else if aktar >= arr then insert into kotes_naplo_p (vevo_id, elado_id, termek_id, ar, mennyiseg, szabad_valt) values (1, userid, termekid, arr, menny, trktsg (menny, arr, 1) ); delete from ajanlati_konyv_p where ajanlati_id = ajanlid; else end if; end if; end if ; -- arjegyzovel jatszunk vegeend loop;close ajanlatok;commit work; return NULL; ; ' language 'plpgsql'; trigger paj_kot on ajanlati_konyv_p; trigger paj_kot after insert on ajanlati_konyv_p for each row execute procedure p_kotogep(); attika@elender.hu http://www.bke.hu/~attika