Hello Jonah,
Nice.
-- No ORDER/LIMIT is required with ITERATIVE as only a single tuple is
present
EXPLAIN ANALYZE
WITH ITERATIVE fib_sum (iteration, previous_number, new_number)
AS (SELECT 1, 0::numeric, 1::numeric
UNION ALL
SELECT (iteration + 1), new_number, (previous_number + new_number)
FROM fib_sum
WHERE iteration <= 10000)
SELECT r.iteration, r.new_number
FROM fib_sum r;
Nice.
My 0,02€ about the feature design:
I'm wondering about how to use such a feature in the context of WITH query
with several queries having different behaviors. Currently "WITH"
introduces a named-query (like a view), "WITH RECURSIVE" introduces a mix
of recursive and named queries, pg really sees whether each one is
recursive or not, and "RECURSIVE" is required but could just be guessed.
Now that there could be 3 variants in the mix, and for the feature to be
orthogonal I think that it should be allowed. However, there is no obvious
way to distinguish a RECURSIVE from an ITERATIVE, as it is more a
behavioral thing than a structural one. This suggests allowing to tag each
query somehow, eg before, which would be closer to the current approach:
WITH
foo(i) AS (simple select),
RECURSIVE bla(i) AS (recursive select),
ITERATIVE blup(i) AS (iterative select),
or maybe after AS, which may be clearer because closer to the actual
query, which looks better to me:
WITH
foo(i) AS (simple select),
bla(i) AS RECURSIVE (recursive select),
boo(i) AS ITERATIVE (iterative select),
…
Also, with 3 cases I would prefer that the default has a name so someone
can talk about it otherwise than saying "default". Maybe SIMPLE would
suffice, or something else. ISTM that as nothing is expected between AS
and the open parenthesis, there is no need to have a reserved keyword,
which is a good thing for the parser.
--
Fabien.
