Message-ID: <4B7D0704.70301@archonet.com>
Date: Thu, 18 Feb 2010 09:23:16 +0000
From: Richard Huxton <dev@archonet.com>
User-Agent: Mozilla/5.0 (X11; U; Linux i686; en-US;
	rv:1.9.1.9pre) Gecko/20100213 Lightning/1.0b1 Shredder/3.0.2pre
MIME-Version: 1.0
To: Amitabh Kant <amitabhkant@gmail.com>
CC: pgsql-sql@postgresql.org
Subject: Re: Referencing external table in update/insert triggers
References: <84b68b3d1002170718s5da61fam69de31ad1e54595f@mail.gmail.com>
In-Reply-To: <84b68b3d1002170718s5da61fam69de31ad1e54595f@mail.gmail.com>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

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