Christopher Browne wrote:
>On Wed, Feb 4, 2009 at 1:23 PM, Bruce Momjian <bruce@momjian.us> wrote:
>> Robert Treat wrote:
>>> On Wednesday 21 January 2009 20:21:41 Bruce Momjian wrote:
>>> CREATE FUNCTION last_updated() RETURNS trigger
>>> AS $$
>>> BEGIN
>>> NEW.last_update = CURRENT_TIMESTAMP;
>>> RETURN NEW;
>>> END $$
>>> LANGUAGE plpgsql;
>>> It requires you name your column last_update, which is what the naming
>>> convention is in pagila, but might not work for everyone. Can someone work
>>> with that and move forward? Or maybe give a more specific pointer to the
>>> generic trigger stuff (I've not looked at it before)
>> Well, I thought it was a good idea, but no one seems to want to do the
>> work.
>I'd like to see more options than that, which, it seems to me,
>establishes a need for more design work.
>Another perspective on temporality is to have a "transaction column"
>which points (via foreign key) to a transaction table, where you would
>use currval('transaction_sequence') as the value instead of
>CURRENT_TIMESTAMP.
I use the following:
CREATE OR REPLACE FUNCTION lastupdate() RETURNS TRIGGER AS $$
BEGINIF OLD.lastupdate=NEW.lastupdateTHEN NEW.lastupdate:=CURRENT_TIMESTAMP;ELSIF OLD.lastupdate IS NULL OR
NEW.lastupdateIS NULLTHEN RAISE EXCEPTION 'Concurrent modification of table %',TG_ARGV[0];END IF;RETURN NEW;
END;$$ LANGUAGE PLPGSQL;
Which allows detection of concurrent updates on the same page (if the
lastupdate value is being fetched before the update-template is filled).
--
Sincerely, Stephen R. van den Berg.
Auto repair rates: basic labor $40/hour; if you wait, $60; if you watch, $80;
if you ask questions, $100; if you help, $120; if you laugh, $140.