Nói một cách chính xác, quá trình này là lặp lại (iteration) chứ không phải đệ quy (recursion), nhưng RECURSIVE là thuật ngữ được ủy ban tiêu chuẩn SQL lựa chọn.

Cấu trúc chung của truy vấn đệ quy Postgres bao gồm:

1. Câu lệnh select không đệ quy
2. Union hoặc union all
3. Câu lệnh select đệ quy

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;

Truy vấn đệ quy Postgres hoạt động như thế nào

1. Đánh giá các câu lệnh không đệ quy và tạo bảng tạm thời
2. Đánh giá các thuật ngữ đệ quy và thêm chúng vào bảng tạm thời
3. Lặp lại bước 2 cho đến khi bảng làm việc trống rỗng.

Sự khác biệt giữa union và union all là union all cho phép trùng lặp, còn union sẽ không cho phép bất kỳ sự trùng lặp nào.

Ví dụ

10 số tự nhiên đầu tiên

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

Đây là ví dụ cơ bản về truy vấn đệ quy Postgres in ra 10 số tự nhiên đầu tiên.

Truy vấn đệ quy Postgres để tìm giai thừa của một số tự nhiên:

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;

Truy vấn này xuất ra hai bảng, một bảng có năm số tự nhiên đầu tiên và bảng còn lại có các tính toán được thực hiện để tìm giai thừa.

Chúng ta có thể chỉ in hàng cuối cùng nhưng ở đây chúng ta có thể thấy cách lặp lại và tính toán diễn ra.

Truy vấn đệ quy Postgres để in chuỗi 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;

Điều này in ra chuỗi Fibonacci lên đến 10.

Hệ thống cấp bậc tổ chức

Với sự trợ giúp của truy vấn đệ quy Postgres, chúng ta có thể tìm thấy hệ thống cấp bậc tổ chức:

Để tạo bảng:

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 là sếp, anh ấy sẽ ở cấp độ đầu tiên. Bhargav, Chay, Dravid, Erin ở cấp độ tiếp theo và những người còn lại sẽ ở cấp độ cuối cùng.

Truy vấn:

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;

Kết quả đầu ra sẽ là:

Reference

• PostgreSQL - Recursive Query Using CTEs - GeeksforGeeks