pg.ddx.io  pgsql-general@postgresql.org mailing list archive  
help / color / mirror / Atom feed
Is "WITH RECURSIVE" limited to the first position of CTEs by design?
3+ messages / 3 participants
[nested] [flat]

* Is "WITH RECURSIVE" limited to the first position of CTEs by design?
@ 2023-11-13 16:17 Kirk Wolak <wolakk@gmail.com>
  2023-11-13 16:23 ` Re: Is "WITH RECURSIVE" limited to the first position of CTEs by design? David G. Johnston <david.g.johnston@gmail.com>
  0 siblings, 1 reply; 3+ messages in thread

From: Kirk Wolak @ 2023-11-13 16:17 UTC (permalink / raw)
  To: pgsql-general <pgsql-general@lists.postgresql.org>

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

^ permalink  raw  reply  [nested|flat] 3+ messages in thread

* Re: Is "WITH RECURSIVE" limited to the first position of CTEs by design?
  2023-11-13 16:17 Is "WITH RECURSIVE" limited to the first position of CTEs by design? Kirk Wolak <wolakk@gmail.com>
@ 2023-11-13 16:23 ` David G. Johnston <david.g.johnston@gmail.com>
  2023-11-13 16:49   ` Re: Is "WITH RECURSIVE" limited to the first position of CTEs by design? Tom Lane <tgl@sss.pgh.pa.us>
  0 siblings, 1 reply; 3+ messages in thread

From: David G. Johnston @ 2023-11-13 16:23 UTC (permalink / raw)
  To: Kirk Wolak <wolakk@gmail.com>; +Cc: pgsql-general <pgsql-general@lists.postgresql.org>

On Mon, Nov 13, 2023 at 9:17 AM Kirk Wolak <wolakk@gmail.com> 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.

The documentation explains clearly the implications on adding the keyword
RECURSIVE after the keyword WITH.

https://www.postgresql.org/docs/current/sql-select.html#SQL-WITH

David J.

^ permalink  raw  reply  [nested|flat] 3+ messages in thread

* Re: Is "WITH RECURSIVE" limited to the first position of CTEs by design?
  2023-11-13 16:17 Is "WITH RECURSIVE" limited to the first position of CTEs by design? Kirk Wolak <wolakk@gmail.com>
  2023-11-13 16:23 ` Re: Is "WITH RECURSIVE" limited to the first position of CTEs by design? David G. Johnston <david.g.johnston@gmail.com>
@ 2023-11-13 16:49   ` Tom Lane <tgl@sss.pgh.pa.us>
  0 siblings, 0 replies; 3+ messages in thread

From: Tom Lane @ 2023-11-13 16:49 UTC (permalink / raw)
  To: David G. Johnston <david.g.johnston@gmail.com>; +Cc: Kirk Wolak <wolakk@gmail.com>; pgsql-general <pgsql-general@lists.postgresql.org>

"David G. Johnston" <david.g.johnston@gmail.com> writes:
> On Mon, Nov 13, 2023 at 9:17 AM Kirk Wolak <wolakk@gmail.com> 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





^ permalink  raw  reply  [nested|flat] 3+ messages in thread


end of thread, other threads:[~2023-11-13 16:49 UTC | newest]

Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2023-11-13 16:17 Is "WITH RECURSIVE" limited to the first position of CTEs by design? Kirk Wolak <wolakk@gmail.com>
2023-11-13 16:23 ` David G. Johnston <david.g.johnston@gmail.com>
2023-11-13 16:49   ` Tom Lane <tgl@sss.pgh.pa.us>

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