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
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 |