Estritamente falando, este processo é iteração (iteration), não recursão (recursion), mas RECURSIVE é a terminologia escolhida pelo comitê de padrões SQL.

A estrutura geral da consulta recursiva Postgres contém:

1. Declaração select não recursiva
2. Union ou union all
3. Declaração 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;

Como funciona uma consulta recursiva Postgres

1. Avalia declarações não recursivas e cria uma tabela temporária
2. Avalia termos recursivos e os adiciona à tabela temporária
3. Repete o passo 2 até que a tabela de trabalho esteja vazia.

A diferença entre union e union all é que union all permite duplicatas enquanto union não permitirá nenhuma duplicata.

Exemplo

Primeiros 10 números naturais

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

Este é o exemplo básico de consulta recursiva Postgres que imprime os primeiros 10 números naturais.

Consulta recursiva Postgres para encontrar o fatorial de um 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 gera duas tabelas, uma com os primeiros cinco números naturais e a outra com os cálculos realizados para encontrar o fatorial.

Podemos imprimir apenas a última linha, mas aqui podemos ver como a iteração e o cálculo ocorrem.

Consulta recursiva Postgres para imprimir a série 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;

Isso imprime a série Fibonacci até 10.

Hierarquia organizacional

Com a ajuda da consulta recursiva Postgres, podemos encontrar a hierarquia organizacional:

Para criar uma tabela:

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 é o chefe, ele estará no primeiro nível. Bhargav, Chay, Dravid, Erin estão no próximo nível e o resto deles estará no último nível.

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;

A saída será:

Reference

• PostgreSQL - Recursive Query Using CTEs - GeeksforGeeks