PostgreSQL CTE Hierarchy
▌Problem
Assume that we have a table, Departments, which include
hierarchy data like this,
and we would like to know the Agile dev team’s parent
hierarchy, for example,
Agile
dev team / Tiger department / IT
Here is a sample for using CTE(Common table
expression) and RECURSIVE modifier to solve the
problem.
▌Solution
▋Recursive CTE
WITH RECURSIVE cte_recursive AS (
SELECT *
FROM "Departments"
WHERE "Id" = '001'
UNION ALL
SELECT t.*
FROM "Departments" t
INNER JOIN cte_recursive r ON t."Id" = r."Parent"
)
SELECT * FROM cte_recursive;
The recursive CTE results in:
▋Function (Optional)
We can create a function for advanced usage.
CREATE OR REPLACE FUNCTION public.find_hirearchy_departmemts(id text) RETURNS SETOF "Departments" AS
$function$
BEGIN
RETURN QUERY
WITH RECURSIVE cte_recursive AS (
SELECT *
FROM "Departments"
WHERE "Id" = id
UNION ALL
SELECT t.*
FROM "Departments" t
INNER JOIN cte_recursive r ON t."Id" = r."Parent"
)
SELECT * FROM cte_recursive;
END;
$function$ LANGUAGE plpgsql;
▌Reference
▋WITH Queries (Common Table Expressions)
