On Sep19, 2011, at 17:59 , David Fetter wrote:
> On Mon, Sep 19, 2011 at 05:12:15PM +0200, Florian Pflug wrote:
>> My first try, BTW, was
>>
>> WITH RECURSIVE infinite(value) AS (SELECT 1 UNION ALL SELECT 1)
>> SELECT * FROM infinite
>>
>> but that returns only two rows. I'd have expected it to returns an infinite
>> stream of 1s as well, since the iteration part of the recursive CTE never
>> returns zero rows. The behaviour I get is what I'd have expected if I had
>> written "UNION" instead of "UNION ALL". Am I missing something, or is that
>> a genuine bug?
>
> That's actually the correct behavior. In order to get a recursion (or
> iteration, whichever way you want to look at it), you need to refer to
> the CTE on the right side of the UNION [ALL] (or the INTERSECT [ALL]
> per the SQL standard).
Interesting. Thanks for the explanation!
best regards,
Florian Pflug