On 10/7/05, Rafael Montoya <rafo-mm@xxxxxxxxxxx> wrote:
> Hi everybody, thanks for your answers about hardware requirements. DB design
> was succesful and now we are migrating stored procedures from oracle to
> PostgreSQL.
> I can't handle cursors very well in PostgreSQL, for example, i need to
> migrate this stored procedure:
>
> CREATE OR REPLACE PROCEDURE LOAD_EXP AS
> cursor c_exp IS
> select C_COD_PRE from temp_codpre;
> BEGIN
> for cur1 in c_exp loop
> update lcmap_ctrcre
> set v_cod_pcar = '07'
> where c_num_exp = cur1.C_COD_PRE;
> commit;
> end loop;
> end LOAD_EXP;
> /
>
> and what i did in PostgreSQL was:
>
> CREATE OR REPLACE FUNCTION LOAD_EXP() RETURNS VOID AS $$
> DECLARE
> c_exp refcursor;
> BEGIN
> open c_exp for select C_COD_PRE from temp_codpre;
> loop
> FETCH c_exp INTO VARIABLE
> IF NOT FOUND THEN
> EXIT;
> END IF;
> update lcmap_ctrcre
> set v_cod_pcar = '07'
> where c_num_exp = cur1.C_COD_PRE;
> end loop;
> close c_exp;
> END;
> $$ LANGUAGE plpgsql;
> select LOAD_EXP()
>
> My really big doubt is about what VARIABLE must be and if this function is
> efficient how is it written.
> I'll appreciate any advice.
> Rafael
>
>
What VARIABLE is? and where you declare cur1?
maybe you want something like:
CREATE OR REPLACE FUNCTION LOAD_EXP() RETURNS VOID AS $$
DECLARE
cur1 record;
BEGIN
for cur1 in select C_COD_PRE from temp_codpre
loop
update lcmap_ctrcre set v_cod_pcar = '07'
where c_num_exp = cur1.C_COD_PRE;
end loop;
END;
$$ LANGUAGE plpgsql;
--
regards,
Jaime Casanova
(DBA: DataBase Aniquilator ;)
---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@xxxxxxxxxxxxxx so that your
message can get through to the mailing list cleanly
