5 ms·
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
by radiowave 7y ago
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.
- maweki 7y agoThank you for this tip. This sounds horrendous in terms of maintainability but nice to see it's possible.
- radiowave 7y agoI 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 )
- hansvm 7y agoYou can also use an inner cte to copy the original table and use it as often as you like.