Dear List,
I've recently found that it would be useful if I could update a timestamp field in some of my tables automatically so
thatwhenever someone updates the tables in question, the timestamp reflects when that change was made.
The thing is, I also want to be able to update the timestamp field if I want to... thus if I update, say, the 'name'
field- the timestamp field would be updated to 'now()'. However, if I updated the timestamp field then that field
wouldsimply take on the value I had suggested.
I managed to find a partially working solution as follows:
CREATE FUNCTION update_modtime() RETURNS opaque AS 'BEGIN IF OLD.modtime = NEW.modtime THEN NEW.modtime = now() END IF;
RETURNNEW; END;' LANGUAGE 'plpgsql';
CREATE TRIGGER autostamp BEFORE UPDATE ON <table-name> FOR EACH ROW EXECUTE PROCEDURE update_modtime();
The problem with this is, if I do UPDATE <table-name> set modtime = <existing-value> WHERE <condition> where
<existing-value>is the current value of modtime, modtime then takes the value of now(). But if I'm explicitly setting
itto its existing value, I don't want the trigger to do this. Of course, I could modify my scripts and stuff so that,
ifI don't want modtime to change, I don't try to change it. But I'm not the only user - and I think the behaviour
wouldbe more pleasant for the different users if modtime always updated to what was specified, if a user was explicitly
settingit.
Thanks for your time,
Any help greatly appreciated.
Rajit
