Question posted in 
In the query I shared below, I use 3 recursive loops to cumulatively retrieve debit, credit and budget values. Can I do this using 1 recursion instead of 3 recursions?
Also, can it be done in this format without changing the format of the query? Briefly, what I want to do in the query is to cumulatively reflect the debit, credit and budget (debit-credit) of the sub-account codes to a parent account for the account codes in accounting, and thus reflect the cumulative debit, credit and budget values to the main account at the top.
select hkodu.id,
hkodu.hesap_kodu as hesapKodu,
hkodu.hesap_adi as hesapAdi,
hkodu.parent_hesap_kodu_id as parentId,
btc.butce_yili as butceYili,
hkodu.active as active,
hkodu.owner_birim_id as birimId,
(WITH recursive tree_hk AS (SELECT h.id, h.hesap_kodu, h.hesap_adi, h.parent_hesap_kodu_id, b.borc, b.alacak
FROM hesap_kodu h
JOIN butce b ON h.id = b.hesap_kodu_id
where h.id = hkodu.id
and h.active = true
UNION ALL
SELECT hk.id,
hk.hesap_kodu,
hk.hesap_adi,
hk.parent_hesap_kodu_id,
bc.borc,
bc.alacak
FROM hesap_kodu hk
JOIN butce bc ON hk.id = bc.hesap_kodu_id
JOIN tree_hk tr ON tr.id = hk.parent_hesap_kodu_id
where hk.active = true)
select sum(borc - alacak)
from tree_hk) as butce,
(WITH recursive tree_hk AS (SELECT h.id, h.hesap_kodu, h.hesap_adi, h.parent_hesap_kodu_id, b.borc, b.alacak
FROM hesap_kodu h
JOIN butce b ON h.id = b.hesap_kodu_id
where h.id = hkodu.id
and h.active = true
UNION ALL
SELECT hk.id,
hk.hesap_kodu,
hk.hesap_adi,
hk.parent_hesap_kodu_id,
bc.borc,
bc.alacak
FROM hesap_kodu hk
JOIN butce bc ON hk.id = bc.hesap_kodu_id
JOIN tree_hk tr ON tr.id = hk.parent_hesap_kodu_id
where hk.active = true)
select sum(alacak)
from tree_hk) as alacak,
(WITH recursive tree_hk AS (SELECT h.id, h.hesap_kodu, h.hesap_adi, h.parent_hesap_kodu_id, b.borc, b.alacak
FROM hesap_kodu h
JOIN butce b ON h.id = b.hesap_kodu_id
where h.id = hkodu.id
and h.active = true
UNION ALL
SELECT hk.id,
hk.hesap_kodu,
hk.hesap_adi,
hk.parent_hesap_kodu_id,
bc.borc,
bc.alacak
FROM hesap_kodu hk
JOIN butce bc ON hk.id = bc.hesap_kodu_id
JOIN tree_hk tr ON tr.id = hk.parent_hesap_kodu_id
where hk.active = true)
select sum(borc)
from tree_hk) as borc
from hesap_kodu hkodu
left join butce btc on btc.hesap_kodu_id = hkodu.id;
2
Answers
Sure, you need only to call it once and than canrefer to it as often as you need
Without some sample data and expected result it is hard to tell, but maybe you could get it without recursions. If it is possible to make correct partition by and order by in Sum() Over() analytic function with windowing clause – then it would look something like below. Think that in postgres windowing clause (Rows Between/Range) could be ommited as it defaults to all preceding till current row.
Once again, not sure, but might be worth to try – it’s up to you.