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.