title: Tree with recursive and default
body: <p>Using Postgres.</p>
  
  <p>I have a <code>pricelists</code></p>
  
  <pre><code>CREATE TABLE pricelists(
    id SERIAL PRIMARY KEY,
    name TEXT,
    parent_id INTEGER REFERENCES pricelists
  );
  </code></pre>
  
  <p>and another table, <code>prices</code>, referencing it</p>
  
  <pre><code>CREATE TABLE prices(
    pricelist_id INTEGER REFERENCES pricelists,
    name TEXT,
    value INTEGER NOT NULL,
    PRIMARY KEY (pricelist_id, name)
  );
  </code></pre>
  
  <ul>
  <li>Parent pricelist <code>id=1</code> may have 10 prices.</li>
  <li>Pricelist <code>id=2</code> as a child of parent <code>1</code> may have 5 prices which override parent <code>1</code> prices of the same price name.</li>
  <li>Child Pricelist <code>id=3</code> as as a child of pricelist <code>2</code> may have 2 price which override child <code>2</code> prices of the same price name.</li>
  </ul>
  
  <p>Thus when I ask for child <code>3</code> prices, I want to get</p>
  
  <ul>
  <li>all prices of child <code>3</code> and</li>
  <li>those prices of his parent (child <code>2</code>) that do not exists in child <code>3</code> and</li>
  <li>all parent <code>1</code> prices that do not exists until now.</li>
  </ul>
  
  <p>The schema can be changed in order to be efficient.</p>
  
  <h3>Example:</h3>
  
  <p>If</p>
  
  <pre><code>SELECT pl.id AS id, pl.parent_id AS parent, p.name AS price_name, value
  FROM pricelists pl
  JOIN prices p ON pl.id = p.pricelist_id;
  </code></pre>
  
  <p>gives</p>
  
  <pre><code>| id       |      parent   |  price_name |     value   |  
  |----------|:-------------:|------------:|------------:|  
  | 1        |  1            | bb          |     10      |  
  | 1        |  1            | cc          |     10      |  
  | 2        |  1            | aa          |     20      |  
  | 2        |  1            | bb          |     20      |  
  | 3        |  2            | aa          |     30      |
  </code></pre>
  
  <p>then I'm looking for a way of fetching <code>pricelist_id = 3</code> prices that'd give me</p>
  
  <pre><code>| id       |      parent   |  price_name |     value   |  
  |----------|:-------------:|------------:|------------:|  
  | 1        |  1            | cc          |     10      |  
  | 2        |  1            | bb          |     20      |  
  | 3        |  2            | aa          |     30      |
  </code></pre>