Hi
Many people are asking how to automatically update columns containing a
modification date on updates. I'm wondering if the only solutions in
the current pgsql really are...
- adding "modification=NOW()" to every update query "manually"
- defining a trigger called on updates
While addings things "manually" is quite clumsy, a trigger actually
causes a second update thus slowing down the Db.
As far as I know, rules don't help due to circular conditions (an
update causes an update causes an update...) and functions stil require
to add stuff to each and every update. But I could be wrong. Please -
anyone - enlighten me, us and the world :-)
I'm dreaming of something like the following:
CREATE TABLE table (modified TIMESTAMP NOT NULL DEFAULT NOW(), data
INTEGER)
CREATE RULE table_rule AS ON UPDATE TO table DO ADD modified=NOW()
INSERT INTO TABLE table (data) VALUES (1) <-- modified is defaulted
to NOW()
UPDATE TABLE table SET data=2 <-- modified is implicitly (by rule)
set to NOW()