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@xxxxxxxxxx>
Reply-To: olly@xxxxxxxxxx
To: Rafael Montoya <rafo-mm@xxxxxxxxxxx>
CC: pgsql-general@xxxxxxxxxxxxxx
Subject: Re: 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@xxxxxxxxxx
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
---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?
http://archives.postgresql.org
