trigger conversion advice needed - Mailing list pgsql-sql

From Clint Stotesbery
Subject trigger conversion advice needed
Date
Msg-id BAY9-F11pKkVavr8nCy0001de1f@hotmail.com
Whole thread Raw
Responses Re: trigger conversion advice needed  (Tom Lane <tgl@sss.pgh.pa.us>)
List pgsql-sql
I'm working on converting a simple trigger from Oracle to Postgres and I 
have a couple ofl questions that I need some help on please. First here's 
the Oracle trigger:

CREATE OR REPLACE TRIGGER t_ship_date
AFTER UPDATE OR INSERT OF order_date ON orders
BEGIN  UPDATE orders  SET ship_date = working_5days(order_date);
END;
/

When I was working on converting the trigger I noticed that Postgres doesn't 
have the OF table_attribute ON syntax support. I just want the trigger to 
fire when the order_date field in the order table is updated or inserted 
like it is specified in the Oracle trigger specification above. So I did 
this in Postgres:

CREATE OR REPLACE FUNCTION t_ship_date()
RETURNS TRIGGER AS '  BEGIN     UPDATE orders     SET ship_date = working_5days(new.order_date);
     RETURN NEW;  END;  ' LANGUAGE 'plpgsql';

I justed used new.order_date in the Postgres one version. I'm pretty sure 
that this isn't going to work, it will probably update every ship_date which 
is not what I want. I just want to update the ship_date when the record's 
order_date is updated. Is there some way in Postgres to specify a trigger to 
fire only when a certain field in a table is changed or inserted (like I had 
in the Oracle version)? I couldn't find anything for triggers to do that in 
the docs so I hope someone can shed some light on this for me. I thought 
maybe something like this could work too (probably closer to being correct):

CREATE OR REPLACE FUNCTION t_ship_date()
RETURNS TRIGGER AS '  BEGIN     IF TG_OP = ''INSERT'' THEN        UPDATE orders        SET ship_date =
working_5days(new.order_date)       where order_no = new.order_no;     ELSIF TG_OP =''UPDATE'' THEN        UPDATE
orders       SET ship_date = working_5days(new.order_date)        where order_no = old.order_no;     END IF;
 
     RETURN NEW;  END;  ' LANGUAGE 'plpgsql';

The working_5days function just adds 5 business days to a date fyi. Now the 
second question I have is due to an error I keep getting when I try and make 
the trigger definition below:

CREATE TRIGGER t_ship_date AFTER UPDATE OR INSERT ON orders
EXECUTE PROCEDURE t_ship_date();

I always get a parse error at or near execute. What am I doing wrong? If I 
could get the trigger to compile then I could test it to see if it works the 
same as the Oracle version. I wouldn't even have to ask the first question 
if I could get it to compile but I thought I might as well ask anyway so I 
don't have to post another message. Thanks for the help.
-Clint

P.S. 7.3.4 is the postgres version on the server.

_________________________________________________________________
Has one of the new viruses infected your computer?  Find out with a FREE 
online computer virus scan from McAfee. Take the FreeScan now!  
http://clinic.mcafee.com/clinic/ibuy/campaign.asp?cid=3963



pgsql-sql by date:

Previous
From: Richard Huxton
Date:
Subject: Re: Unsigned numbers
Next
From: mohan@physics.gmu.edu
Date:
Subject: Problem: Postgresql not starting