Multiple CTE in single query

sql postgresql common-table-expression recursive-query arel
axvm picture axvm · Feb 7, 2016 · Viewed 47k times · Source

Is it possible to combine multiple CTEs in single query with arel? I am looking for way to get result like this:

WITH 'cte1' AS (
...
),
WITH RECURSIVE 'cte2' AS (
...
),
WITH 'cte3' AS (
...
)
SELECT ... FROM 'cte3' WHERE ...

As you can see, I have one recursive CTE and two non recursive.

Answer

Erwin Brandstetter picture Erwin Brandstetter · Feb 7, 2016

Use the key word WITH once at the top. If any of your Common Table Expressions (CTE) are recursive (rCTE) you have to add the keyword RECURSIVE at the top once also, even if not all CTEs are recursive:

WITH RECURSIVE
  cte1 AS (...)         -- can still be non-recursive
, cte2 AS (SELECT ...
           UNION ALL
           SELECT ...)  -- recursive term
, cte3 AS (...)
SELECT ... FROM cte3 WHERE ...

The manual:

If RECURSIVE is specified, it allows a SELECT subquery to reference itself by name.

Bold emphasis mine. And, even more insightful:

Another effect of RECURSIVE is that WITH queries need not be ordered: a query can reference another one that is later in the list. (However, circular references, or mutual recursion, are not implemented.) Without RECURSIVE, WITH queries can only reference sibling WITH queries that are earlier in the WITH list.

Bold emphasis mine again. Meaning that the order of WITH clauses is meaningless when the RECURSIVE key word has been used.

BTW, since cte1 and cte2 in the example are not referenced in the outer SELECT and are plain SELECT commands themselves (no collateral effects), they are never executed (unless referenced in cte3).

Related questions

Recursive query used for transitive closure

I've created a simple example to illustrate transitive closure using recursive queries in PostgreSQL. However, something is off with my recursive query. I'm not familiar with the syntax yet so this request may be entirely noobish of me, and for …

Read More
How to use multiple WITH statements in one PostgreSQL query?

I would like to "declare" what are effectively multiple TEMP tables using the WITH statement. The query I am trying to execute is along the lines of: WITH table_1 AS ( SELECT GENERATE_SERIES('2012-06-29', '2012-07-03…

Read More
INSERT rows into multiple tables in a single query, selecting from an involved table

I have two tables of the following form (i.e., every foo is linked to exactly one bar). CREATE TABLE foo ( id INTEGER PRIMARY KEY, x INTEGER NOT NULL, y INTEGER NOT NULL, ..., bar_id INTEGER UNIQUE NOT NULL, FOREIGN …

Read More