PostgreSQL मा CTEs र recursive queries कसरी काम गर्छन्?

Question

Accepted Answer

एक **CTE** (Common Table Expression), **`WITH`** सँग परिभाषित, एक नामकृत अस्थायी परिणाम सेट हो जसले जटिल क्वेरीहरूलाई पठनीय बनाउँछ। PostgreSQL ले CTEs लाई पूर्ण रूपमा समर्थन गर्छ, जसमा **recursive** CTEs (`WITH RECURSIVE`) पनि समावेश छ, जो hierarchical/tree डेटा को लागी ट्र्याभर्स गर्न प्रयोग गरिन्छ।

## आधारभूत CTEs — पठनीय क्वेरी निर्माण

```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 ले जटिल क्वेरीलाई नामकृत, पठनीय चरणहरूमा विभाजित गर्छ (जोडिएको, प्रत्येकले पूर्ववर्ती संदर्भ गर्छ) — nested subqueries भन्दा धेरै स्पष्ट।

## Recursive CTEs — hierarchies को लागी ट्र्याभर्स गर्नुहोस्

```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** को आधार मामला (सुरुवाती पङ्क्तिहरू) र एक recursive चरण (CTE आफैमा फिर्ता जोडिने `UNION ALL`) हुन्छ, कुनै नयाँ पङ्क्तिहरू नभएसम्म पुनरावृत्ति गर्दै — **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) थिए; अब तिनीहरू राम्रो प्रदर्शनको लागी inline गर्न सकिन्छ — `MATERIALIZED`/`NOT MATERIALIZED` यसलाई नियन्त्रण गर्छ।

## किन यो महत्त्वपूर्ण छ

CTEs र recursive queries **पठनीयता र hierarchical डेटा** को लागी मूल्यवान PostgreSQL features हुन्, तसर्थ यिनीहरू बुझ्नु रक्षणीय जटिल क्वेरीहरू लेख्न र tree-संरचित डेटा को लागी उपयोगी छ।

दैनिक लाभ **पठनीयता** हो: multiple subquery स्तरहरु को साथ जटिल क्वेरीहरू राम्रो गर्न गाह्रो हुन्छ, जबकि CTEs ले तिनीहरूलाई नामकृत, क्रमिक तार्किक चरणहरूमा विघटन गर्न दिन्छ (शीर्षबाट-तलसम्म पढ्नुहोस्), स्पष्टतालाई नाटकीय रूपमा सुधार गर्छ — CTEs लाई रक्षणीय SQL लेख्ने लागी महत्त्वपूर्ण उपकरण बनाउँछ।

अनौठो क्षमता **recursive CTEs** हो (`WITH RECURSIVE`), जसले **hierarchical र tree-संरचित डेटा** को क्वेरी गर्न सक्षम गर्छ (organizational charts, category trees, bill-of-materials, threaded comments, graph traversal) — क्वेरीहरुको एक वर्ग जो अन्यथा SQL मा धेरै गाह्रो हुन्छ, recursive CTEs लाई hierarchical traversal को लागी मानक समाधान बनाउँछ।

Recursive CTE संरचना (आधार मामला + recursive चरण आफैमा फिर्ता जोडिने) बुझ्नु यो सामान्य आवश्यकताको लागी महत्त्वपूर्ण ज्ञान हो।

एक उपयोगी Postgres-विशिष्ट विवरण **CTE materialization** व्यवहार हो (Postgres 12+ ले राम्रो प्रदर्शनको लागी CTEs लाई inline गर्न सक्छ, `MATERIALIZED`/`NOT MATERIALIZED` सँग नियन्त्रणीय) — optimization को लागी प्रासंगिक।

जटिल क्वेरीहरू सामान्य हुन् (र पठनीयता रक्षणको लागी महत्त्वपूर्ण छ), र hierarchical डेटा (जो recursive CTEs ले अनन्य रूपमा राम्रोसँग ह्याङ्डल गर्छ) बारम्बार देखा पर्छ (org संरचनाहरू, nested categories), CTEs र recursive queries बुझ्नु मूल्यवान, व्यावहारिक-प्रासंगिक PostgreSQL ज्ञान हो जसले क्वेरी रक्षणीयतालाई सुधार गर्छ र hierarchical क्वेरीलाई खोल्छ — स्वच्छ, परिष्कृत SQL को लागी बारम्बार-प्रासंगिक क्षमता र एक सामान्य विषय, विशेष गरी tree-संरचित डेटा क्वेरी गर्ने आवर्ती आवश्यकताको लागी recursive फारम।