title: How would I create a nested set materialized view in postresql?
body: <p>I have hierarchical parent child data. I would like there to be a nested set view into that data which I could query. It seems that the safest way to maintain this nested set view into the table is for it actually be a postgresql view of raw data.</p>
  
  <pre><code>raw_data (
      id INTEGER PRIMARY KEY,
      parent INTEGER NULL
  )
  </code></pre>
  
  <p>I am looking for a way to transform <code>raw_data</code> in <code>nested_set_view</code> as part of a view create statement.  </p>
  
  <pre><code>nested_set_view (
      id INTEGER PRIMARY KEY,
      lft INTEGER NOT NULL,
      rgt INTEGER NOT NULL
  )
  </code></pre>
  
  <p>For some reason I am imagining that this might be easier or more efficient if <code>nested_set_view</code> were like</p>
  
  <pre><code>nested_set_view (
      id INTEGER PRIMARY KEY,
      lft INTEGER NOT NULL,
      rgt INTEGER NOT NULL,
      root INTEGER NOT NULL
  )
  </code></pre>