From: Amitabh Kant <amitabhkant@gmail.com>
To: Richard Huxton <dev@archonet.com>
Cc: pgsql-sql@postgresql.org
Subject: Re: Referencing external table in update/insert triggers
Date: Thu, 18 Feb 2010 15:03:36 +0530
Message-ID: <84b68b3d1002180133x549f2b07p6d40682cd94d1d86@mail.gmail.com> (raw)
In-Reply-To: <4B7D0704.70301@archonet.com>
References: <84b68b3d1002170718s5da61fam69de31ad1e54595f@mail.gmail.com>
<4B7D0704.70301@archonet.com>
On Thu, Feb 18, 2010 at 2:53 PM, Richard Huxton <dev@archonet.com> wrote:
> 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
>
Thanks Richard.
With regards
Amitabh Kant
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: amitabhkant@gmail.com, dev@archonet.com
Subject: Re: Referencing external table in update/insert triggers
In-Reply-To: <84b68b3d1002180133x549f2b07p6d40682cd94d1d86@mail.gmail.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox