PostgreSQL میں CTEs اور recursive queries کیسے کام کرتے ہیں؟

Question

Accepted Answer

**CTE** (Common Table Expression)، جو **`WITH`** سے define ہوتا ہے، ایک named temporary result set ہے جو complex queries کو readable بناتا ہے۔ PostgreSQL مکمل طور پر CTEs کو support کرتا ہے، بشمول **recursive** CTEs (`WITH RECURSIVE`) جو hierarchical/tree data میں traversal کے لیے استعمال ہوتے ہیں۔

## بنیادی CTEs — readable query building

```sql
WITH active_users AS (
  SELECT id, name FROM users WHERE active = true
),
order_counts AS (
  SELECT user_id, COUNT(*) AS cnt FROM orders GROUP BY user_id
)
SELECT u.name, oc.cnt
FROM active_users u
JOIN order_counts oc ON u.id = oc.user_id;   -- compose named steps cleanly
```

CTEs ایک complex query کو named، readable steps میں توڑتے ہیں (chained، ہر ایک پچھلے کو reference کرتے ہوئے) — nested subqueries سے بہت زیادہ صاف ستھرا۔

## Recursive CTEs — hierarchies میں traversal

```sql
-- find all subordinates under employee 1 (an org-chart hierarchy)
WITH RECURSIVE subordinates AS (
  -- BASE CASE: the starting point
  SELECT id, name, manager_id FROM employees WHERE id = 1
  UNION ALL
  -- RECURSIVE STEP: find employees whose manager is in the result so far
  SELECT e.id, e.name, e.manager_id
  FROM employees e
  JOIN subordinates s ON e.manager_id = s.id
)
SELECT * FROM subordinates;          -- the entire tree under employee 1
```

**Recursive CTE** کا ایک base case (starting rows) اور ایک recursive step ہوتا ہے (`UNION ALL` خود CTE کے ساتھ join کرتے ہوئے)، جو نئی rows نہ آنے تک iterate کرتا ہے — **trees اور hierarchies** کے لیے بہترین ہے (org charts، category trees، bill-of-materials، graph paths)۔

## CTE materialization پر ایک نوٹ (Postgres-مخصوص)

```sql
-- Postgres 12+ may INLINE CTEs (optimize them) by default; force behavior if needed:
WITH x AS MATERIALIZED (...)      -- force materialization (compute once, store)
WITH x AS NOT MATERIALIZED (...)  -- force inlining (optimize into the main query)
```

Postgres 12 سے پہلے، CTEs ہمیشہ ایک "optimization fence" (materialized) تھے؛ اب وہ بہتر performance کے لیے inlined ہو سکتے ہیں — `MATERIALIZED`/`NOT MATERIALIZED` یہ کنٹرول کرتا ہے۔

## یہ کیوں اہم ہے

CTEs اور recursive queries **readability اور hierarchical data** کے لیے valuable PostgreSQL features ہیں، لہذا انہیں سمجھنا maintainable complex queries لکھنے اور tree-structured data کو handle کرنے کے لیے مفید ہے۔

روز مرہ کا فائدہ **readability** ہے: متعدد subquery levels والے complex queries کو maintain کرنا مشکل ہو جاتا ہے، جبکہ CTEs آپ کو انہیں named، sequential logical steps میں decompose کرنے دیتے ہیں (top-to-bottom پڑھیں)، جو clarity کو ڈراماٹیکلی بہتر بناتا ہے — CTEs کو maintainable SQL لکھنے کے لیے ایک اہم tool بناتے ہوئے۔

نمایاں صلاحیت **recursive CTEs** (`WITH RECURSIVE`) ہے، جو **hierarchical اور tree-structured data** کو query کرنے کی سہولت دیتے ہیں (organizational charts، category trees، bill-of-materials، threaded comments، graph traversal) — queries کی ایک class جو SQL میں بصورت دیگر بہت مشکل ہے، recursive CTEs کو hierarchical traversal کا standard حل بناتے ہوئے۔

Recursive CTE structure کو سمجھنا (base case + recursive step جو خود سے جڑتا ہے) اس عام ضرورت کے لیے اہم علم ہے۔

ایک مفید Postgres-مخصوص تفصیل **CTE materialization** behavior ہے (Postgres 12+ CTEs کو بہتر performance کے لیے inline کر سکتا ہے، `MATERIALIZED`/`NOT MATERIALIZED` سے کنٹرول کے قابل) — optimization کے لیے متعلقہ۔

چونکہ complex queries عام ہیں (اور readability maintenance کے لیے اہم ہے)، اور چونکہ hierarchical data (جو recursive CTEs اچھی طرح handle کرتے ہیں) اکثر نظر آتا ہے (org structures، nested categories)، CTEs اور recursive queries کو سمجھنا valuable، عملی طور پر متعلقہ PostgreSQL علم ہے جو query maintainability کو بہتر بناتا ہے اور hierarchical querying کو unlock کرتا ہے — clean، sophisticated SQL کے لیے ایک frequently-relevant صلاحیت اور ایک عام topic، خاص طور پر recursive form tree-structured data کو query کرنے کی بار بار آنے والی ضرورت کے لیے۔