Common table expression (CTE) הוא תת שאילתה עם שם בפסוקית WITH לפני הפקודה הראשית: WITH name AS (SELECT ...) SELECT ... FROM name. השאילתה הראשית קוראת את ה-CTE כמו טבלה, כך שתת שאילתות מקוננות הופכות לשלבים שקוראים מלמעלה למטה.
ה-CTE totals מסכם את ההזמנות של כל לקוח, והשאילתה הראשית משאירה את הלקוחות מעל 100: cy עם 300 ו-ana עם 180. CTE קיים רק בפקודה שהוא שייך אליה.
לשרשר כמה CTEs
מפרידים CTEs בפסיקים. כל אחד יכול לקרוא את ה-CTEs שהוגדרו לפניו.
average קורא את totals, והשאילתה הראשית קוראת את שניהם. הסכומים של הלקוחות הם 180, 70 ו-300 והממוצע שלהם בערך 183, כך שרק cy מעליו.
לעבור על עץ עם WITH RECURSIVE
WITH RECURSIVE מאפשר ל-CTE להפנות לעצמו. יש לו שני חלקים שמחוברים ב-UNION ALL:
- שאילתת העוגן, שמחזירה את שורות ההתחלה;
- השאילתה הרקורסיבית, שמצרפת את ה-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 בלי תופעות לוואי שהשאילתה הראשית מפנה אליו פעם אחת משולב בה. ה-planner מבצע לו אופטימיזציה יחד עם השאילתה החיצונית, כך ש-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 יותר מפעם אחת. CTEs רקורסיביים ו-CTEs שמשנים נתונים תמיד מחושבים בנפרד.
שאלות נפוצות
האם 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 או טבלה זמנית.