Recursive CTEs in PostgreSQL

-- Organizational hierarchy
WITH RECURSIVE org_chart AS (
  -- Anchor: start with CEO
  SELECT id, name, manager_id, 0 as level
  FROM employees
  WHERE manager_id IS NULL
  
  UNION ALL
  
  -- Recursive: find subordinates
  SELECT e.id, e.name, e.manager_id, oc.level + 1
  FROM employees e
  JOIN org_chart oc ON e.manager_id = oc.id
)
SELECT * FROM org_chart
ORDER BY level, name;

-- DETAILED_TAB:
-- Generate series with recursive CTE
WITH RECURSIVE dates AS (
  SELECT DATE '2024-01-01' as date
  UNION ALL
  SELECT (date + INTERVAL '1 day')::date
  FROM dates
  WHERE date < '2024-01-31'
)
SELECT date FROM dates;

-- Find all related records (graph traversal)
WITH RECURSIVE related AS (
  -- Start node
  SELECT id, parent_id, name, ARRAY[id] as path
  FROM categories
  WHERE id = 5
  
  UNION ALL
  
  -- Find connections
  SELECT c.id, c.parent_id, c.name, r.path || c.id
  FROM categories c
  JOIN related r ON c.parent_id = r.id
  WHERE NOT c.id = ANY(r.path)  -- Avoid cycles
)
SELECT * FROM related;