Is "WITH RECURSIVE" limited to the first position of CTEs by design?

From: Kirk Wolak <wolakk(at)gmail(dot)com>
To: pgsql-general <pgsql-general(at)lists(dot)postgresql(dot)org>
Subject: Is "WITH RECURSIVE" limited to the first position of CTEs by design?
Date: 2023-11-13 16:17:00
Message-ID: CACLU5mRRGgoM5Lg7WJbgt2n7JsZRaPiN9c9-UUFmuGPPFZvC5g@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

Team,
This was strange. I wanted to set up a CTE that would FEED my recursive
query.
And also feed the results filtering...

These are the most trivial examples. The second one results in a syntax
error. Which implies that:
1) There can only be one recursive CTE at the same level?
2) It must be the FIRST CTE?

Example:
WITH RECURSIVE CTE1 AS (select 2 UNION ALL SELECT 0),
CTE2 AS (select 1 UNION ALL SELECT 0) select 3 ;

-- This does not work:

WITH CTE1 AS (select 2 UNION ALL SELECT 0),
RECURSIVE CTE2 AS (select 1 UNION ALL SELECT 0)
SELECT 3;
--> ERROR: syntax error at or near "CTE2"

Thanks in advance...

Kirk

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Shaik Mohammad Mujeeb 2023-11-13 16:19:33 Re: Issue in compiling postgres on latest macOS 14.1.1
Previous Message Tom Lane 2023-11-13 15:49:00 Re: Issue in compiling postgres on latest macOS 14.1.1