3 ms·
Do recursive CTEs count as SQL, then? Despite the name, they are practically loops.
by kqr 7y ago
Do recursive CTEs count as SQL, then? Despite the name, they are practically loops.
- kragen 7y agoI wrote this query for Dercuano the other day; it computes the Collatz sequence starting from each of the first 100 integers, running a second Collatz computation twice as fast in parallel to detect any cycles (and of course there are none): with recursive collatz as (select 1 as x, 1 as n, 1 as m, 0 as p, 0=1 as exit union select case when nextx then x + 1 else x end as x, case when nextx then x + 1 else (case n % 2 when 1 then 3*n + 1 else n / 2 end) end as n, case when nextx then x + 1 else (case newm % 2 when 1 then 3*newm + 1 else newm / 2 end) end as m, case when nextx then 0 else p+1 end as p, case when nextx then 0 = 1 else n = newm end as exit from (select (m = 1 or newm = 1) as nextx, x, n, m, p, newm from (select x, n, m, p, case m % 2 when 1 then 3*m + 1 else m / 2 end as newm from collatz where not exit) a) b where x < 100) select * from collatz; It works in Postgres 9.5.14; I'm interested to hear if it works or doesn't in other SQL implementations. I think this definitely qualifies as a loop.