Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1r2a7a-00HVHG-IH for pgsql-general@arkaria.postgresql.org; Mon, 13 Nov 2023 16:49:10 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.94.2) (envelope-from ) id 1r2a7X-00GknV-Dq for pgsql-general@arkaria.postgresql.org; Mon, 13 Nov 2023 16:49:07 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1r2a7X-00GknN-3S for pgsql-general@lists.postgresql.org; Mon, 13 Nov 2023 16:49:07 +0000 Received: from sss.pgh.pa.us ([68.162.161.243]) by makus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1r2a7U-005OPf-H1 for pgsql-general@lists.postgresql.org; Mon, 13 Nov 2023 16:49:05 +0000 Received: from sss1.sss.pgh.pa.us (localhost [127.0.0.1]) by sss.pgh.pa.us (8.15.2/8.15.2) with ESMTP id 3ADGn3x12073792; Mon, 13 Nov 2023 11:49:03 -0500 From: Tom Lane To: "David G. Johnston" cc: Kirk Wolak , pgsql-general Subject: Re: Is "WITH RECURSIVE" limited to the first position of CTEs by design? In-reply-to: References: Comments: In-reply-to "David G. Johnston" message dated "Mon, 13 Nov 2023 09:23:57 -0700" MIME-Version: 1.0 Content-Type: text/plain; charset="UTF-8" Content-ID: <2073790.1699894143.1@sss.pgh.pa.us> Content-Transfer-Encoding: 8bit Date: Mon, 13 Nov 2023 11:49:03 -0500 Message-ID: <2073791.1699894143@sss.pgh.pa.us> List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk "David G. Johnston" writes: > On Mon, Nov 13, 2023 at 9:17 AM Kirk Wolak wrote: >> 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? > There is only one WITH keyword as well, that doesn't change the fact there > can be multiple attached queries to it. Yeah, RECURSIVE is attached to the WITH not to the query. That's because it has global implications for the visibility of the CTE names across all the CTEs in the WITH. regards, tom lane