Re: function DECODE and triggers

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