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!

Kouber Saparev · Accepted Answer · 2016-03-26 07:29:18Z

52

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();

edited Mar 26, 2016 at 7:29

answered Jan 5, 2012 at 16:10

Kouber Saparev

8,2552 gold badges32 silver badges28 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Joseloman Over a year ago

Just to complement this very good answer: change "NEW.lastmodified" to the respective name of this field in your table. For example, if I have a column with name "updated", the FUNCTION would be "NEW.updated".

duffymo · Accepted Answer · 2012-01-05 10:37:00Z

9

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

edited Jan 5, 2012 at 10:37

answered Jan 5, 2012 at 10:18

duffymo

310k46 gold badges377 silver badges572 bronze badges

Collectives™ on Stack Overflow

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

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related