Postgresql - update rule - possible to have a last modified date, automatically updated "on update" of that row?

Question

I want to have a "lastmodified" timestamp (or datetime? not sure if it makes a difference other than presentation of the data) to log the last modified date/time of that record's entry.

Apparently this is possible using triggers. Since I haven't used triggers before, I thought I could first try an "update rule" since that is new to me too:

http://www.postgresql.org/docs/8.3/static/rules-update.html

What I have is this table to log a customer's session data:

CREATE TABLE customer_session (
    customer_sessionid serial PRIMARY KEY,
    savedsearch_contents text,
    lastmodified timestamp default now()
); /* 
    @ lastmodified - should be updated whenever the table is updated for this entry, just for reference.
     */

Then I could create a rule like this. I'm not sure about the syntax, or whether to use NEW or OLD. Could anyone advise the correct syntax?

CREATE RULE customer_session_lastmodified AS 
ON UPDATE TO customer_session
DO UPDATE customer_session SET lastmodified = current_timestamp WHERE customer_sessionid = NEW.customer_sessionid

As you can see I want to update the lastmodified entry of THAT customer_sessionid only, so I'm not sure how to reference it. The UPDATE query would be like this:

UPDATE customer_session SET savedsearch_contents = 'abcde' 
WHERE customer_sessionid = {unique customer ID}

Many thanks!

Answer 1

You cannot do it with a rule, since it would create an infinite recursion. The correct way is to create a before trigger, just as duffymo proposed.

CREATE FUNCTION sync_lastmod() RETURNS trigger AS $$ BEGIN   NEW.lastmodified := NOW();    RETURN NEW; END; $$ LANGUAGE plpgsql;  CREATE TRIGGER   sync_lastmod BEFORE UPDATE ON   customer_session FOR EACH ROW EXECUTE PROCEDURE   sync_lastmod(); 

Answer 2

You could write a trigger that would fire BEFORE UPDATE to modify that date.

See Example 39-4, which adds user name and timestamp before an UPDATE:

http://www.postgresql.org/docs/current/static/plpgsql-trigger.html