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@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
---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend
