Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

lastmod and create timestamps for records

Posted on 2005-03-29
6
Medium Priority
?
339 Views
Last Modified: 2008-03-10
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?
0
Comment
Question by:cxm928
  • 4
6 Comments
 
LVL 4

Expert Comment

by:Robson
ID: 13654303
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();
0
 
LVL 4

Expert Comment

by:Robson
ID: 13654317
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 :)
0
 
LVL 4

Accepted Solution

by:
Robson earned 1000 total points
ID: 13654346
Already noticed one error:

create function set_create_ts()
returns trigger as
'begin
NEW.create_ts:=now();
return NEW;
end' language 'plpgsql';

(missing '()', tested in 8.0 version)

Regards,
Robson.
0
Become an Android App Developer

Ready to kick start your career in 2018? Learn how to build an Android app in January’s Course of the Month and open the door to new opportunities.

 
LVL 4

Expert Comment

by:Robson
ID: 13654384
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.
0
 

Author Comment

by:cxm928
ID: 13654633
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...
0
 
LVL 22

Expert Comment

by:earth man2
ID: 13656118
You only really need update trigger.  You can set default value to be now()
eg
create table_1 ( create_ts timestamp default now() );
0

Featured Post

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Best database to use for Maps is PostgreSQL. This is an open source database. Comes as a package with most Linux OS. For more info visit the following site: http://www.postgresql.org/ (http://www.postgresql.org/) This requires some add-o…
By, Vadim Tkachenko. In this article we’ll look at ClickHouse on its one year anniversary.
Steps to create a PostgreSQL RDS instance in the Amazon cloud. We will cover some of the default settings and show how to connect to the instance once it is up and running.
The Relationships Diagram is a good way to get an overall view of what a database is keeping track of. It is also where relationships are defined. A relationship specifies how two tables connect to each other. As you build tables in Microsoft Ac…
Suggested Courses

571 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question