Re: [General] Using cursors... - Mailing list pgsql-general

Thanks for your answer.

In your example you are handling record, isn't it  necessary to use fetch to
read all the table? or the "for ....in select ....  loop..."  reads all the
records?

For executing this procedure, must this calling be at the end of the
function?
--->  select load_exp();
or it isn't necessary?

Thanks again for your answers.
Rafael

>From: Jaime Casanova <systemguards@gmail.com>
>Reply-To: Jaime Casanova <systemguards@gmail.com>
>To: Rafael Montoya <rafo-mm@hotmail.com>
>CC: pgsql-general@postgresql.org
>Subject: Re: [GENERAL] [General] Using cursors...
>Date: Fri, 7 Oct 2005 11:10:05 -0500
>
>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 ;)
>
>---------------------------(end of broadcast)---------------------------
>TIP 1: if posting/reading through Usenet, please send an appropriate
>        subscribe-nomail command to majordomo@postgresql.org so that your
>        message can get through to the mailing list cleanly

_________________________________________________________________
Descarga gratis la Barra de Herramientas de MSN
http://www.msn.es/usuario/busqueda/barra?XAPID=2031&DI=1055&SU=http%3A//www.hotmail.com&HL=LINKTAG1OPENINGTEXT_MSNBH