Recursive SQL Queries with PostgreSQL

Re:Recursive SQL Queries with PostgreSQL by radiowave on Monday March 16, 2020 @05:47AM (#22591297)

It's been a few years since I ran into this, but if memory serves you can work around the limitation of only referring to the recursive table once by joining a lateral subquery into the recursive part of the query. Inside the subquery you're then free of this limitation.

[ Parent | on HN ]

Re:Recursive SQL Queries with PostgreSQL by hansvm on Monday March 16, 2020 @12:37PM (#22594194)

You can also use an inner cte to copy the original table and use it as often as you like.

[ Parent | on HN ]

Re:Recursive SQL Queries with PostgreSQL by maweki on Monday March 16, 2020 @05:55AM (#22591329)

Thank you for this tip. This sounds horrendous in terms of maintainability but nice to see it's possible.

[ Parent | on HN ]

Re:Recursive SQL Queries with PostgreSQL by radiowave on Monday March 16, 2020 @06:08AM (#22591378)

I just found it in my notes. I don't think it looks too bad - everything you were going to do in the recursive part, you just do inside the subquery instead.

  WITH RECURSIVE my_cte AS (
    SELECT foo, bar FROM whatever
    UNION ALL
    SELECT (s).*
    FROM my_cte, LATERAL (
      SELECT ... -- refer to my_cte multiple times here
    ) s
  )

[ Parent | on HN ]