title: Collapsing Results of a Union in a Postgres View
body: <h2>Basic Question</h2>
  
  <p>Is there a way in Postgres 9.2 to create a view that aggregates data from two tables, with a bit of logic involved?</p>
  
  <p>The logic we need is:</p>
  
  <ol>
  <li>Data in one table takes precedence over the other</li>
  <li>Subsequent rows with the same <code>key</code> (a combination of multiple fields) overwrite previous ones.</li>
  <li>Rows with a status of <code>D</code> delete any preceding rows for a given 'key'.</li>
  </ol>
  
  <h2>More Details and Example</h2>
  
  <p>I have 2 schemas in a Postgres database. They each have identical tables and columns, but different data.  One is for official data, and one is for proposed changes.</p>
  
  <p>Note: I'm sure there is a better way, but this is a legacy setup and can't be changed.  This is a very simplified, fictionalized example, but shows the situation and result I need.</p>
  
  <p>So we have a table of characteristics, describing a widget. There is only one of each type of data in the official data (a widget will have one size, one color, etc).</p>
  
  <p>Proposed changes, once approved, change the official data. There may be multiple pending changes for a given type of data.</p>
  
  <h3><code>official</code> schema</h3>
  
  <pre><code>CREATE TABLE characteristics (
      widget_id      integer NOT NULL,
      variation_id   integer NOT NULL,
      value          varchar(10),
      action_date    date,
      status         char(1)
  );
  </code></pre>
  
  <p>Sample data in <code>official.characteristics</code>:</p>
  
  <pre><code>1,1,GI Joe,12/25/2012,C
  1,2,Green,12/25/2012,C
  1,3,M,12/25/2012,C
  1,4,Plastic,12/25/2012,C
  2,1,GI Joe,12/25/2012,C
  2,2,Green,12/25/2012,C
  2,3,L,12/25/2012,C
  2,4,Plastic,12/25/2012,C
  </code></pre>
  
  <p>So we have 2 widgets, one is medium, green and plastic.  One is large, green, and plastic.</p>
  
  <h3><code>proposed</code> schema</h3>
  
  <pre><code>CREATE TABLE characteristics (
      widget_id      integer NOT NULL,
      variation_id   integer NOT NULL,
      value          varchar(10),
      action_date    date,
      status         char(1)
  );
  </code></pre>
  
  <p>Sample data in <code>proposed.characteristics</code>:</p>
  
  <pre><code>1,2,Blue,2/22/2013,C
  1,4,Plastic,2/22/2013,D
  2,2,Purple,2/10/2013,C
  2,2,Green,2/22/2013,D
  2,3,XL,2/22/2013,C
  </code></pre>
  
  <p>If we want to see what the result of all the proposed changes would be, we can query both tables, with newer data replacing old, or D rows deleting any previous ones.</p>
  
  <pre><code>SELECT
    'o' as src,
    lpad(widget_id::text,4,'0'::text) || '_' || lpad(variation_id::text,4,'0'::text) as key,
    *
  FROM
    proposed.characteristics
  ORDER BY
    key ASC,
    action_date::date ASC
  </code></pre>
  
  <p>The 2nd query is the same, but on the other table and with 'p' as the src.</p>
  
  <p>Using PHP, I can query each table, official first, proposed changes second, and put the data into an array with the <code>key</code> (<code>widget_id || '_' || variation_id</code>) as key. Any new rows overwrite old ones. If the <code>status</code> is <code>D</code> (for delete), then the row with the key is deleted (though a subsequent proposed change may re-add it).</p>
  
  <p>So for the data above we would end up with:</p>
  
  <pre><code>o,0001_0001,1,1,GI Joe,12/25/2012,C
  p,0001_0002,1,2,Blue,2/22/2013,C
  o,0001_0003,1,3,M,12/25/2012,C
  o,0002_0001,1,1,GI Joe,12/25/2012,C
  p,0002_0003,2,3,XL,2/22/2013,C
  o,0002_0004,2,4,Plastic,12/25/2012,C
  </code></pre>
  
  <h3>Summary</h3>
  
  <p>Is there a way I can create a view where I can directly query the results above?<br>
  And still have the <code>D</code> for delete work, and newer changes overwrite previous changes or official data?</p>