Re: Is postorder tree traversal possible with recursive CTE's? - Mailing list pgsql-general

Hi

with partial sum:

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

          join recipe on (recipe.name = pizza.ingredient)

)

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)

This is gorgeous but I suspect any level greater than 10 wide will present sorting problems, no?  Maybe a fixed two-digit, zero filled number per level? Pushing the problem off by an order of magnitude :)
An exercise left to the OP perhaps.