CODEWITHSUNDEEP
postgresql
rishijd
rishijd

Reputation: 1344

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

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!

Upvotes: 33

Views: 27958

Answers (2)

Kouber Saparev
Kouber Saparev

Reputation: 8115

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

Upvotes: 52

duffymo
duffymo

Reputation: 308938

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

Upvotes: 9

Related Questions