document_count_categories_func.sql

CREATE OR REPLACE FUNCTION public.document_count_categories()
 RETURNS TABLE(output_ancestor_name text, output_all_document numeric, output_verified_document numeric, output_revision_document numeric)
 LANGUAGE plpgsql
AS $function$
	declare
		parent_category_id UUID;
    begin
		FOR parent_category_id IN
      		SELECT category_id FROM categories where parent_id is null
   		LOOP
      		RETURN QUERY 
      		select *
			from generate_document_count(parent_category_id);
  		END LOOP;
    END
$function$
;

generate_document_count_func.sql.sql

CREATE OR REPLACE FUNCTION public.generate_document_count(input_id uuid)
 RETURNS TABLE(output_ancestor_name text, output_all_document numeric, output_verified_document numeric, output_revision_document numeric, output_viewed_document numeric, output_downloaded_document numeric)
 LANGUAGE plpgsql
AS $function$
    BEGIN
         RETURN QUERY
			with recursive category_tree AS (
    			select category_id, "name", ARRAY[]::UUID[] AS ancestors
		    	from categories WHERE parent_id IS NULL
		    	union all
		    	select categories.category_id, categories."name", category_tree.ancestors || categories.parent_id
    			from categories, category_tree
    			where categories.parent_id = category_tree.category_id
			), document_count_per_sub_category as (
				select 
					(select "name" from categories where category_id = input_id) as ancestor_name,
					"name", 
					ct.category_id as id, 
					count(d.id) as all_document, 
					count(d.id) filter (where d.verification_status_id = 2) as verified_document, 
					count(d.id) filter (where d."version" > 1) as revision_document,
					viewed_document,
					downloaded_document
				FROM category_tree ct
				left join category_document_access cd
				on cd.category_id = ct.category_id
				left join (
					select 
						d_inner.id, 
						d_inner.document_name,
						d_inner.verification_status_id, 
						d_inner."version",
						count (udv.*) as viewed_document,
						count (udd.*) as downloaded_document
					from documents d_inner
					left join user_document_view udv
					on d_inner.id = udv.document_id
					left join user_document_download udd
					on d_inner.id = udd.document_id
					group by d_inner.id, d_inner.verification_status_id, d_inner."version"				
				) d
				on cd.document_id = d.id
				WHERE input_id = any(ct.ancestors)
				group by ct."name", ct.category_id, d.verification_status_id, d.viewed_document, d.downloaded_document
			)
			select 
				ancestor_name, 
				sum(all_document) as all_document,
				sum(verified_document) as verified_document, 
				sum(revision_document) as revision_document,
				sum(viewed_document) as viewed_document,
				sum(downloaded_document) as downloaded_document
			from document_count_per_sub_category
			group by ancestor_name;
    END;
$function$
;