تعبير الجدول المشترك (CTE) استعلام فرعي مسمّى في عبارة WITH قبل الجملة الرئيسية: WITH name AS (SELECT ...) SELECT ... FROM name. يقرأ الاستعلام الرئيسي CTE كأنه جدول، فتتحول الاستعلامات الفرعية المتداخلة إلى خطوات تقرؤها من الأعلى إلى الأسفل.
يجمع CTE المسمّى totals طلبات كل عميل، ويحتفظ الاستعلام الرئيسي بالعملاء الذين يتجاوزون 100: cy بـ 300 وana بـ 180. ولا يوجد CTE إلا في الجملة التي ينتمي إليها.
سلسلة من عدة CTE
افصل بين تعبيرات CTE بفواصل، ويستطيع كل منها قراءة ما عُرّف قبله.
يقرأ average الجدول totals، ويقرأ الاستعلام الرئيسي الاثنين. مجاميع العملاء هي 180 و70 و300، ومتوسطها نحو 183، فلا يتجاوزه إلا cy.
التنقل في شجرة باستخدام WITH RECURSIVE
يتيح WITH RECURSIVE لتعبير CTE الإشارة إلى نفسه. وله جزءان يجمعهما UNION ALL:
- استعلام الأساس (anchor)، الذي يعيد صفوف البداية؛
- الاستعلام التعاودي، الذي يربط CTE بالجدول ليجد المستوى التالي.
يشغّل PostgreSQL الجزء التعاودي على الصفوف التي أنتجتها الخطوة السابقة، ويتوقف عندما لا تعيد خطوة أي صف.
تجد كل خطوة الأشخاص التابعين لصفوف الخطوة السابقة. وتصل Mia إلى العمق 3 بالمسار Rosa > Omar > Ivo > Mia. ولعرض كل من هم تحت مدير واحد بدل الشجرة كلها، غيّر WHERE في استعلام الأساس إلى id = 2.
وللصعود إلى الأعلى، من موظف حتى القمة، بدّل شرط الربط إلى s.id = c.manager_id.
إيقاف CTE تعاودي لا نهائي
لا ينتهي CTE التعاودي إلا حين لا تعيد خطوة أي شيء. ومع بيانات تدور في حلقة (A تابع لـ B، وB تابع لـ A) لا ينتهي أبدًا. أضف حدًا للعمق مثل WHERE c.depth < 20، أو استخدم عبارة CYCLE (PostgreSQL 14+) التي تعلّم أول صف مكرر وتتوقف عن متابعته:
WITH RECURSIVE chain AS (
SELECT id, manager_id FROM staff WHERE id = 5
UNION ALL
SELECT s.id, s.manager_id FROM staff s JOIN chain c ON s.id = c.manager_id
) CYCLE id SET is_cycle USING visited
SELECT * FROM chain;
توليد الصفوف باستخدام CTE تعاودي
لا يحتاج الجزء التعاودي إلى جدول. هذا الاستعلام يعدّ من 1 إلى 5 بإعادة تغذية قيمة كل صف:
لسلاسل الأرقام أو التواريخ البسيطة تكون generate_series أقصر.
نقل الصفوف باستخدام CTE يعدّل البيانات
يمكن أن يكون CTE INSERT أو UPDATE أو DELETE مع عبارة RETURNING، وتقرأ الجملة الرئيسية الصفوف المعادة. هذه الجملة تنقل الطلبات القديمة إلى جدول أرشيف في جملة واحدة:
يُحذف الطلبان 1 و3 ويُدرجان في الأرشيف معًا، فإن حدث فشل بقي الجدولان كما كانا. وكل أجزاء الجملة ترى اللقطة نفسها: لا يرى الاستعلام الرئيسي أثر DELETE على orders، بل ما يمرره RETURNING فقط (راجع RETURNING).
التحكم في الدمج باستخدام MATERIALIZED
منذ PostgreSQL 12 يُدمج CTE الخالي من الآثار الجانبية الذي يشير إليه الاستعلام الرئيسي مرة واحدة. يحسّنه المخطِّط مع الاستعلام الخارجي، فيستطيع WHERE المكتوب خارج CTE استخدام فهرس. أما CTE المشار إليه مرتين أو أكثر فيُحسب مرة واحدة وتُعاد نتيجته. ويمكنك فرض أي من السلوكين:
WITH recent AS MATERIALIZED (
SELECT * FROM events WHERE created_at > now() - interval '1 day'
)
SELECT * FROM recent WHERE user_id = 42;
WITH recent AS NOT MATERIALIZED (...)
يحسب MATERIALIZED تعبير CTE مرة واحدة وحده، ويُبقي WHERE الخارجي خارجه. استخدمه حين تتبين أن الخطة المدمجة أبطأ. ويطلب NOT MATERIALIZED الدمج حتى حين يُشار إلى CTE أكثر من مرة. أما تعبيرات CTE التعاودية والمعدِّلة للبيانات فتُحسب دائمًا منفصلة.
الأسئلة الشائعة
هل CTE أسرع من الاستعلام الفرعي في PostgreSQL؟
غالبًا لا أحد منهما أسرع. منذ PostgreSQL 12 يُدمج CTE المشار إليه مرة واحدة والخالي من الآثار الجانبية في الاستعلام الرئيسي، فيحصل على خطة الاستعلام الفرعي المكافئ نفسها. وقبل الإصدار 12 كان كل CTE يُحسب منفصلًا، وهذا قد يمنع استخدام الفهرس.
هل يمكن استخدام CTE في UPDATE أو DELETE؟
نعم. يمكن أن يسبق WITH INSERT وUPDATE وDELETE وMERGE، ويمكن أن يكون CTE نفسه INSERT أو UPDATE أو DELETE مع RETURNING، وتقرأ الجملة الرئيسية صفوفه.
كيف أوقف حلقة لا نهائية في CTE تعاودي؟
أعطِ الجزء التعاودي شرطًا يتوقف في النهاية عن إعادة الصفوف، مثل حد للعمق (WHERE depth < 10). وللبيانات التي قد تعود إلى نفسها، تعلّم عبارة CYCLE (PostgreSQL 14+) الصف المكرر وتتوقف عنده.
هل يمكن استخدام CTE في أكثر من استعلام؟
لا. يوجد CTE فقط في الجملة التي كُتب فيها. ولإعادة استخدام استعلام في عدة جمل أنشئ عرضًا (view) أو جدولًا مؤقتًا.