Recursive CTE Org Chart

Walk a manager → employee hierarchy with WITH RECURSIVE. The lineage view treats the recursive CTE as a self-referencing stage.

The lineage

The SQL

CREATE TABLE employees (
  employee_id INT PRIMARY KEY,
  manager_id INT REFERENCES employees(employee_id),
  full_name TEXT,
  title TEXT
);

CREATE VIEW v_org_chart AS
WITH RECURSIVE org AS (
  SELECT employee_id, manager_id, full_name, title, 0 AS depth,
         full_name AS path
  FROM employees
  WHERE manager_id IS NULL
  UNION ALL
  SELECT e.employee_id, e.manager_id, e.full_name, e.title, o.depth + 1,
         o.path || ' > ' || e.full_name
  FROM employees e
  JOIN org o ON e.manager_id = o.employee_id
)
SELECT employee_id, manager_id, full_name, title, depth, path
FROM org;

What the lineage shows

• v_org_chart.path is built by concatenating employees.full_name across recursive iterations.
• depth is purely derived — it has no direct source column.
• Recursive CTEs are visualized as a stage that consumes itself plus the base table.