Estrictamente hablando, este proceso es iteración (iteration), no recursión (recursion), pero RECURSIVE es la terminología elegida por el comité de estándares SQL.

La estructura general de la consulta recursiva de Postgres contiene:

1. Declaración select no recursiva
2. Union o union all
3. Declaración select recursiva

WITH RECURSIVE name_cte AS (
  SELECT statement  /* non-recursive statement */
  UNION [ALL]
  SELECT statement  /*recursive statement referencing the above select statement */
)
SELECT * FROM name_cte;

Cómo funciona una consulta recursiva de Postgres

1. Evalúa las declaraciones no recursivas y crea una tabla temporal
2. Evalúa los términos recursivos y los agrega a la tabla temporal
3. Repite el paso 2 hasta que la tabla de trabajo esté vacía.

La diferencia entre union y union all es que union all permite duplicados mientras que union no permitirá ningún duplicado.

Ejemplo

Primeros 10 números naturales

WITH RECURSIVE tens AS (
   SELECT 1 as n
 UNION ALL
   SELECT n+1 FROM tens
)
SELECT n FROM tens limit 10;

Este es el ejemplo básico de consulta recursiva de Postgres que imprime los primeros 10 números naturales.

Consulta recursiva de Postgres para encontrar el factorial de un número natural:

WITH RECURSIVE fact (n, factorial)
AS (
    SELECT 1 as n, 5 as factorial
union all
    SELECT n+1, factorial*n FROM fact where n < 5
)
SELECT * FROM fact;

Esta consulta genera dos tablas, una con los primeros cinco números naturales y la otra tabla con los cálculos que se realizan para encontrar el factorial.

Podemos imprimir solo la última fila, pero aquí podemos ver cómo se llevan a cabo la iteración y el cálculo.

Consulta recursiva de Postgres para imprimir la serie de Fibonacci:

WITH RECURSIVE fibb
AS (
    SELECT 1::bigint as n, 0::bigint as a, 1::bigint as b
UNION ALL
    SELECT n+1, b as a, (a+b) as b FROM fibb
)
SELECT b FROM fibb limit 10;

Esto imprime la serie de Fibonacci hasta 10.

Jerarquía organizacional

Con la ayuda de la consulta recursiva de Postgres, podemos encontrar la jerarquía organizacional:

Para crear una tabla:

INSERT INTO employees (
  employee_id,
  full_name,
  manager_id
)
VALUES
(1, 'Abhi', NULL),
(2, 'Bhargav', 1),
(3, 'Chay', 1),
(4, 'Dravid', 1),
(5, 'Erin', 1),
(6, 'Ford', 2),
(7, 'Gagan', 2),
(8, 'Harry', 3),
(9, 'Isaac', 3),
(10, 'Jack', 4),
(11, 'Kiran', 5);

Abhi es el jefe, estará en el primer nivel. Bhargav, Chay, Dravid, Erin están en el siguiente nivel y el resto de ellos estará en el último nivel.

Consulta:

WITH RECURSIVE subordinates AS (
  SELECT employee_id, manager_id, full_name, 0 as level
  FROM employees
  WHERE manager_id IS NULL

  UNION ALL

  SELECT e.employee_id, e.manager_id, e.full_name, level+1
  FROM employees e
  INNER JOIN subordinates s ON s.employee_id = e.manager_id
)  
SELECT * FROM subordinates;

La salida será:

Reference

• PostgreSQL - Recursive Query Using CTEs - GeeksforGeeks