Thread: Adding a rule to update a last_changed field

I'm trying to use a rule to maintain a last_changed field. 

Using something like:

CREATE RULE rule1 AS ON UPDATE TO thetable DO UPDATE thetable SET last_changed = now() WHERE id = new.id;

This obviously doesn't work as the DO UPDATE triggers the rule
again, thus looping meaninglessly.

Is there a neat way to work around this? I'd like to just assign
now() to the last_changed field before the actual SQL update
code is run. 

Thanks





-----
Sent using MailStart.com ( http://MailStart.Com/welcome.html )
The FREE way to access your mailbox via any web browser, anywhere!

I'm trying to use a rule to maintain a last_changed field. 
  CREATE RULE rule1 AS    ON UPDATE TO thetable    DO UPDATE thetable SET last_changed = now()    WHERE id = new.id;

Try instead something like
  CREATE RULE rule1 AS    ON UPDATE TO thetable    DO INSTEAD UPDATE thetable SETlast_changed = now(),field1 =
new.field1,field2= new.field2,...fieldn = new.fieldn    WHERE id = new.id;
 

That is, use an INSTEAD rule and replace each field explicitly.

Cheers,
Brook

grant@intekom.com writes:
> I'm trying to use a rule to maintain a last_changed field. 

Use a trigger, not a rule.  You can adjust the last_changed
field before the tuple is stored that way.  There's an example
in the manual's discussion of triggers.
        regards, tom lane