Link to home
Start Free TrialLog in
Avatar of cxm928
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?
Avatar of Robson
Robson
Flag of Poland image

Hi,
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();
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
Avatar of Robson
Robson
Flag of Poland image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
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.
Avatar of cxm928
cxm928

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...
Avatar of earth man2
You only really need update trigger.  You can set default value to be now()
eg
create table_1 ( create_ts timestamp default now() );