examples · sql · 13 / 13 · works today

13. WITH RECURSIVE

works today

Recursive CTEs (WITH RECURSIVE ...) execute over /sql. There is no prefix gate on WITH - a statement that starts with it routes through the same path a SELECT takes, and plain non-recursive CTEs run there too. The recursive form is evaluated to a fixed point and returns real rows. Bounds worth knowing before you use it are below, and for wide hierarchy walks the graph endpoints are still the better tool.

the classic case

Walk an org chart down from one root. This runs as written:

WITH RECURSIVE descendants AS (
  SELECT id, manager_id FROM hr.employees WHERE id = 'ceo'
  UNION ALL
  SELECT e.id, e.manager_id
    FROM hr.employees e
    INNER JOIN descendants d ON e.manager_id = d.id
)
SELECT * FROM descendants

The response carries the whole transitive closure from the seed row, and nothing outside it. Access control covers the real tables named in both arms, so a seed or step term you cannot read is refused rather than silently skipped.

the bounds

UNION ALL between the seed and step arms. UNION (distinct) is refused - deduplicating would mean hashing every intermediate row.
Exactly one CTE in a statement that uses RECURSIVE; chaining a second binding off the recursive one is refused.
No per-CTE column list (WITH RECURSIVE t(a, b) AS ...).
No aggregate over the recursive relation in the outer projection - SELECT COUNT(*) FROM t is refused, so count client-side. (Over a plain CTE, COUNT(*) does fold - the asymmetry is deliberate.)
Two caps bound the walk: a depth cap of 100 iterations, and a separate cap of 1,000,000 accumulated rows. Cyclic or wide-branching data hits one of them and comes back as a 400 naming the cap it hit - it will not spin or exhaust the engine. Add a visited-set predicate to make such a walk terminate with rows instead.
Filtering and projecting the recursive relation in the outer query works: SELECT id FROM descendants WHERE id <> 'ceo'.

the alternative - graph BFS

For a walk that branches widely, or one you want to run repeatedly, declare the parent column as a relation on the schema and walk the edge with depth-bounded BFS instead. This is not a workaround for a missing feature - it is the traversal-shaped tool for a traversal-shaped job.

# A hierarchy walk via the graph endpoint instead of WITH RECURSIVE.
# Assumes the schema declares manager_id as a [[relations]] block:
#
#   [[relations]]
#   name          = "reports_to"
#   from_col      = "manager_id"
#   bidirectional = true
#
#   [relations.target]
#   namespace = "hr"
#   table     = "employees"
#   pk        = "id"

curl "https://$OC_HOST/v1/tenants/$OC_TENANT/graph/hr.employees/bfs?rel=reports_to&pk=ceo&max_depth=10" \
  -H "Authorization: Bearer $OC_TOKEN"

See Graph reference for BFS, shortest path, k-shortest, and the other 16 graph algorithms. Schema reference → relations covers how to declare the relation.

when to prefer graph

BFS takes max_depth as an explicit per-request argument. WITH RECURSIVE is capped too, but by fixed engine limits you tune the query around rather than pass in.
Forward + reverse traversal are both indexed (bidirectional relations) - "who reports to X" and "who does Y report to" are equally fast.
For weighted paths or shortest-path, you can swap BFS for Dijkstra or k-shortest without rewriting the schema.