Restrictions on recursive common table expressions
The following restrictions apply to recursive common table expressions.
- References to other recursive common table expressions cannot appear within the definition of recursive common table expressions.
Thus, recursive common table expressions cannot be mutually recursive. However, non-recursive common table expressions can
contain references to recursive ones, and recursive common table expressions can contain references to non-recursive ones.
- The only set operator permitted between the initial subquery and the recursive subquery is UNION ALL. No other set operators
are permitted.
- Within the definition of a recursive subquery, a self-reference to the recursive table expression can appear only within the
FROM clause of the recursive subquery.
- When a self-reference appears within the FROM clause of the recursive subquery, the reference to the recursive table cannot
appear on the null-supplying side of an outer join.
- The recursive subquery cannot contain DISTINCT, or a GROUP BY or an ORDER BY clause.
- The recursive subquery can not make use of any aggregate function.
- To prevent runaway recursive queries, an error is generated if the number of levels of recursion exceeds the current setting
of the max_recursive_iterations option. The default value of this option is 100.
