Re: Using cursors...

On 10/7/05, Rafael Montoya <rafo-mm@hotmail.com> wrote:
> 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?
>

the for construct hide the cursor details for you...
you just loop through the records retrived for the select statement

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

you call the function executing: 'select load_exp();' from your
application or from psql

> 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
>
>


--
Atentamente,
Jaime Casanova
(DBA: DataBase Aniquilator ;)