Search Postgresql Archives

Re: Is postorder tree traversal possible with recursive CTE's?

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 





On 06/19/2018 01:14 PM, Hellmuth Vargas wrote:

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.



[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]

  Powered by Linux