Re: Auto-updated fields

Christopher Browne wrote:
>On Wed, Feb 4, 2009 at 1:23 PM, Bruce Momjian <bruce(at)momjian(dot)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
$$
BEGIN
IF OLD.lastupdate=NEW.lastupdate
THEN
NEW.lastupdate:=CURRENT_TIMESTAMP;
ELSIF OLD.lastupdate IS NULL OR NEW.lastupdate IS NULL
THEN
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.