cxm928
asked on
lastmod and create timestamps for records
For all tables in my database I have columns create_ts and last_mod_ts, which represent the create and last modification timestamps for each record. Is there a way to have PostgreSQL set these values on inserts and updates, rather than have the application pass these along with each query?
With some effort you could write a function that creates triggers and run it for each table in your database that contains theese fields -- but it's worth only in you have a great lot of tables :)
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Note that now() returns transaction start time, not current time, and transactions may be very long! If you want to use this fields for data replication use timenow() instead of now() (I didn't and regretted ;).
Robson.
Robson.
ASKER
Robson,
Your thoughts and advice on using triggers is very helpful. I am surprised that this is not something that is supported by the database itself, rather than having to setup triggers. But, the solution you suggest seems worth trying. I'm not sure of the performance impact, but the desired behavior is achieved.
Thanks...
Your thoughts and advice on using triggers is very helpful. I am surprised that this is not something that is supported by the database itself, rather than having to setup triggers. But, the solution you suggest seems worth trying. I'm not sure of the performance impact, but the desired behavior is achieved.
Thanks...
You only really need update trigger. You can set default value to be now()
eg
create table_1 ( create_ts timestamp default now() );
eg
create table_1 ( create_ts timestamp default now() );
I'm not testing it but writting 'online', hope it helps
create function set_create_ts returns trigger as
'begin
NEW.create_ts:=now();
return NEW;
end' language 'plpgsql';
create function set_last_mod_ts returns trigger as
'begin
NEW.create_ts:=now();
return NEW;
end' language 'plpgsql';
And for each TABLENAME in your database:
create trigger set_create_ts before insert on TABLENAME for each row execute procedure set_create_ts();
(altertative: create field create_ts with default value now() -- but whill work only if you skip create_ts in INSERT command).
create trigger set_create_ts before update on TABLENAME for each row execute procedure set_last_mod_ts();