Thread: [General] Using cursors...

[General] Using cursors...

From

"Rafael Montoya"

Date:

07 October 2005, 15:52:41

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

_________________________________________________________________
Acepta el reto MSN Premium: Correos más divertidos con fotos y textos
increíbles en MSN Premium. Descárgalo y pruébalo 2 meses gratis.
http://join.msn.com?XAPID=1697&DI=1055&HL=Footer_mailsenviados_correosmasdivertidos

Re: [General] Using cursors...

From

Jaime Casanova

Date:

07 October 2005, 16:17:01

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

Re: [General] Using cursors...

From

"Rafael Montoya"

Date:

07 October 2005, 17:36:35

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

Re: Using cursors...

From

Jaime Casanova

Date:

07 October 2005, 20:26:50

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

Re: exceptions

From

"Rafael Montoya"

Date:

11 October 2005, 17:24:51

I'm migrating some triggers from oracle to postgresql and i can´t find the
equivalent of the following two sentences:
1)
DECLARE
     TMP_COD_PRO  PRODUCT.COD_PRO%TYPE;

I don't know if its equivalent exists in PostgreSQL

2)
EXCEPTION
        when no_data_found then null;

what i tried :

exception
       if not found then null;

but it seems not to be correct, can anybody give me a hand? thanks
Rafael

_________________________________________________________________
Acepta el reto MSN Premium: Protección para tus hijos en internet.
Descárgalo y pruébalo 2 meses gratis.
http://join.msn.com?XAPID=1697&DI=1055&HL=Footer_mailsenviados_proteccioninfantil

Re: exceptions

From

Michael Fuhr

Date:

11 October 2005, 18:36:08

On Tue, Oct 11, 2005 at 07:24:12PM +0200, Rafael Montoya wrote:
> I'm migrating some triggers from oracle to postgresql and i can´t find the
> equivalent of the following two sentences:
> 1)
> DECLARE
>     TMP_COD_PRO  PRODUCT.COD_PRO%TYPE;

See "Declarations" in the PL/pgSQL documentation for the available
syntax:

http://www.postgresql.org/docs/8.0/interactive/plpgsql-declarations.html

> 2)
> EXCEPTION
>        when no_data_found then null;
>
> what i tried :
>
> exception
>       if not found then null;
>
> but it seems not to be correct, can anybody give me a hand?

See "Trapping Errors" in the documentation for the allowed syntax
(only available in 8.0 and later):

http://www.postgresql.org/docs/8.0/interactive/plpgsql-control-structures.html#PLPGSQL-ERROR-TRAPPING

In PL/pgSQL, queries that return no rows don't raise a "no data"
exception.  To check whether any rows were returned you can use
FOUND in an ordinary IF statement.

--
Michael Fuhr

Windows Installation error

From

"Rafael Montoya"

Date:

13 October 2005, 20:05:26

This error is shown after choose the language in the installation of
Postgresql 8.0.3 in windows 2000:

"The installer has detected an incompatible version of OpenSSL installed in
your system PATH. PostgreSQL requires OpenSSL 0.9.7 or later. If you remove
your OpenSSL files (LIBEAY32.DLL and SSLEAY32.DLL) the installer will
install the new versioon automatically. "

but when i press OK installation seems to be normal. I didn't delete the
files because i don't know if these new files can change something in
another program.  The installation finished and i don't know if my database
won't have any problem becauseof this error. Were the files updated? do i
have to install again?

Rafael

_________________________________________________________________
Acepta el reto MSN Premium: Correos más divertidos con fotos y textos
increíbles en MSN Premium. Descárgalo y pruébalo 2 meses gratis.
http://join.msn.com?XAPID=1697&DI=1055&HL=Footer_mailsenviados_correosmasdivertidos