Recursive CTE

WITH RECURSIVE for graph traversal and hierarchies, plus DECLARE RECURSIVE VIEW for IVM.

A recursive CTE has a base case and a recursive case joined by UNION or UNION ALL. It runs until the working set is empty or the max-iterations guard trips.

Basic shape

WITH RECURSIVE ancestors(id, ancestor, depth) AS (
  -- Base: start at each user
  SELECT id, parent_id, 1 FROM users WHERE parent_id IS NOT NULL
  UNION ALL
  -- Recursive: walk up one more level
  SELECT a.id, u.parent_id, a.depth + 1
  FROM ancestors a
  JOIN users u ON a.ancestor = u.id
  WHERE a.depth < 10    -- max-iterations guard
)
SELECT * FROM ancestors;

Use UNION (deduplicated) for tree-shaped recursion, UNION ALL when duplicate rows are fine (graph traversal).

Iteration guards

Guard	When to use
`WHERE depth < N` in the recursive arm	Tree / bounded hierarchy.
Stop when no new rows are produced	Default — Krishiv detects a fixed point.
Session-level `KRISHIV_RECURSIVE_MAX_ITERATIONS` (default 1000)	Safety net for runaway recursion.

Common patterns

Bill of materials explosion

WITH RECURSIVE bom(part, sub, qty, depth) AS (
  SELECT part, sub, qty, 1 FROM components WHERE part = 'widget'
  UNION ALL
  SELECT bom.part, c.sub, bom.qty * c.qty, bom.depth + 1
  FROM bom JOIN components c ON bom.sub = c.part
  WHERE bom.depth < 20
)
SELECT * FROM bom;

Shortest path in a graph

WITH RECURSIVE paths(src, dst, hops, path) AS (
  SELECT src, dst, 1, ARRAY[src, dst]::text[] FROM edges
  UNION ALL
  SELECT p.src, e.dst, p.hops + 1, p.path || ARRAY[e.dst]
  FROM paths p JOIN edges e ON p.dst = e.src
  WHERE NOT (e.dst = ANY(p.path))  -- avoid cycles
    AND p.hops < 10
)
SELECT * FROM paths;

Recursive incremental views

For graph-shaped views that need to update incrementally as new edges arrive, use DECLARE RECURSIVE VIEW in the IVM DSL:

DECLARE RECURSIVE VIEW ancestor_of AS
  SELECT id, parent_id AS ancestor FROM org_chart
  UNION ALL
  SELECT a.id, oc.parent_id FROM ancestor_of a JOIN org_chart oc ON a.ancestor = oc.id;

Each tick of the IVM job walks one level of recursion. The default max-iterations is 1000 (configurable per view).