0

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).

1
  • 1
    I have managed to solve the problem just by trying, but I have posted it here because it seems not to be well documented anywhere in the internet. I hope those who will make the same mistake later will not be so confused as I was!
    – Pavel V.
    Aug 7 '14 at 9:57
1

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.