Introduction
The very basics
Infinite looping
Processing trees with recursive CTE
Processing graphs with recursive CTE
Instruction
Let's move on to more interesting exercise!
Exercise
Your task is to show the following columns:
id– ID of each department,name– name of the department,part_of– ID of the department one level up,path– defined as all names from the root department until the given department, separated with slashes ('/').
Remember the root department has a NULL part_of id.
Stuck? Here's a hint!
The query we created for employees might help you here:
WITH RECURSIVE hierarchy AS (
SELECT
id,
first_name,
last_name,
superior_id,
CAST(last_name AS text) AS path
FROM employee
WHERE superior_id IS NULL
UNION ALL
SELECT
employee.id,
employee.first_name,
employee.last_name,
employee.superior_id,
hierarchy.path || '->' || employee.last_name
FROM employee, hierarchy
WHERE employee.superior_id = hierarchy.id
)
SELECT
*
FROM hierarchy;
