Sebenarnya, proses ini adalah iterasi (iteration), bukan rekursi (recursion), tetapi RECURSIVE adalah terminologi yang dipilih oleh komite standar SQL.

Struktur umum dari kueri rekursif Postgres meliputi:

1. Pernyataan select non-rekursif
2. Union atau union all
3. Pernyataan select rekursif

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;

Bagaimana cara kerja kueri rekursif Postgres

1. Mengevaluasi pernyataan non-rekursif dan membuat tabel sementara
2. Mengevaluasi istilah rekursif dan menambahkannya ke tabel sementara
3. Ulangi langkah 2 sampai tabel kerja kosong.

Perbedaan antara union dan union all adalah bahwa union all mengizinkan duplikat sedangkan union tidak akan mengizinkan duplikat sama sekali.

Contoh

10 bilangan asli pertama

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

Ini adalah contoh dasar dari kueri rekursif Postgres yang mencetak 10 bilangan asli pertama.

Kueri rekursif Postgres untuk mencari faktorial dari bilangan asli:

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;

Kueri ini menghasilkan dua tabel, satu dengan lima bilangan asli pertama dan tabel lainnya dengan perhitungan yang dilakukan untuk mencari faktorial.

Kita dapat mencetak baris terakhir saja tetapi di sini kita dapat melihat bagaimana iterasi dan perhitungan terjadi.

Kueri rekursif Postgres untuk mencetak deret 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;

Ini mencetak deret Fibonacci hingga 10.

Hirarki organisasi

Dengan bantuan kueri rekursif Postgres, kita dapat menemukan hirarki organisasi:

Untuk membuat tabel:

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 adalah bos, dia akan berada di tingkat pertama. Bhargav, Chay, Dravid, Erin berada di tingkat berikutnya dan sisanya akan berada di tingkat terakhir.

Kueri:

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;

Outputnya akan menjadi:

Reference

• PostgreSQL - Recursive Query Using CTEs - GeeksforGeeks