Tree with recursive and default

Question

Using Postgres.

I have a pricelists

CREATE TABLE pricelists(
  id SERIAL PRIMARY KEY,
  name TEXT,
  parent_id INTEGER REFERENCES pricelists
);

and another table, prices, referencing it

CREATE TABLE prices(
  pricelist_id INTEGER REFERENCES pricelists,
  name TEXT,
  value INTEGER NOT NULL,
  PRIMARY KEY (pricelist_id, name)
);

• Parent pricelist id=1 may have 10 prices.
• Pricelist id=2 as a child of parent 1 may have 5 prices which override parent 1 prices of the same price name.
• Child Pricelist id=3 as as a child of pricelist 2 may have 2 price which override child 2 prices of the same price name.

Thus when I ask for child 3 prices, I want to get

• all prices of child 3 and
• those prices of his parent (child 2) that do not exists in child 3 and
• all parent 1 prices that do not exists until now.

The schema can be changed in order to be efficient.

Example:

If

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;

gives

| 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      |

then I'm looking for a way of fetching pricelist_id = 3 prices that'd give me

| id       |      parent   |  price_name |     value   |  
|----------|:-------------:|------------:|------------:|  
| 1        |  1            | cc          |     10      |  
| 2        |  1            | bb          |     20      |  
| 3        |  2            | aa          |     30      |

Answer 1

WITH RECURSIVE cte AS (
   SELECT id, name, parent_id, 1 AS lvl
   FROM   pricelists
   WHERE  id = 3  -- provide your id here

   UNION ALL
   SELECT pl.id, pl.name, pl.parent_id, c.lvl + 1
   FROM   cte  c
   JOIN   pricelists pl ON pl.id = c.parent_id
   )
SELECT DISTINCT ON (p.price_name)
       c.id, c.parent_id, p.price_name, p.value
FROM   cte c
JOIN   prices p ON p.pricelist_id = c.id
ORDER  BY p.price_name, c.lvl;  -- lower lvl beats higher level

• Use a recursive CTE like here:

  • Total children values based on parent
  • Recursive SELECT query to return rates of arbitrary depth?

  There are many related answers.

• Join to prices once at the end, that's cheaper.

• Use DISTINCT ON the get the "greatest per group":

  • Select first row in each GROUP BY group?