À proprement parler, ce processus est une itération (iteration) et non une récursivité (recursion), mais RECURSIVE est la terminologie choisie par le comité de normalisation SQL.

La structure générale de la requête récursive Postgres contient :

1. Instruction select non récursive
2. Union ou union all
3. Instruction select récursive

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;

Comment fonctionne une requête récursive Postgres

1. Évalue les instructions non récursives et crée une table temporaire
2. Évalue les termes récursifs et les ajoute à la table temporaire
3. Répète l’étape 2 jusqu’à ce que la table de travail soit vide.

La différence entre union et union all est que union all autorise les doublons alors que union n’autorise aucun doublon.

Exemple

Les 10 premiers nombres naturels

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

Ceci est l’exemple de base d’une requête récursive Postgres qui imprime les 10 premiers nombres naturels.

Requête récursive Postgres pour trouver la factorielle d’un nombre naturel :

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;

Cette requête génère deux tables, l’une avec les cinq premiers nombres naturels et l’autre table avec les calculs effectués pour trouver la factorielle.

Nous pouvons imprimer uniquement la dernière ligne, mais ici nous pouvons voir comment l’itération et le calcul ont lieu.

Requête récursive Postgres pour imprimer la 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;

Cela imprime la série de Fibonacci jusqu’à 10.

Hiérarchie organisationnelle

À l’aide de la requête récursive Postgres, nous pouvons trouver la hiérarchie organisationnelle :

Pour créer une table :

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 est le patron, il sera au premier niveau. Bhargav, Chay, Dravid, Erin sont au niveau suivant et les autres seront au dernier niveau.

Requête :

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 sortie sera :

Reference

• PostgreSQL - Recursive Query Using CTEs - GeeksforGeeks