To: pgsql-sql@postgresql.org
From: Thomas Kellerer <spam_eater@gmx.net>
Subject: Combining several CTEs with a recursive CTE
Date: Tue, 20 Sep 2011 11:58:20 +0200
Lines: 41
Message-ID: <j59o2m$dn1$1@dough.gmane.org>
Mime-Version: 1.0
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 7bit
User-Agent: Mozilla/5.0 (Windows; U; Windows NT 5.1; en-US;
	rv:1.8.1.23) Gecko/20090812 Thunderbird/2.0.0.23 Mnenhy/0.7.6.666

Hello all,

this is more a "just curious" question, rather than a real world problem.

We can combine several CTEs into a single select using something like this:

WITH cte_1 as (
    select ....
),
cte_2 as (
    select ...
    where id (select some_col from cte_1)
)
select *
from cte_2;


But this does not seem to work when a recursive CTE is involved


WITH cte_1 as (
    select ....
),
recursive cte_2 as (
    select ...
    where id (select some_col from cte_1)

    union all

    select ...
)
select *
from cte_2;

This throws an error: syntax error at or near "cte_2"

I'm just wondering if this is intended behavioury, simply not (yet) implemented or even invalid according to the standard? I didn't find any reference that it's not allowed in the manual.

Regards
Thomas