PostgreSQL ERROR: Recursive Reference to Query ‘najdi_tot’ Explained

ctepostgresql

I have a trouble with the following CTE query:

  WITH RECURSIVE najdi_tot(sj) AS (
    SELECT nad FROM s_vztah WHERE pod=581
    UNION
    SELECT pod FROM s_vztah WHERE nad=581
    UNION
    SELECT sv.nad FROM najdi_tot n, s_vztah sv 
    WHERE sv.pod = n.sj
    UNION
    SELECT sv.pod FROM najdi_tot n, s_vztah sv 
    WHERE sv.nad = n.sj
  )
  SELECT array_agg(sj) FROM najdi_tot

Following error occurs:

ERROR:  recursive reference to query "najdi_tot" must not appear within its non-recursive term
LINE 6:     SELECT sv.nad FROM najdi_tot n, s_vztah sv 
                               ^

I made a google search ( postgresql "recursive reference to query" "must not appear within its non-recursive term" ) and found nothing but maillists where I failed to find the solution (perhaps it is there, but hidden in discussion).

What should my query do is to find all rows linked from a table connecting two rows in the same table (nad and pod).

Best Answer

The problem was caused by multiplication in the recursive calls. When I deleted one of them, the problem was solved:

  WITH RECURSIVE najdi_tot(sj) AS (
    SELECT nad FROM s_vztah WHERE pod=581
    UNION
    SELECT pod FROM s_vztah WHERE nad=581
    UNION
    SELECT sv.nad FROM najdi_tot n, s_vztah sv 
    WHERE sv.pod = n.sj
  )
  SELECT array_agg(sj) FROM najdi_tot

Then I realized that the relation is symmetrical - at the first call I get all the linked rows and recursion only adds the row I had in the beginning. So I removed the recursion at all and everything is OK:

  WITH najdi_tot(sj) AS (
    SELECT nad FROM s_vztah WHERE pod=581
    UNION
    SELECT pod FROM s_vztah WHERE nad=581
  )
  SELECT array_agg(sj) FROM najdi_tot
Related Question