Recursive CTEs for Hierarchical Data

CREATE TABLE employees (
  id         int PRIMARY KEY,
  name       text NOT NULL,
  manager_id int REFERENCES employees(id)
);

-- Find all reports under employee 5, any depth
WITH RECURSIVE reports AS (
  -- Base case: the root employee
  SELECT id, name, manager_id, 0 AS depth
  FROM employees
  WHERE id = 5

  UNION ALL

  -- Recursive case: direct reports of already-found employees
  SELECT e.id, e.name, e.manager_id, r.depth + 1
  FROM employees e
  JOIN reports r ON r.id = e.manager_id
)
SELECT depth, repeat('  ', depth) || name AS indented_name
FROM reports
ORDER BY depth, name;

-- Build a breadcrumb path and detect cycles with an array of visited IDs
WITH RECURSIVE category_path AS (
  SELECT id, name, parent_id,
         ARRAY[id] AS visited,
         name::text AS path
  FROM categories
  WHERE parent_id IS NULL

  UNION ALL

  SELECT c.id, c.name, c.parent_id,
         cp.visited || c.id,
         cp.path || ' > ' || c.name
  FROM categories c
  JOIN category_path cp ON cp.id = c.parent_id
  WHERE NOT c.id = ANY(cp.visited)  -- cycle guard
)
SELECT path FROM category_path ORDER BY path;

-- Generate a calendar of dates (also doable with generate_series)
WITH RECURSIVE cal AS (
  SELECT '2024-01-01'::date AS d
  UNION ALL
  SELECT d + 1 FROM cal WHERE d < '2024-01-31'
)
SELECT d FROM cal;