[General] Using cursors...

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

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&amp;DI=1055&amp;SU=http%3A//www.hotmail.com&amp;HL=LINKTAG1OPENINGTEXT_MSNBH

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&amp;DI=1055&amp;SU=http%3A//www.hotmail.com&amp;HL=LINKTAG1OPENINGTEXT_MSNBH

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

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&amp;DI=1055&amp;HL=Footer_mailsenviados_proteccioninfantil

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

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&amp;DI=1055&amp;HL=Footer_mailsenviados_correosmasdivertidos

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?

You will most likely run into problems *if* you enable SSL connections.
As long as you don't use SSL connections, you should be fine. It's for
just tihs reason that the instlaler proceeds even though it detects a
problem - it might not affect you.

The files were *not* updated. If you are going to use the SSL
functionality, you need to get them upgraded. One way is to remove the
old ones and then reinstlal postgresql. If you don't want to do that,
you can just grab the latest openssl DLLs from their site and replace
the files with them, and postgresql will happily use this.

For 8.1 we'll use OpenSSL libraries stored in the postgresql directory,
so they will not conflict with whatever is on your system already.

//Magnus

I need to know if there is a tool that convert oracle procedures and
triggers to plpgsql syntax. Please, can anybody tell me where do i download
it from?, i'll thank you a lot.
Rafael

_________________________________________________________________
Un amor, una aventura, compa��a para un viaje. Reg�strate gratis en MSN Amor
& Amistad. http://match.msn.es/match/mt.cfm?pg=channel&amp;tcid=162349

Rafael Montoya wrote:

I need to know if there is a tool that convert oracle procedures and
triggers to plpgsql syntax. Please, can anybody tell me where do i
download it from?, i'll thank you a lot.

I don't know of a free tool that will do procedures. Probably worth
reading through Oracle notes here:
http://techdocs.postgresql.org/

Also - search for "ora2pg" - might be useful.

If you have money to spend, it might be worth checking out EnterpriseDB
- they claim to have Oracle compatibility. News item/company site below.
http://www.postgresql.org/about/news.367
http://www.enterprisedb.com/

HTH
--
Richard Huxton
Archonet Ltd

dev@archonet.com (Richard Huxton) writes:

If you have money to spend, it might be worth checking out
EnterpriseDB - they claim to have Oracle compatibility. News
item/company site below.
http://www.postgresql.org/about/news.367
http://www.enterprisedb.com/

It would be quite useful to have some sort of general idea as to what
the "closer compatibility to Oracle" of EnterpriseDB means.
--
output = reverse("gro.mca" "@" "enworbbc")
http://cbbrowne.com/info/sgml.html
Referring to undocumented private communications allows one to claim
virtually anything: "we discussed this idea in our working group last
year, and concluded that it was totally brain-damaged".
-- from the Symbolics Guidelines for Sending Mail

On Thu, 2005-10-20 at 12:35 -0400, Chris Browne wrote:

dev@archonet.com (Richard Huxton) writes:

If you have money to spend, it might be worth checking out
EnterpriseDB - they claim to have Oracle compatibility. News
item/company site below.
http://www.postgresql.org/about/news.367
http://www.enterprisedb.com/

It would be quite useful to have some sort of general idea as to what
the "closer compatibility to Oracle" of EnterpriseDB means.

Well that would be a question for EnterpriseDB and their marketing/tech
staff. There product is completely closed and there is no documentation
online.

Sincerely,

Joshua D. Drake

--
The PostgreSQL Company - Command Prompt, Inc. 1.503.667.4564
PostgreSQL Replication, Consulting, Custom Development, 24x7 support
Managed Services, Shared and Dedicated Hosting
Co-Authors: plPHP, plPerlNG - http://www.commandprompt.com/

I'm migrating from oracle to postgresl, and i have these 2 problems:

1.
PostgreSQL doesn't support function DECODE from Oracle, but it can be
replicated with
CASE WHEN expr THEN expr [...] ELSE expr END , the problem appears when i
want to convert this sentence from oracle to postgresl:
select decode (pre.C_GEN,'01','M','02','F','') as GENERO
my convertion is
case when pre.C_GEN = '01' then GENERO='M' else GENERO='F'
end ,
but i dont' know if the assigment of GENERO is correct.

2.
Writing triggers i don't know if postgresql supports statements like this:
CREATE OR REPLACE TRIGGER trig
AFTER UPDATE OF column2 <<----- Here is the doubt
ON table_product
FOR EACH ROW
BEGIN
...
END

In postgresql:
CREATE OR REPLACE TRIGGER trig
AFTER UPDATE OF column2 <<----- is this correct?
ON table_product
FOR EACH ROW EXECUTE PROCEDURE trig();

Thanks for your answers..
Rafael

_________________________________________________________________
�Est�s pensando en cambiar de coche? Todas los modelos de serie y extras en
MSN Motor. http://motor.msn.es/researchcentre/

On Tue, 2005-10-25 at 00:16 +0200, Rafael Montoya wrote:

I'm migrating from oracle to postgresl, and i have these 2 problems:

1.
PostgreSQL doesn't support function DECODE from Oracle, but it can be
replicated with
CASE WHEN expr THEN expr [...] ELSE expr END , the problem appears when i
want to convert this sentence from oracle to postgresl:
select decode (pre.C_GEN,'01','M','02','F','') as GENERO
my convertion is
case when pre.C_GEN = '01' then GENERO='M' else GENERO='F'
end ,
but i dont' know if the assigment of GENERO is correct.

SELECT CASE WHEN re.C_GEN = '01' THEN 'M' ELSE 'F' END AS GENER0

2.
Writing triggers i don't know if postgresql supports statements like this:
CREATE OR REPLACE TRIGGER trig
AFTER UPDATE OF column2 <<----- Here is the doubt
ON table_product
FOR EACH ROW
BEGIN
...
END

In postgresql:
CREATE OR REPLACE TRIGGER trig

CREATE TRIGGER does not support CREATE OR REPLACE

AFTER UPDATE OF column2 <<----- is this correct?

No. PostgreSQL doesn't support column triggers yet.

ON table_product
FOR EACH ROW EXECUTE PROCEDURE trig();

CREATE TRIGGER trig
AFTER UPDATE
ON table_product
FOR EACH ROW EXECUTE PROCEDURE trig();

In trig() you need to make the action conditional:

IF NEW.column2 <> OLD.column2 OR
(NEW.column2 IS NULL) <> (OLD.column2 IS NULL) THEN
...
END IF;

(assuming it's written in plpgsql).
--
Oliver Elphick olly@lfix.co.uk
Isle of Wight http://www.lfix.co.uk/oliver
GPG: 1024D/A54310EA 92C8 39E7 280E 3631 3F0E 1EC0 5664 7A2F A543 10EA
========================================
Do you want to know God? http://www.lfix.co.uk/knowing_god.html

Thanks for your answer, and if i have many options like

decode (pre.C_EST,'01','U','02','M','03','W','04','D','05','O','06','S','')
as Est

do i have to write many else options in this way?

select case when pre.C_EST = '01' THEN 'U'
ELSE when pre-C_EST = '02' THEN 'M'
...
END AS EST

Rafael

From: Oliver Elphick <olly@lfix.co.uk>
Reply-To: olly@lfix.co.uk
To: Rafael Montoya <rafo-mm@hotmail.com>
CC: pgsql-general@postgresql.org
Subject: Re: [GENERAL] function DECODE and triggers
Date: Tue, 25 Oct 2005 10:15:17 +0100

On Tue, 2005-10-25 at 00:16 +0200, Rafael Montoya wrote:

I'm migrating from oracle to postgresl, and i have these 2 problems:

1.
PostgreSQL doesn't support function DECODE from Oracle, but it can be
replicated with
CASE WHEN expr THEN expr [...] ELSE expr END , the problem appears when

i

want to convert this sentence from oracle to postgresl:
select decode (pre.C_GEN,'01','M','02','F','') as GENERO
my convertion is
case when pre.C_GEN = '01' then GENERO='M' else

GENERO='F'

end ,
but i dont' know if the assigment of GENERO is correct.

SELECT CASE WHEN re.C_GEN = '01' THEN 'M' ELSE 'F' END AS GENER0

2.
Writing triggers i don't know if postgresql supports statements like

this:

CREATE OR REPLACE TRIGGER trig
AFTER UPDATE OF column2 <<----- Here is the doubt
ON table_product
FOR EACH ROW
BEGIN
...
END

In postgresql:
CREATE OR REPLACE TRIGGER trig

CREATE TRIGGER does not support CREATE OR REPLACE

AFTER UPDATE OF column2 <<----- is this correct?

No. PostgreSQL doesn't support column triggers yet.

ON table_product
FOR EACH ROW EXECUTE PROCEDURE trig();

CREATE TRIGGER trig
AFTER UPDATE
ON table_product
FOR EACH ROW EXECUTE PROCEDURE trig();

In trig() you need to make the action conditional:

IF NEW.column2 <> OLD.column2 OR
(NEW.column2 IS NULL) <> (OLD.column2 IS NULL) THEN
...
END IF;

(assuming it's written in plpgsql).
--
Oliver Elphick olly@lfix.co.uk
Isle of Wight http://www.lfix.co.uk/oliver
GPG: 1024D/A54310EA 92C8 39E7 280E 3631 3F0E 1EC0 5664 7A2F A543 10EA
========================================
Do you want to know God? http://www.lfix.co.uk/knowing_god.html

_________________________________________________________________
Moda para esta temporada. Ponte al d�a de todas las tendencias.
http://www.msn.es/Mujer/moda/default.asp

On Tue, 2005-10-25 at 18:31 +0200, Rafael Montoya wrote:

Thanks for your answer, and if i have many options like

decode (pre.C_EST,'01','U','02','M','03','W','04','D','05','O','06','S','')
as Est

do i have to write many else options in this way?

select case when pre.C_EST = '01' THEN 'U'
ELSE when pre-C_EST = '02' THEN 'M'
...
END AS EST

Rafael

The syntax is

CASE WHEN condition1 THEN value1
WHEN condition2 THEN value2
...
ELSE default_value
END

Oliver

Rafael Montoya wrote:

Thanks for your answer, and if i have many options like

decode
(pre.C_EST,'01','U','02','M','03','W','04','D','05','O','06','S','') as Est

do i have to write many else options in this way?

select case when pre.C_EST = '01' THEN 'U'
ELSE when pre-C_EST = '02' THEN 'M'
...
END AS EST

You could write a stored procedure that does what you want.

--
Alban Hertroys
alban@magproductions.nl

magproductions b.v.

T: ++31(0)534346874
F: ++31(0)534346876
M:
I: www.magproductions.nl
A: Postbus 416
7500 AK Enschede

//Showing your Vision to the World//

I have this statement in oracle:

CREATE OR REPLACE TRIGGER trig
AFTER INSERT OR UPDATE OR DELETE OF column2 <<----- Here
is the doubt
ON table_product
FOR EACH ROW
BEGIN
...
END

Migrating to PostgreSQL, the conditionals for AFTER UPDATE OF COLUMN2 in
trig() are:

IF NEW.column2 <> OLD.column2 OR
(NEW.column2 IS NULL) <> (OLD.column2 IS NULL) THEN
...
END IF;

but, i can not found the conditionals for AFTER INSERT OF COL2 and AFTER
DELETE OF COL2, please, 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&amp;DI=1055&amp;HL=Footer_mailsenviados_proteccioninfantil

On Tue, Nov 01, 2005 at 12:33:47AM +0100, Rafael Montoya wrote:

I have this statement in oracle:

CREATE OR REPLACE TRIGGER trig
AFTER INSERT OR UPDATE OR DELETE OF column2 <<----- Here is the doubt
ON table_product
FOR EACH ROW
BEGIN
...
END

Migrating to PostgreSQL, the conditionals for AFTER UPDATE OF COLUMN2 in
trig() are:

IF NEW.column2 <> OLD.column2 OR
(NEW.column2 IS NULL) <> (OLD.column2 IS NULL) THEN
...
END IF;

A simpler condition would be

IF NEW.column2 IS DISTINCT FROM OLD.column2 THEN
...
END IF;

IS DISTINCT FROM is like <> except that it works with NULL:

NULL IS DISTINCT FROM NULL -- false
NULL IS DISTINCT FROM something -- true

If you're using the same function for insert, update, and delete
triggers then you'll need to check TG_OP before executing the above
code; otherwise you'll get an error like 'record "old" is not
assigned yet'.

IF TG_OP = 'UPDATE' THEN
IF NEW.column2 IS DISTINCT FROM OLD.column2 THEN
...
END IF;
END IF;

The nested IF is necessary because you can't depend on short-circuiting
as in some other languages.

but, i can not found the conditionals for AFTER INSERT OF COL2 and AFTER
DELETE OF COL2, please, give me a hand.

Does a column list affect trigger behavior for inserts and deletes?
I don't see those behaviors defined in SQL:2003:

<trigger event> ::=
INSERT
| DELETE
| UPDATE [ OF <trigger column list> ]

What, if anything, is different between "AFTER INSERT OF COL2" and
a simple "AFTER INSERT"?

--
Michael Fuhr