Re: function DECODE and triggers

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(at)lfix(dot)co(dot)uk>
>Reply-To: olly(at)lfix(dot)co(dot)uk
>To: Rafael Montoya <rafo-mm(at)hotmail(dot)com>
>CC: pgsql-general(at)postgresql(dot)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(at)lfix(dot)co(dot)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