Any way to nest WITH..AS?

Question

I'm trying to do something like:

WITH megalodon_view AS (
     -- 200 lines of gibberish
)
WITH RECURSIVE traverse_table AS (
     -- big queries with multiple uses of megalodon_view for recursive traversing
)

Observe it's not a mather of defining 2 CTEs, but to use on within the scope of the other.

I'd like to use it in production, so I want preferrably not to create anything physically

The idea is the first CTE can be used multiple times within my recursive one, without having to define it in various sections — Whimusical, Jan 15 '18 at 16:49
Sure. That's a valid use case for using multiple CTEs in the same statement. I've got one in the wild that has multiple levels of CTEs. Two of them at different levels are recursive even. [Check this question, which is similar, but may be more to point](https://stackoverflow.com/questions/27204824/sql-use-a-reference-of-a-cte-to-another-cte) — JNevill, Jan 15 '18 at 16:54

score 1 · Accepted Answer · answered Jan 15 '18 at 16:45

You can specify this as:

WITH recursive megalodon_view AS (
     -- 200 lines of gibberish
     ), 
     traverse_table AS (
     -- big queries with multiple uses of megalodon_view for recursive traversing
     )
select . . .;

The with recursive is needed only once, for the first CTE (even though that one is not recursive).

score 0 · Answer 2 · answered Jan 15 '18 at 16:41

0

Without exact query it is hard to say what are you trying to achieve. Anyway you could nest WITH like:

WITH RECURSIVE t(n) AS (
     SELECT *
     FROM (WITH cte AS (SELECT 1 )
           SELECT * FROM cte
           ) sub
  UNION ALL
  SELECT n+1 FROM t WHERE n < 100
)
SELECT COUNT(*) FROM t;

DBFiddle Demo

answered Jan 15 '18 at 16:41

Lukasz Szozda

162,964
23
234
275

I would like avoiding doing the superdefinition twice – Whimusical Jan 15 '18 at 16:45

Any way to nest WITH..AS?

2 Answers2