În mie., 18 dec. 2024 la 16:07, Greg Sabino Mullane <htamfids@gmail.com> a scris:
Do you mean something like "... WHERE pg_magic_iteration_number < 10"? Looking at the source code, I don't see a trivial way to accomplish that.
Oh, I disagree, just told you that the iteration number is readily available in the field computed by the SEARCH BREADTH FIRST clause.
Maintaining the count as a column in your select is still the canonical way. As someone who writes a lot of recursive CTEs (especially each December!), I'm not sure how useful this feature would be, as the number of loops is rarely the criteria for ending the iterations.
I never said I use the iteration number to end the process, I need it to pick the right table to be joined. If the iteration number was stored in a working table column, I would be forced to perform a LATERAL join, which recomputes the same joined table again and again for every row in the working table.
Certainly the best solution is to use pl/pgsql, which gets you iterative loops, lots of introspection and ways to break out of the loop, and even true recursion.
Thought about it, of course, but I'm pretty sure that plain JOINs are quicker than linear search loops written in pl/pgsql (remember I need to intersect an dynamic number of arrays) .
