Re: Auto-updated fields

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.