Re: function DECODE and triggers

From: Oliver Elphick <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: function DECODE and triggers
Date: 2005-10-25 09:15:17
Message-ID: 1130231717.30145.106.camel@linda.lfix.co.uk
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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

In response to

Responses

Browse pgsql-general by date

  From Date Subject
Next Message WireSpot 2005-10-25 09:18:34 Re: Deleting vs foreign keys
Previous Message Martijn van Oosterhout 2005-10-25 08:56:45 Re: Installation Problem