Introduction
The very basics
Infinite looping
Processing trees with recursive CTE
Processing graphs with recursive CTE
Summary

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;