Learning PostgreSQL 11 - Third Edition

Recursive and hierarchical queries

It's possible to refer to the name of a CTE from the code of that CTE itself. These statements are called recursive queries. Recursive queries must have a special structure that tells the database that the subquery is recursive. The structure of a recursive query is as follows:

WITH RECURSIVE <subquery_name> (<field list>) AS(  <non-recursive term>  UNION [ALL|DISTINCT]  <recursive term>)[,...]<main query>

Both non-recursive and recursive terms are subqueries that must return the same number of fields of the same types. The names of the fields are specified in the declaration of the whole recursive query; therefore, it does not matter which names are assigned to the fields in the subqueries.

A non-recursive ...