pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedCombining several CTEs with a recursive CTE
3+ messages / 2 participants
[nested] [flat]
* Combining several CTEs with a recursive CTE
@ 2011-09-20 09:58 Thomas Kellerer <spam_eater@gmx.net>
0 siblings, 1 reply; 3+ messages in thread
From: Thomas Kellerer @ 2011-09-20 09:58 UTC (permalink / raw)
To: pgsql-sql
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
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Combining several CTEs with a recursive CTE
@ 2011-09-20 14:15 David Johnston <polobo@yahoo.com>
parent: Thomas Kellerer <spam_eater@gmx.net>
0 siblings, 1 reply; 3+ messages in thread
From: David Johnston @ 2011-09-20 14:15 UTC (permalink / raw)
To: Thomas Kellerer <spam_eater@gmx.net>; +Cc: pgsql-sql
On Sep 20, 2011, at 5:58, Thomas Kellerer <spam_eater@gmx.net> wrote:
>
> 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
>
>
Try sticking the recursive keyword after the "with" if any of the following CTEs are recursive.
WITH RECURSIVE
normal1 AS ()
,recursine1 AS ()
,normal2 AS ()
,recursine2 AS ()
SELECT ...
David J.=
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Combining several CTEs with a recursive CTE
@ 2011-09-20 14:54 Thomas Kellerer <spam_eater@gmx.net>
parent: David Johnston <polobo@yahoo.com>
0 siblings, 0 replies; 3+ messages in thread
From: Thomas Kellerer @ 2011-09-20 14:54 UTC (permalink / raw)
To: pgsql-sql
David Johnston, 20.09.2011 16:15:
>> 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
>>
>>
> Try sticking the recursive keyword after the "with" if any of the
> following CTEs are recursive.
>
> WITH RECURSIVE normal 1 AS () ,recursine1 AS () ,normal2 AS ()
> ,recursine2 AS () SELECT ...
>
> David J.
Ah! That does the trick.
Thanks
Thomas
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2011-09-20 14:54 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2011-09-20 09:58 Combining several CTEs with a recursive CTE Thomas Kellerer <spam_eater@gmx.net>
2011-09-20 14:15 ` David Johnston <polobo@yahoo.com>
2011-09-20 14:54 ` Thomas Kellerer <spam_eater@gmx.net>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox