As you know, Postgres currently supports SQL:1999 recursive common table expressions, using WITH RECURSIVE. However, Postgres does not allow more than one recursive self-reference in the recursive term. This restriction seems to be unnecessary.
In this mail, I'd like to propose a patch that removes this restriction, and therefore allows the use of multiple self-references in the recursive term. After the patch:
WITH RECURSIVE t(n) AS ( VALUES(1) UNION ALL SELECT t.n+f.n FROM t, t AS f WHERE t.n < 100 ) SELECT * FROM t;
n ----- 1 2 4 8 16 32 64 128 (8 rows)
This feature deviates only slightly from the current WITH RECURSIVE, and requires very little changes (~10 loc). Any thoughts on this?
-- Denis Hirn
I am not at all sure what the standard says about such recursion but it looks like the two t's are treated in your patch as the same incarnation of the table, not as a cross join of two incarnations. The natural result I would expect from a this query would be all numbers from 1 to 198 (assuming that the query is modified to restrict f.n and that UNION ALL is converted to UNION to avoid infinite recursion).
SET @@standard_compliant_cte=0; WITH RECURSIVE t(n) AS ( SELECT 1 UNION -- ALL SELECT t.n + f.n FROM t, t AS f WHERE t.n < 4 AND f.n < 4 ) SELECT * FROM t;