I played with a silly example and got a result that surprises me:
WITH RECURSIVE fib AS (
SELECT n, "fibₙ"
FROM (VALUES (1, 1::bigint), (2, 1)) AS f(n,"fibₙ")
UNION ALL
SELECT max(n) + 1,
sum("fibₙ")::bigint
FROM (SELECT n, "fibₙ"
FROM fib
ORDER BY n DESC
LIMIT 2) AS tail
HAVING max(n) < 10
)
SELECT * FROM fib;
n | fibₙ
----+------
1 | 1
2 | 1
3 | 2
4 | 2
5 | 2
6 | 2
7 | 2
8 | 2
9 | 2
10 | 2
(10 rows)
I would have expected either the Fibonacci sequence or
ERROR: aggregate functions are not allowed in a recursive query's recursive term
Yours,
Laurenz Albe