Re: [SQL] Adding a rule to update a last_changed field - Mailing list pgsql-sql

This does not work, as it still calls a new UPDATE and re-invokes the rule.


> >   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 SET
>     last_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.