[PostgreSQL] Recursive CTE for hierarchy data

 

 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)

▋PostgreSQL CTE