Re: [General] Using cursors...

On 10/7/05, Rafael Montoya <rafo-mm(at)hotmail(dot)com> 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 ;)