بشكل صارم، هذه العملية هي تكرار (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;

يخرج هذا الاستعلام جدولين، أحدهما يحتوي على أول خمسة أعداد طبيعية والجدول الآخر يحتوي على الحسابات التي تم إجراؤها لإيجاد المضروب.

يمكننا طباعة الصف الأخير فقط ولكن هنا يمكننا رؤية كيف يتم التكرار والحساب.

استعلام 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