Re: handing created and updated fields

Hi,

Citing "Jim C. Nasby" <decibel@decibel.org>:
> ON INSERT: force created and updated to be current_timestamp
> ON UPDATE: deny updated created. force updated to be set to
> current_timestamp
[snip]
> Does anyone have an example of the best way to handle this scenario?

Something along the lines of the following should work (but test first
anyways, though I have copied smaller parts of this from the definitions
in one of my databases here, I have made modifications to fit your
specific task, so typos/errors might have sneaked in):

create function update_trigger() returns trigger as
'begin
new.created := old.created;
new.updated := CURRENT_TIMESTAMP;
return new;
end;'
language 'plpgsql';

create trigger update_trigger BEFORE UPDATE ON your_table_name
FOR EACH ROW EXECUTE PROCEDURE update_trigger();

create function insert_trigger() returns trigger as
'begin
new.created := CURRENT_TIMESTAMP;
new.updated := CURRENT_TIMESTAMP;
return new;
end;'
language 'plpgsql';

create trigger insert_trigger BEFORE INSERT ON your_table_name
FOR EACH ROW EXECUTE PROCEDURE insert_trigger();

HTH,
Regards,
Daniel