Recursive CTE for Tree Queries

-- Table: categories (id, name, parent_id)
-- Get full category tree from a root node
WITH RECURSIVE category_tree AS (
  -- Base case: start from root
  SELECT id, name, parent_id, 0 AS depth, name::text AS path
  FROM categories
  WHERE parent_id IS NULL

  UNION ALL

  -- Recursive step: join children
  SELECT c.id, c.name, c.parent_id, ct.depth + 1,
         ct.path || ' > ' || c.name
  FROM categories c
  INNER JOIN category_tree ct ON c.parent_id = ct.id
  WHERE ct.depth < 10  -- safety limit
)
SELECT id, name, depth, path
FROM category_tree
ORDER BY path;

-- Get all ancestors of a specific node
WITH RECURSIVE ancestors AS (
  SELECT id, name, parent_id
  FROM categories
  WHERE id = 42  -- target node

  UNION ALL

  SELECT c.id, c.name, c.parent_id
  FROM categories c
  INNER JOIN ancestors a ON a.parent_id = c.id
)
SELECT * FROM ancestors;