Re: function DECODE and triggers - Mailing list pgsql-general

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