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

From Hellmuth Vargas
Subject Re: Is postorder tree traversal possible with recursive CTE's?
Date
Msg-id CAN3Qy4pAZJFnbALEUFLVCc5ZXqLRcnpd-UZmvnw1UP_ZFV__uw@mail.gmail.com
Whole thread Raw
In response to Re: Is postorder tree traversal possible with recursive CTE's?  (Rob Sargent <robjsargent@gmail.com>)
List pgsql-general
Hi

This is gorgeous but I suspect any level greater than 10 wide will present sorting problems, no? 


no, it should not be inconvenient

 Maybe a fixed two-digit, zero filled number per level? 

neither

Pushing the problem off by an order of magnitude     :)
An exercise left to the OP perhaps.



El mar., 19 de jun. de 2018 a la(s) 14:52, Rob Sargent (robjsargent@gmail.com) escribió:



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)







--
Cordialmente,

Ing. Hellmuth I. Vargas S.
Esp. Telemática y Negocios por Internet 
Oracle Database 10g Administrator Certified Associate
EnterpriseDB Certified PostgreSQL 9.3 Associate

pgsql-general by date:

Previous
From: Rob Sargent
Date:
Subject: Re: Is postorder tree traversal possible with recursive CTE's?
Next
From: Rob Sargent
Date:
Subject: Re: Run Stored procedure - function from VBA