This is tested with PostgreSQL 9.6, but it's a general SQL question.
Is it possible to use self-joins of the recursive table in a recursive CTE (rCTE)?
I tried the following rCTE containing a self-join of the recursive term
WITH RECURSIVE x (id) AS ( SELECT 1 id UNION ALL SELECT x1.id+x2.id FROM x x1, x x2 WHERE x1.id < 5 AND x2.id < 5 AND x1.id = x2.id ) SELECT * FROM x;
, which hopefully should be equivalent to:
WITH RECURSIVE x (id) AS ( SELECT 1 id UNION ALL SELECT id+id FROM x WHERE id < 5 ) SELECT * FROM x;
But the former rCTE generates an error:
ERROR: recursive reference to query "x" must not appear more than once LINE 3: SELECT 1 id UNION ALL SELECT x1.id+x2.id FROM x x1, x x2
Is there a fundamental reason why the recursive reference must not appear more than once? Or is this just a limitation of the PostgreSQL implementation?
Also, is there a work-around?