CTE، CTE بازگشتی و CTE تغییردهنده‌ی داده

کوئری‌های خوانا و قدرتمند با WITH

CTE (Common Table Expression) یک زیرکوئری نام‌دار است که کوئری طولانی را به گام‌های خوانا می‌شکند:

WITH monthly AS (
  SELECT o.customer_id, sum(oi.line_total_rial) AS revenue
  FROM orders o JOIN order_items oi ON oi.order_id = o.id
  WHERE o.created_at >= timestamptz '2026-09-23 00:00+03:30'
  GROUP BY o.customer_id
), ranked AS (
  SELECT customer_id, revenue, ntile(4) OVER (ORDER BY revenue DESC) AS quartile
  FROM monthly
)
SELECT c.full_name, r.revenue
FROM ranked r JOIN customers c ON c.id = r.customer_id
WHERE r.quartile = 1;

MATERIALIZED یا NOT MATERIALIZED

تا نسخه‌ی 11 هر CTE یک «حصار بهینه‌سازی» بود: جدا اجرا و نتیجه‌اش ذخیره می‌شد و شرط‌های بیرونی به داخلش نمی‌رفت. از نسخه‌ی 12 CTEی که یک بار ارجاع شده و عارضه‌ی جانبی ندارد، مثل زیرکوئری inline می‌شود. می‌توانید صریح تعیین کنید:

WITH big AS MATERIALIZED (SELECT ...)           -- یک بار محاسبه، چند بار استفاده
WITH filtered AS NOT MATERIALIZED (SELECT ...)  -- اجازه بده شرط‌ها به داخل بروند

CTE بازگشتی

برای داده‌ی درختی: دسته‌بندی محصولات، ساختار سازمانی یا فهرست مواد (BOM). مثال: یک فرش از نخ‌های رنگی ساخته می‌شود و هر نخ رنگی از نخ خام و رنگ:

CREATE TABLE bom (parent text, child text, qty numeric, PRIMARY KEY (parent, child));
INSERT INTO bom VALUES
 ('AF-1203', 'نخ-لاکی', 4.2), ('AF-1203', 'نخ-سرمه‌ای', 3.1),
 ('نخ-لاکی', 'اکریلیک-خام', 1.02), ('نخ-لاکی', 'رنگ-قرمز', 0.05);

WITH RECURSIVE tree AS (
  SELECT child, qty, 1 AS depth, ARRAY[parent, child] AS path
  FROM bom WHERE parent = 'AF-1203'
  UNION ALL
  SELECT b.child, t.qty * b.qty, t.depth + 1, t.path || b.child
  FROM bom b JOIN tree t ON b.parent = t.child
  WHERE t.depth < 10 AND NOT b.child = ANY (t.path)
)
SELECT repeat('  ', depth - 1) || child AS item, round(qty, 3) AS kg_per_m2
FROM tree ORDER BY path;

بخش اول «لنگر» است و یک بار اجرا می‌شود؛ بخش دوم با ردیف‌های تولیدشده در دور قبل تکرار می‌شود تا وقتی ردیف جدیدی تولید نشود. شرط depth و path جلوی حلقه‌ی بی‌نهایت را در داده‌ی خراب می‌گیرد. از نسخه‌ی 14 می‌توانید به‌جای ساختن دستی path از SEARCH DEPTH FIRST BY child SET ord و CYCLE child SET is_cycle USING path استفاده کنید.

CTE تغییردهنده‌ی داده

WITH closed AS (
  UPDATE orders SET status = 'delivered'
  WHERE status = 'finishing' AND id = ANY ('{501,502,503}')
  RETURNING id, customer_id
)
INSERT INTO notifications (customer_id, message)
SELECT customer_id, 'سفارش ' || id || ' تحویل شد' FROM closed;

نکته‌هایی که کمتر کسی می‌داند

• همه‌ی بخش‌های یک WITH روی یک snapshot اجرا می‌شوند: اگر در یک CTE ردیفی را UPDATE کنید، CTE دیگر یا کوئری اصلی مقدار قدیمی جدول را می‌بیند؛ فقط از طریق RETURNING به داده‌ی جدید دسترسی دارید.
• CTEهای شامل INSERT/UPDATE/DELETE همیشه کامل اجرا می‌شوند، حتی اگر کوئری اصلی به آن‌ها ارجاع ندهد.
• در CTE بازگشتی، UNION (بدون ALL) ردیف‌های تکراری را حذف می‌کند و در گراف‌های ساده جلوی حلقه را می‌گیرد؛ اما وقتی ستون depth یا path دارید، هر ردیف یکتاست و UNION دیگر نجاتتان نمی‌دهد.
• اگر بعد از ارتقا از نسخه‌ی 11 کوئری‌ای کند شد، احتمالاً CTE آن inline شده و برنامه‌ریز مسیر بدتری انتخاب کرده؛ AS MATERIALIZED رفتار قدیم را برمی‌گرداند.
• برای پرهیز از محاسبه‌ی تکراری یک تابع گران (مثلاً فراخوانی تابع PL/pgSQL روی هر ردیف) در چند جای کوئری، آن را در یک CTE با MATERIALIZED حساب کنید؛ inline شدن می‌تواند آن را چند بار اجرا کند.