title: postgres update a date field when a boolean field is set to true
body: <p>For the sake of the example, consider a table</p>
  
  <pre><code>create table foo (
    contents text NOT NULL,
    is_active boolean NOT NULL DEFAULT false,
    dt_active date
  )
  </code></pre>
  
  <p>I insert a record:</p>
  
  <pre><code>insert into foo (contents) values ('bar')
  </code></pre>
  
  <p>So far, so good. Later on, I now want to 'activate' the record:</p>
  
  <pre><code>update foo set is_active = true
  </code></pre>
  
  <p>What I would like to do when <code>is_active</code> is changed from <code>false</code> to <code>true</code>, is for <code>dt_active</code> is set to <code>now()</code>. For bonus points it would be nice if <code>is_active</code> is changed from <code>true</code> to <code>fals</code>e, dt_active is set to null, but I can live without that.</p>
  
  <p>I'd really like to push this housekeeping into the database, it would make the client code much cleaner (since many tables (and even column tuples within tables) could benefit from this technique).</p>
  
  <p>I'm stumped as to how to pull out the current record in the database in the trigger (I'm using plpgsql), in order to compare the "then" with the "now". Pointers to code examples or snippets greatly appreciated.</p>