Genau genommen ist dieser Prozess eine Iteration (iteration), keine Rekursion (recursion), aber RECURSIVE ist die vom SQL-Standardkomitee gewählte Terminologie.

Die allgemeine Struktur der rekursiven Postgres-Abfrage enthält:

1. Nicht-rekursive select-Anweisung
2. Union oder union all
3. Rekursive select-Anweisung

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;

Wie funktioniert eine rekursive Postgres-Abfrage?

1. Wertet nicht-rekursive Anweisungen aus und erstellt eine temporäre Tabelle
2. Wertet rekursive Terme aus und fügt sie der temporären Tabelle hinzu
3. Wiederholt Schritt 2, bis die Arbeitstabelle leer ist.

Der Unterschied zwischen union und union all besteht darin, dass union all Duplikate zulässt, während union keine Duplikate zulässt.

Beispiel

Die ersten 10 natürlichen Zahlen

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

Dies ist das grundlegende Beispiel für eine rekursive Postgres-Abfrage, die die ersten 10 natürlichen Zahlen ausgibt.

Rekursive Postgres-Abfrage zum Finden der Fakultät einer natürlichen Zahl:

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;

Diese Abfrage gibt zwei Tabellen aus, eine mit den ersten fünf natürlichen Zahlen und die andere Tabelle mit den Berechnungen, die durchgeführt werden, um die Fakultät zu finden.

Wir können nur die letzte Zeile drucken, aber hier können wir sehen, wie die Iteration und Berechnung stattfinden.

Rekursive Postgres-Abfrage zum Drucken der Fibonacci-Reihe:

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;

Dies druckt die Fibonacci-Reihe bis 10.

Organisationshierarchie

Mit Hilfe der rekursiven Postgres-Abfrage können wir die Organisationshierarchie finden:

So erstellen Sie eine Tabelle:

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 ist der Chef, er wird auf der ersten Ebene sein. Bhargav, Chay, Dravid, Erin sind auf der nächsten Ebene und der Rest von ihnen wird auf der letzten Ebene sein.

Abfrage:

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;

Die Ausgabe wird sein:

Reference

• PostgreSQL - Recursive Query Using CTEs - GeeksforGeeks