พูดตามตรง กระบวนการนี้คือการทำซ้ำ (iteration) ไม่ใช่การเรียกซ้ำ (recursion) แต่ RECURSIVE คือคำศัพท์ที่คณะกรรมการมาตรฐาน SQL เลือกใช้

โครงสร้างทั่วไปของคิวรีแบบเรียกซ้ำของ Postgres ประกอบด้วย

1. คำสั่ง select แบบไม่เรียกซ้ำ
2. Union หรือ union all
3. คำสั่ง select แบบเรียกซ้ำ

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;

คิวรีแบบเรียกซ้ำของ Postgres ทำงานอย่างไร

1. ประเมินคำสั่งแบบไม่เรียกซ้ำและสร้างตารางชั่วคราว
2. ประเมินพจน์แบบเรียกซ้ำและเพิ่มลงในตารางชั่วคราว
3. ทำซ้ำขั้นตอนที่ 2 จนกว่าตารางทำงานจะว่างเปล่า

ความแตกต่างระหว่าง union และ union all คือ union all อนุญาตให้มีข้อมูลซ้ำได้ แต่ union จะไม่อนุญาตให้มีข้อมูลซ้ำเลย

ตัวอย่าง

จำนวนธรรมชาติ 10 ตัวแรก

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

นี่คือตัวอย่างพื้นฐานของคิวรีแบบเรียกซ้ำของ Postgres ซึ่งจะพิมพ์จำนวนธรรมชาติ 10 ตัวแรกออกมา

คิวรีแบบเรียกซ้ำของ Postgres เพื่อหาแฟกทอเรียลของจำนวนธรรมชาติ:

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;

คิวรีนี้จะแสดงผลสองตาราง ตารางหนึ่งมีจำนวนธรรมชาติ 5 ตัวแรก และอีกตารางหนึ่งมีการคำนวณที่ทำเพื่อหาแฟกทอเรียล

เราสามารถพิมพ์เฉพาะแถวสุดท้ายได้ แต่ในที่นี้เราสามารถเห็นได้ว่าการทำซ้ำและการคำนวณเกิดขึ้นอย่างไร

คิวรีแบบเรียกซ้ำของ Postgres เพื่อพิมพ์ลำดับฟีโบนัชชี:

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;

สิ่งนี้จะพิมพ์ลำดับฟีโบนัชชีสูงถึง 10

ลำดับชั้นขององค์กร

ด้วยความช่วยเหลือของคิวรีแบบเรียกซ้ำของ Postgres เราสามารถหาลำดับชั้นขององค์กรได้:

ในการสร้างตาราง:

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 เป็นเจ้านาย เขาจะอยู่ในระดับแรก Bhargav, Chay, Dravid, Erin อยู่ในระดับถัดไป และคนอื่นๆ จะอยู่ในระดับสุดท้าย

คิวรี:

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;

ผลลัพธ์จะเป็น:

Reference

• PostgreSQL - Recursive Query Using CTEs - GeeksforGeeks