Re: Using cursors...

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