Re: Referencing external table in update/insert triggers - Mailing list pgsql-sql

On 17/02/10 15:18, Amitabh Kant wrote:

CREATE OR REPLACE FUNCTION update_data() RETURNS TRIGGER AS $update_data$
BEGIN
    IF NEW.t1f4>  t2.t2f4
        UPDATE t2 set t2f2=NEW.t1f2, t2f3=NEW.t1f3, t2f4=NEW.t1f4 where
t2f1=NEW.d1;
        RETURN NEW;
    END IF;
END;
$update_data$ LANGUAGE plpgsql;

I would like to compare the date present in the t2f4 with the new data being
updated through the trigger.  Using the if line as listed above returns an
error.

You can always do something like:
 SELECT t2.t2f4 INTO my_variable FROM t2 WHERE ...
 IF NEW.t1f4 > my_variable

However, for this case you can just do an update with an extended where clause:
 UPDATE t2 ... WHERE t2f1=NEW.d1 AND NEW.t1f4 > t2.t2f4

No need for the IF.

--
 Richard Huxton
 Archonet Ltd