Postgresql – calculate or get price of parent items on bill of materials


i want to calculate or get the price of parent items on bill of materials query

here is the fiddle->

please note in the second row, betax has qty = 2 and the other parent items could behave the same

qty of the first row, always will be 1

what i need is to get the 'price' and 'subtotal' for every parent

Best Answer

the price will surely be null if you tell it to. Initialize the base case for the CTE with 0 instead:

with recursive cte_main as 
    select 'ALFA001'::character varying as id_father
         , 'ALFA001'::character varying as id_son
         , 'ALFA001' as tree 
         , 1::double precision as qty
         , 0::double precision as price
         , 0::double precision as subtotal
         , 'ALFA001'::character varying as sort
         , 0 as levell  

Second, you need to handle cases where the table contains null. You can use COALESCE for that purpose:

    union all
    select e.id_father
         , e.id_son
         , repeat('......',levell+1) || e.id_son as tree
         , e.qty
         , coalesce(e.price,0)
         , e.qty * coalesce(e.price,0) as subtotal
         , sort || ' a- ' || e.id_son::character varying as sort  
         , levell+1 
    from public.main as e
    inner join cte_main 
        on e.id_father = cte_main.id_son 
select * from cte_main 
order by sort

Since all your attributes may contain null, you need to take that into consideration for the other attributes as well.