Hierarchical Data

Recursive queries are typically used to deal with hierarchical or tree-structured data, such as chart of accounts (COA). COA is a listing all the accounts in the general ledger in such a way that forms a hierarchical structure, for example the following:


acc_
acc_id parid acc_code     acc_name                    acc_level
-----------------------------------------------------------------
1           10.0000.0000 AKTIVA                         0
2 1 11.0000.0000 AKTIVA LANCAR                   1
3 2 11.1000.0000 Kas dan Setara Kas                2
4  3 11.1100.0000 Kas                            3
5  4 11.1110.0000 Kas - Program Kemitraan          4
6  4 11.1120.0000 Kas - Bina Lingkungan             4
7  3 11.1200.0000 Bank                            3
8  7 11.1210.0000 Bank - Program Kemitraan        4
....
....
116  97 22.0000.0000 KEWAJIBAN JANGKA PANJANG 1
117  116 22.2000.0000 Hutang Kepada BUMN Pembina Lain  2
119  116 22.9000.0000 Hutang Jangka Panjang Lainnya     2
122          30.0000.0000 AKTIVA BERSIH                   0
123  122 33.0000.0000 PENYISIHAN                      1



The list is generated by running the following SQL command:


WITH RECURSIVE tree AS (SELECT acc_id,acc_code, acc_parid, acc_name ,0 AS acc_level 
FROM tova.coa WHERE acc_parid IS NULL
UNION ALL
SELECT c.acc_id, c.acc_code, c.acc_parid, c.acc_name, acc_level+1 AS acc_level 
FROM tova.coa c JOIN tree t ON t.acc_id  = c.acc_parid)
SELECT acc_id, acc_parid, tova.getakunttk(acc_code), acc_name, acc_level 
FROM tree b order by 3;


I save the transaction only at the lowest level account, of course user still can see reports for each account in a hierarchy. The stored function below will display accounts hierarchy.

CREATE OR REPLACE FUNCTION tova.getcoa_allchildofparentrec(IN pacc_parid integer) 
RETURNS TABLE (acc_id integer,acc_code character varying, acc_name character varying) AS
$$
BEGIN
    RETURN QUERY WITH RECURSIVE tree AS (
            SELECT a.acc_id, a.acc_code as acc_code,a.acc_name 
               FROM tova.coa a WHERE acc_parid = $1
            UNION ALL
            SELECT a.acc_id, a.acc_code as acc_code,a.acc_name 
               FROM tova.coa a, tree t WHERE t.acc_id = a.acc_parid)
    SELECT h.acc_id,h.acc_code, h.acc_name FROM tree h;
END;
$$ LANGUAGE plpgsql;


To display the coa's tree, just call this:
select acc_code, acc_name from ferry.getcoa_allchildofparentrec(34) order by 1;


acc_code acc_name
---------------------------------------------------------------------
11.3610.0000 Piutang Pinjaman Mitra Binaan - Sektor Industri
11.3610.6108 PPMB Sektor Industri - Kab. Landak
11.3610.6103 PPMB Sektor Industri - Kab. Sanggau
11.3610.6301 PPMB Sektor Industri - Kab. Tanah Laut
11.3610.6309 PPMB Sektor Industri - Kab. Tabalong
11.3610.6105 PPMB Sektor Industri - Kab. Sintang
11.3610.6303 PPMB Sektor Industri - Kab. Banjar
11.3610.6100 PPMB Sektor Industri Prov. Kalimantan Barat
11.3610.6171 PPMB Sektor Industri - Kota Pontianak
11.3620.0000 Piutang Pinjaman Mitra Binaan - Sektor Perdagangan
11.3630.0000 Piutang Pinjaman Mitra Binaan - Sektor Pertanian
11.3640.0000 Piutang Pinjaman Mitra Binaan - Sektor Peternakan
11.3650.0000 Piutang Pinjaman Mitra Binaan - Sektor Perkebunan
11.3660.0000 Piutang Pinjaman Mitra Binaan - Sektor Perikanan
11.3670.0000 Piutang Pinjaman Mitra Binaan - Sektor Jasa
11.3680.0000 Piutang Pinjaman Mitra Binaan - Sektor Lain
11.3610.6300 Sektor Industri Prov. Kalimantan Selatan
11.3610.6310 PPMB - Sektor Industri - Kab. Tanah Bumbu
...
...
11.3670.6171 Piutang Pinjaman Mitra Binaan - Sektor Jasa - Kota Pontianak
11.3660.6171 Piutang Pinjaman Mitra Binaan - Sektor Perikanan - Kota Pontianak
11.3680.6171 Piutang Pinjaman Mitra Binaan - Sektor Lain - Kota Pontianak