On 10/7/05, Rafael Montoya <rafo-mm@xxxxxxxxxxx> 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@xxxxxxxxx> > >Reply-To: Jaime Casanova <systemguards@xxxxxxxxx> > >To: Rafael Montoya <rafo-mm@xxxxxxxxxxx> > >CC: pgsql-general@xxxxxxxxxxxxxx > >Subject: Re: [General] Using cursors... > >Date: Fri, 7 Oct 2005 11:10:05 -0500 > > > >On 10/7/05, Rafael Montoya <rafo-mm@xxxxxxxxxxx> 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@xxxxxxxxxxxxxx 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 ;) ---------------------------(end of broadcast)--------------------------- TIP 4: Have you searched our list archives? http://archives.postgresql.org
