Thread: Re: [SQL] Adding a rule to update a last_changed field

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

My original choice was to use a trigger, but I don't want to introduce
C code when I don't have to. I don't believe that making external calls
should be necessary for a query that can be so simply expressed in SQL.

And from the manual: "The procedure name is the C function called"

If triggers can use SQL, I'd be happy to use them, but I can't find a single
example or description on how to do this.

Grant Kaufmann <grant@intekom.com> writes:
> My original choice was to use a trigger, but I don't want to introduce
> C code when I don't have to. I don't believe that making external calls
> should be necessary for a query that can be so simply expressed in SQL.

Who said anything about C code?  I was thinking of a pl/pgsql trigger,
actually.  The pl/pgsql section of the manual (programmer's guide under
procedural languages) has an example of almost exactly this problem.

If you do want to stick with rules then you'll need to make sure that
the underlying insert is to a different table than the one the insert
rule applies to --- in other words, create a view and have users insert
into the view.
        regards, tom lane