PolarDB:WITH Queries (Common Table Expressions)

How recursive queries are evaluated

1. Evaluate the non-recursive term. For UNION (but not UNION ALL), discard duplicate rows. Place all remaining rows in the query result and in a temporary working table.

2. While the working table is not empty, repeat these steps:

   1. Evaluate the recursive term, substituting the current contents of the working table for the recursive self-reference. For UNION (but not UNION ALL), discard duplicate rows and rows that duplicate any previous result row. Place all remaining rows in the query result and in a temporary intermediate table.

   2. Replace the working table contents with the intermediate table contents, then empty the intermediate table.

In the sum example above, the working table holds a single row at each step, taking values from 1 through 100. At step 100, the WHERE clause produces no output, so the query terminates.

Traversal order

The recursive query algorithm generates output in width-first search order. To display results in depth-first search order, have the outer query use ORDER BY on a path column constructed during traversal.

Cycle detection

Recursive queries over graph-structured or hierarchical data can loop indefinitely if the data contains cycles. Using UNION instead of UNION ALL eliminates fully duplicate rows, but a cycle may not produce identical output rows — it may only repeat a subset of fields. The standard approach is to track visited values in an array.

The following query searches a graph table via a link field. Without cycle detection it loops if the link relationships form a cycle:

WITH RECURSIVE search_graph(id, link, data, depth) AS (
    SELECT g.id, g.link, g.data, 1
    FROM graph g
  UNION ALL
    SELECT g.id, g.link, g.data, sg.depth + 1
    FROM graph g, search_graph sg
    WHERE g.id = sg.link
)
SELECT * FROM search_graph;

Add path and cycle columns to detect and stop at cycles:

WITH RECURSIVE search_graph(id, link, data, depth, path, cycle) AS (
    SELECT g.id, g.link, g.data, 1,
      ARRAY[g.id],
      false
    FROM graph g
  UNION ALL
    SELECT g.id, g.link, g.data, sg.depth + 1,
      path || g.id,
      g.id = ANY(path)
    FROM graph g, search_graph sg
    WHERE g.id = sg.link AND NOT cycle
)
SELECT * FROM search_graph;

When multiple fields together identify a cycle, use an array of rows. For example, to check both f1 and f2:

WITH RECURSIVE search_graph(id, link, data, depth, path, cycle) AS (
    SELECT g.id, g.link, g.data, 1,
      ARRAY[ROW(g.f1, g.f2)],
      false
    FROM graph g
  UNION ALL
    SELECT g.id, g.link, g.data, sg.depth + 1,
      path || ROW(g.f1, g.f2),
      ROW(g.f1, g.f2) = ANY(path)
    FROM graph g, search_graph sg
    WHERE g.id = sg.link AND NOT cycle
)
SELECT * FROM search_graph;

Beyond cycle prevention, the path array is independently useful: it records the exact path taken to reach any particular row.

Hierarchical data example

A common use of recursive queries is finding all direct and indirect sub-parts of a product from a table of immediate parent-child relationships:

WITH RECURSIVE included_parts(sub_part, part, quantity) AS (
    SELECT sub_part, part, quantity FROM parts WHERE part = 'our_product'
  UNION ALL
    SELECT p.sub_part, p.part, p.quantity
    FROM included_parts pr, parts p
    WHERE p.part = pr.sub_part
)
SELECT sub_part, SUM(quantity) AS total_quantity
FROM included_parts
GROUP BY sub_part;

Testing potentially infinite queries

To test a recursive query that might loop, add a LIMIT to the parent query:

WITH RECURSIVE t(n) AS (
    SELECT 1
  UNION ALL
    SELECT n+1 FROM t
)
SELECT n FROM t LIMIT 100;

This works because PostgreSQL's implementation evaluates only as many rows of a WITH query as are actually fetched by the parent query, so LIMIT can stop runaway recursion during development. Do not rely on this in production — other systems may behave differently, and this approach does not work when the outer query sorts the recursive query's results or joins them to another table, because in those cases the outer query fetches all rows anyway.

Default behavior

A non-recursive, side-effect-free CTE (a SELECT with no volatile functions) can be folded into the parent query, enabling joint optimization of both query levels.

• Folded (merged) when the parent query references the CTE exactly once.

• Materialized (a temporary copy is created) when the parent query references the CTE more than once.

Folded example — equivalent to querying big_table directly with the index on key:

WITH w AS (
    SELECT * FROM big_table
)
SELECT * FROM w WHERE key = 123;

Materialized example — the CTE is evaluated in full, then joined with itself without index benefit:

WITH w AS (
    SELECT * FROM big_table
)
SELECT * FROM w AS w1 JOIN w AS w2 ON w1.key = w2.ref
WHERE w2.key = 123;

Overriding the default with MATERIALIZED and NOT MATERIALIZED

Use NOT MATERIALIZED to allow the parent query's restrictions to apply directly to scans of the underlying table:

WITH w AS NOT MATERIALIZED (
    SELECT * FROM big_table
)
SELECT * FROM w AS w1 JOIN w AS w2 ON w1.key = w2.ref
WHERE w2.key = 123;

Use MATERIALIZED when the CTE contains an expensive function that should run only once per row:

WITH w AS (
    SELECT key, very_expensive_function(val) AS f FROM some_table
)
SELECT * FROM w AS w1 JOIN w AS w2 ON w1.f = w2.f;

Here, materialization ensures very_expensive_function is called once per table row, not twice.

RETURNING and temporary tables

The output of RETURNING — not the target table — forms the temporary table that the rest of the query can reference. If a data-modifying statement in WITH has no RETURNING clause, it forms no temporary table and cannot be referenced elsewhere. It still executes, however:

WITH t AS (
    DELETE FROM foo
)
DELETE FROM bar;

This removes all rows from both foo and bar. The row count reported to the client includes only rows removed from bar.

Using recursive CTEs with data-modifying statements

Recursive self-references inside data-modifying statements are not allowed. As a workaround, reference the output of a recursive WITH instead:

WITH RECURSIVE included_parts(sub_part, part) AS (
    SELECT sub_part, part FROM parts WHERE part = 'our_product'
  UNION ALL
    SELECT p.sub_part, p.part
    FROM included_parts pr, parts p
    WHERE p.part = pr.sub_part
)
DELETE FROM parts
WHERE part IN (SELECT part FROM included_parts);

This removes all direct and indirect sub-parts of a product.

Execution semantics

Data-modifying statements in WITH differ from SELECT in one important way: they always execute exactly once and run to completion, regardless of whether the primary query reads any of their output. By contrast, a SELECT in WITH runs only as far as the parent query demands.

All sub-statements in WITH execute concurrently with each other and with the main query using the same snapshot. They cannot see each other's effects on the target tables. RETURNING is the only mechanism for passing data between sub-statements and the main query.

This snapshot behavior determines what the outer SELECT sees:

-- Outer SELECT sees the original prices (before the UPDATE)
WITH t AS (
    UPDATE products SET price = price * 1.05
    RETURNING *
)
SELECT * FROM products;

-- Outer SELECT sees the updated data (from the RETURNING clause)
WITH t AS (
    UPDATE products SET price = price * 1.05
    RETURNING *
)
SELECT * FROM t;