clear markdown feedback
clear markdown feedback
By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0. 3799194 fiddles created (41759 in the last week).

CREATE TABLE gear_category ( id int PRIMARY KEY , name text , parent_id int ); INSERT INTO gear_category VALUES (1, 'foo' , NULL) , (2, 'bar' , 1) , (3, 'bar1', 2) -- 0 items , (4, 'bar2', 2) -- 2 items , (5, 'bar2', 3) , (6, 'bar2', 3) , (7, 'bar2', 3) -- 3 items ; CREATE TABLE gear_item ( id int , name text , category_id int REFERENCES gear_category ); INSERT INTO gear_item VALUES (1, 'cat1', 1) , (2, 'cat2', 2) , (3, 'cat3', 4) , (4, 'cat4', 4) , (5, 'cat4', 7) , (6, 'cat4', 7) , (7, 'cat4', 7) ;
7 rows affected
7 rows affected
 hidden batch(es)


CREATE OR REPLACE FUNCTION f_tree_ct() RETURNS TABLE (id int, count_direct_child_items int, count_recursive_child_items int) LANGUAGE plpgsql AS $func$ DECLARE _lvl int; BEGIN -- basic table with added path and count CREATE TEMP TABLE t1 AS WITH RECURSIVE tree AS ( SELECT c.id, c.parent_id, '{}'::int[] as path, 0 AS lvl FROM gear_category c WHERE c.parent_id IS NULL UNION ALL SELECT c.id, c.parent_id, path || c.parent_id, lvl + 1 FROM tree t JOIN gear_category c ON c.parent_id = t.id ) , tree_ct AS ( SELECT t.id, t.parent_id, t.path, t.lvl, COALESCE(i.item_ct, 0) AS item_ct FROM tree t LEFT JOIN ( SELECT i.category_id AS id, count(*)::int AS item_ct FROM gear_item i GROUP BY 1 ) i USING (id) ) TABLE tree_ct; -- CREATE INDEX ON t1 (lvl); -- only for very deep trees SELECT INTO _lvl max(lvl) FROM t1; -- identify max lvl to start bottom up -- recursively aggregate each level in 2nd temp table CREATE TEMP TABLE t2 AS SELECT t1.id, t1.parent_id, t1.lvl , t1.item_ct , t1.item_ct AS sum_ct FROM t1 WHERE t1.lvl = _lvl; IF _lvl > 0 THEN FOR i IN REVERSE _lvl .. 1 LOOP INSERT INTO t2 SELECT t1.id, t1.parent_id, t1.lvl, t1.item_ct , CASE WHEN t2.sum_ct IS NULL THEN t1.item_ct ELSE t1.item_ct + t2.sum_ct END FROM t1 LEFT JOIN ( SELECT t2.parent_id AS id, sum(t2.sum_ct) AS sum_ct FROM t2 WHERE t2.lvl = i GROUP BY 1 ) t2 USING (id) WHERE t1.lvl = i - 1; END LOOP; END IF; RETURN QUERY -- only requested columns, unsorted SELECT t2.id, t2.item_ct, t2.sum_ct FROM t2; DROP TABLE t1, t2; -- to allow repeated execution in one transaction RETURN; END $func$;
 hidden batch(es)


SELECT * FROM f_tree_ct() ORDER BY id;
id count_direct_child_items count_recursive_child_items
1 1 7
2 1 6
3 0 3
4 2 2
5 0 0
6 0 0
7 3 3
 hidden batch(es)


WITH RECURSIVE tree AS ( SELECT id, parent_id, ARRAY[id] as path FROM gear_category WHERE parent_id IS NULL UNION ALL SELECT c.id, c.parent_id, path || c.id FROM tree t JOIN gear_category c ON c.parent_id = t.id ) , tree_ct AS ( SELECT t.id, t.path, COALESCE(i.item_ct, 0) AS item_ct FROM tree t LEFT JOIN ( SELECT category_id AS id, count(*) AS item_ct FROM gear_item GROUP BY 1 ) i USING (id) ) SELECT t.id , t.item_ct AS count_direct_child_items , sum(t1.item_ct) AS count_recursive_child_items FROM tree_ct t LEFT JOIN tree_ct t1 ON t1.path[1:array_upper(t.path, 1)] = t.path GROUP BY t.id, t.item_ct ORDER BY t.id;
id count_direct_child_items count_recursive_child_items
1 1 7
2 1 6
3 0 3
4 2 2
5 0 0
6 0 0
7 3 3
 hidden batch(es)