Re: Auto-updated fields

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:
>> > Alvaro Herrera wrote:
>> > > Robert Treat wrote:
>> > > > On Thursday 08 May 2008 00:27:10 Tino Wildenhain wrote:
>> > > > > David Fetter wrote:
>> > >
>> > > Ref: http://archives.postgresql.org/pgsql-hackers/2008-05/msg00198.php
>> > >
>> > > > > > 1. Create a generic (possibly overloaded) trigger function,
>> > > > > > bundled with PostgreSQL, which sets a field to some value. For
>> > > > > > example, a timestamptz version might set the field to now().
>> > > > >
>> > > > > Having the pre defined triggers at hand could be useful, especially
>> > > > > for people not writing triggers so often to get used to it but I'm
>> > > > > really not happy with the idea of magic preprocessing.
>> > > >
>> > > > I have a generic version of this in pagila.
>> > >
>> > > Now that we have a specific file in core for generic triggers (right now
>> > > with a single one), how about adding this one to it?
>> >
>> > Any progress on this? TODO?
>>
>> I think this is a TODO, but not sure who is working on it or what needs to be
>> done. The generic version in pagila is perhaps not generic enough:
>>
>> 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.

Thus...

create or replace function update_txid () returns trigger as $$
begin
if TG_OP = 'UPDATE' then
NEW.tx_id := currval('some-schema.tx_sequence');
return NEW;
else
raise exception 'tx update requested on non-update request - %', TG_OP;
end if;
return NEW;
end
$$ language plpgsql;

Thus, I'd encourage having the column as well as the kind of value
(timestamp vs sequence value) both being parameters for this.
--
http://linuxfinances.info/info/linuxdistributions.html
Calvin Trillin - "Health food makes me sick."