recursive CTE
A common table expression that refers to its own name, used to walk trees and paths such as an organisation chart.
In more detail
It has an anchor part that produces the starting rows and a recursive part, joined to it by a union, that adds rows based on the previous step. It stops when a step adds no new rows, so the recursive part must eventually find nothing.
Example
WITH RECURSIVE tree(id, name, depth) AS (... UNION ALL ...)Related terms
- common table expression — A named intermediate result, written in a WITH clause before the main query, that later parts of the query can use like a table.
- UNION ALL — An operator that stacks the rows of two queries with the same columns into one result, keeping duplicates.
Taught in
- Common Table Expressions — PostgreSQL, Indexing & Relational Data Modeling