Re: [General] Using cursors...

On 10/7/05, Rafael Montoya <rafo-mm@hotmail.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 ;)