11

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 x,

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?

mustaccio
  • 28,207
  • 24
  • 60
  • 76
tinlyx
  • 3,810
  • 14
  • 50
  • 79

2 Answers2

14

Indeed error like recursive reference to query "x" must not appear more than once is some strange restriction applied in postgres. And I made assumption it is because their parser just simple distinguish recursive and non-recursive part of query by present of that table. Meantime for that present nice workaround - you may use nested CTE (WITH statement), and give another name for such table. For your initial example it will look like:

WITH RECURSIVE
x (id) AS (
  SELECT 1 id
  UNION ALL
  SELECT * FROM (
    WITH x_inner AS ( -- Workaround of error: recursive reference to query "x" must not appear more than once
      SELECT * FROM x
    )
    SELECT x1.id+x2.id
    FROM x_inner x1, x_inner x2
    WHERE x1.id < 5 AND x2.id < 5 AND x1.id = x2.id
  )t
)
SELECT * FROM x;

You could try it in SQL fiddle.

Hubbitus
  • 251
  • 2
  • 6
1

To your first question: by joining x to x you are creating two separate instances of the object. When Postgres tries to recurse it does not know which path (instance) to follow.

Essentially your second query is the work around. Though I assume you have a more complicated situation that this is a simplification of?

indiri
  • 2,211
  • 9
  • 12