Re: Learning about WITH RECURSIVE

Richard Broersma <richard(dot)broersma(at)gmail(dot)com> writes:
> Actually I'm still confused. I must me missing something. When I
> manually following the directions of:
> http://www.postgresql.org/docs/8.4/interactive/queries-with.html

> I get the following when I try:

> WITH RECURSIVE t(n) AS (
> VALUES (1)
> UNION ALL
> SELECT n+1 FROM t WHERE n < 100
> )
> SELECT sum(n) FROM t;

> (1) --initial non-recursive working table

> (1) UA (2) = (1,2) --new(1) working table

> (1,2) UA (2,3) = (1,2,2,3) --new(2) working table

> (1,2,2,3) UA (2,3,3,4) = (1,2,2,2,3,3,3,4) --new(3) working table

You're confusing the working table with the final output. In this
test case, the working table contains exactly one row after each
step (except after the last, when it contains no rows). That one
row is also added to the result, but we don't use the whole result
for the next iteration of the recursive term.

regards, tom lane