title: Postgresql - update rule - possible to have a last modified date, automatically updated "on update" of that row?
body: <p>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.</p>
  
  <p>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:</p>
  
  <p><a href="http://www.postgresql.org/docs/8.3/static/rules-update.html" rel="noreferrer">http://www.postgresql.org/docs/8.3/static/rules-update.html</a></p>
  
  <p>What I have is this table to log a customer's session data:</p>
  
  <pre><code>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.
       */
  </code></pre>
  
  <p>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?</p>
  
  <pre><code>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
  </code></pre>
  
  <p>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:</p>
  
  <pre><code>UPDATE customer_session SET savedsearch_contents = 'abcde' 
  WHERE customer_sessionid = {unique customer ID}
  </code></pre>
  
  <p>Many thanks!</p>