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
