Received: from maia.hub.org (unknown [200.46.204.183]) by mail.postgresql.org (Postfix) with ESMTP id 4FBB6632EF7 for ; Wed, 4 Nov 2009 20:04:11 -0400 (AST) Received: from mail.postgresql.org ([200.46.204.86]) by maia.hub.org (mx1.hub.org [200.46.204.183]) (amavisd-maia, port 10024) with ESMTP id 32338-09 for ; Thu, 5 Nov 2009 00:04:03 +0000 (UTC) X-Greylist: from auto-whitelisted by SQLgrey-1.7.6 Received: from sss.pgh.pa.us (sss.pgh.pa.us [66.207.139.130]) by mail.postgresql.org (Postfix) with ESMTP id 994156325E5 for ; Wed, 4 Nov 2009 20:04:03 -0400 (AST) Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1]) by sss.pgh.pa.us (8.14.2/8.14.2) with ESMTP id nA5041hH000145; Wed, 4 Nov 2009 19:04:01 -0500 (EST) To: Richard Broersma cc: sql pgsql Subject: Re: Learning about WITH RECURSIVE In-reply-to: <396486430911041427q3a0c9518v9c999a34290798ed@mail.gmail.com> References: <396486430911041403u639c750bn488ada60822448c0@mail.gmail.com> <27333.1257372664@sss.pgh.pa.us> <396486430911041427q3a0c9518v9c999a34290798ed@mail.gmail.com> Comments: In-reply-to Richard Broersma message dated "Wed, 04 Nov 2009 14:27:14 -0800" Date: Wed, 04 Nov 2009 19:04:01 -0500 Message-ID: <144.1257379441@sss.pgh.pa.us> From: Tom Lane X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=-2.599 tagged_above=-10 required=5 tests=BAYES_00=-2.599 X-Spam-Level: X-Archive-Number: 200911/24 X-Sequence-Number: 33375 Richard Broersma 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