with recursive pizza (name, step, ingredient, quantity,
unit, rel_qty, path, weight)
as (
select
name, step, ingredient, quantity, unit
, quantity::numeric(10,2)
, step::text
, case when unit = 'g' then
quantity::numeric(10,2) else null end
from recipe
where name = 'pizza'
union all
select
recipe.name,
recipe.step, recipe.ingredient, recipe.quantity, recipe.unit
, (pizza.rel_qty *
recipe.quantity)::numeric(10,2)
, pizza.path || '.' || recipe.step
, case when recipe.unit = 'g' then
(pizza.rel_qty * recipe.quantity)::numeric(10,2) else null
end
from pizza
)
select path, ingredient, quantity, rel_qty, unit, weight,sum(weight)
over(partition by split_part(path,'.',1)) as
parcial_weight, sum(weight) over() as total_weight
from pizza
order by path;
path | ingredient |
quantity | rel_qty | unit | weight | parcial_weight |
total_weight
-------+--------------+----------+---------+-------+--------+----------------+--------------
1 | tomato sauce
| 1.00 | 1.00 | pcs | | 113.00 |
313.00
1.1 | tomato
| 100.00 | 100.00 | g | 100.00 | 113.00 |
313.00
1.2 | basil
| 10.00 | 10.00 | g | 10.00 | 113.00 |
313.00
1.3 | salt
| 3.00 | 3.00 | g | 3.00 | 113.00 |
313.00
2 | pizza bottom
| 1.00 | 1.00 | pcs | | 200.00 |
313.00
2.2 | dough
| 1.00 | 1.00 | pcs | | 200.00 |
313.00
2.2.1 | flour
| 150.00 | 150.00 | g | 150.00 | 200.00 |
313.00
2.2.2 | water
| 50.00 | 50.00 | g | 50.00 | 200.00 |
313.00
2.2.3 | salt
| 1.00 | 1.00 | pinch | | 200.00 |
313.00
(9 rows)
