PostgreSQL ਵਿੱਚ CTEs ਅਤੇ recursive queries ਕਿਵੇਂ ਕੰਮ ਕਰਦੇ ਹਨ?

Question

Accepted Answer

A **CTE** (Common Table Expression), **`WITH`** ਨਾਲ ਪਰਿਭਾਸ਼ਿਤ, ਇੱਕ ਨਾਮਿਤ ਅਸਥਾਈ ਨਤੀਜਾ ਸੈੱਟ ਹੈ ਜੋ ਗੁੰਝਲਦਾਰ queries ਨੂੰ ਪੜ੍ਹਨਯੋਗ ਬਣਾਉਂਦਾ ਹੈ। PostgreSQL ਵਿੱਚ CTEs ਦਾ ਪੂਰਨ ਸਮਰਥਨ ਹੈ, ਜਿਸ ਵਿੱਚ **recursive** CTEs (`WITH RECURSIVE`) ਸ਼ਾਮਲ ਹਨ ਜੋ ਹਾਇਰਾਰਕੀਕਲ/ਟ੍ਰੀ ਡੇਟਾ ਨੂੰ ਟ੍ਰਾਵਰਸ ਕਰਨ ਲਈ ਹਨ।

## ਬੁਨਿਆਦੀ CTEs — ਪੜ੍ਹਨਯੋਗ query ਬਿਲਡਿੰਗ

```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 ਇੱਕ ਗੁੰਝਲਦਾਰ query ਨੂੰ ਨਾਮਿਤ, ਪੜ੍ਹਨਯੋਗ ਪਗਾਂ ਵਿੱਚ ਵੰਡਦੇ ਹਨ (ਚੇਨਡ, ਹਰ ਇੱਕ ਪਿਛਲੇ ਨੂੰ ਸੰਦਰਭਿਤ ਕਰਦਾ ਹੈ) — ਨੇਸਟਡ subqueries ਨਾਲੋਂ ਬਹੁਤ ਸਪੱਸ਼ਟ।

## Recursive CTEs — ਹਾਇਰਾਰਕੀ ਨੂੰ ਟ੍ਰਾਵਰਸ ਕਰੋ

```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) ਸਨ; ਹੁਣ ਉਹ ਬਿਹਤਰ ਕਾਰਜ-ਪ੍ਰਦਰਸ਼ਨ ਲਈ ਇਨਲਾਈਨ ਹੋ ਸਕਦੇ ਹਨ — `MATERIALIZED`/`NOT MATERIALIZED` ਇਸ ਨੂੰ ਨਿਯੰਤਰਿਤ ਕਰਦਾ ਹੈ।

## ਇਹ ਕਿਉਂ ਮਹੱਤਵਪੂਰਨ ਹੈ

CTEs ਅਤੇ recursive queries **ਪੜ੍ਹਨਯੋਗਤਾ ਅਤੇ ਹਾਇਰਾਰਕੀਕਲ ਡੇਟਾ** ਲਈ ਕੀਮਤੀ PostgreSQL ਵਿਸ਼ੇਸ਼ਤਾਵਾਂ ਹਨ, ਇਸ ਲਈ ਉਨ੍ਹਾਂ ਨੂੰ ਸਮਝਣਾ ਦੇਖ-ਭਾਲ ਯੋਗ ਗੁੰਝਲਦਾਰ queries ਲਿਖਣ ਅਤੇ tree-ਤਰਜੀਹੀ ਡੇਟਾ ਨਾਲ ਨਿਪਟਣ ਲਈ ਉਪਯੋਗੀ ਹੈ।

ਰੋਜ਼ਮਰਾਂ ਦਾ ਲਾਭ **ਪੜ੍ਹਨਯੋਗਤਾ** ਹੈ: ਮਲਟੀਪਲ subquery ਲਬਲੀਜ਼ ਵਾਲੀਆਂ ਗੁੰਝਲਦਾਰ queries ਦੇਖ-ਭਾਲ ਲਈ ਮੁਸ਼ਕਿਲ ਬਣ ਜਾਂਦੀਆਂ ਹਨ, ਜਦਕਿ CTEs ਤੁਹਾਨੂੰ ਉਨ੍ਹਾਂ ਨੂੰ ਨਾਮਿਤ, ਕ੍ਰਮਵਾਰ ਤਰਕਿਕ ਪਗਾਂ ਵਿੱਚ ਅਲੱਗ ਕਰਨ ਦਿੰਦੇ ਹਨ (ਉਪਰ-ਤੋਂ-ਹੇਠ ਪੜ੍ਹੋ), ਸਪੱਸ਼ਟਤਾ ਵਿੱਚ ਨਾਟਕੀ ਸੁਧਾਰ — CTEs ਨੂੰ ਦੇਖ-ਭਾਲ ਯੋਗ SQL ਲਿਖਣ ਲਈ ਇੱਕ ਮਹੱਤਵਪੂਰਨ ਸਾਧਨ ਬਣਾਉਂਦਾ ਹੈ।

ਬੇਮਿਸਾਲ ਯੋਗਤਾ **recursive CTEs** ਹੈ (`WITH RECURSIVE`), ਜੋ **ਹਾਇਰਾਰਕੀਕਲ ਅਤੇ tree-ਤਰਜੀਹੀ ਡੇਟਾ** ਦੀ ਪ੍ਰਸ਼ਨ ਕਰਨ ਦੇ ਯੋਗ ਬਣਾਉਂਦਾ ਹੈ (ਸਾਂਗਠਨਿਕ ਚਾਰਟ, ਸ਼੍ਰੇਣੀ trees, bill-of-materials, threaded comments, graph traversal) — SQL ਵਿੱਚ ਬਿਲਕੁਲ ਮੁਸ਼ਕਿਲ queries ਦੀ ਇੱਕ ਕਲਾਸ, recursive CTEs ਨੂੰ ਹਾਇਰਾਰਕੀਕਲ ਟ੍ਰਾਵਰਸਲ ਲਈ ਮਿਆਰੀ ਹੱਲ ਬਣਾਉਂਦਾ ਹੈ।

Recursive CTE ਬਣਤਰ (ਬੇਸ ਕੇਸ + recursive ਸਟੈਪ ਆਪ ਨੂੰ ਜੋੜਨਾ) ਨੂੰ ਸਮਝਣਾ ਇਸ ਆਮ ਲੋੜ ਲਈ ਮੁੱਖ ਗਿਆਨ ਹੈ।

ਇੱਕ ਉਪਯੋਗੀ Postgres-ਵਿਸ਼ੇਸ਼ ਵੇਰਵਾ **CTE materialization** ਵਿਵਹਾਰ ਹੈ (Postgres 12+ CTEs ਨੂੰ ਬਿਹਤਰ ਕਾਰਜ-ਪ੍ਰਦਰਸ਼ਨ ਲਈ ਇਨਲਾਈਨ ਕਰ ਸਕਦਾ ਹੈ, `MATERIALIZED`/`NOT MATERIALIZED` ਨਾਲ ਨਿਯੰਤ੍ਰਿਤ) — ਅਨੁਕੂਲਤਾ ਲਈ ਪ੍ਰਸੰਗਿਕ।

ਕਿਉਂਕਿ ਗੁੰਝਲਦਾਰ queries ਆਮ ਹਨ (ਅਤੇ ਦੇਖ-ਭਾਲ ਲਈ ਪੜ੍ਹਨਯੋਗਤਾ ਮਾਇਨੇ ਰੱਖਦੀ ਹੈ), ਅਤੇ ਕਿਉਂਕਿ ਹਾਇਰਾਰਕੀਕਲ ਡੇਟਾ (ਜਿਸਨੂੰ recursive CTEs ਵਿਲੱਖਣ ਤਰੀਕੇ ਨਾਲ ਚੰਗੀ ਤਰ੍ਹਾਂ ਸੰਭਾਲਦੇ ਹਨ) ਅਕਸਰ ਦਿਖਾਈ ਦਿੰਦਾ ਹੈ (org structures, nested categories), CTEs ਅਤੇ recursive queries ਨੂੰ ਸਮਝਣਾ ਕੀਮਤੀ, ਅਮਲੀ PostgreSQL ਗਿਆਨ ਹੈ ਜੋ query ਦੇਖ-ਭਾਲ ਨੂੰ ਬਿਹਤਰ ਬਣਾਉਂਦਾ ਹੈ ਅਤੇ ਹਾਇਰਾਰਕੀਕਲ ਪ੍ਰਸ਼ਨ ਸੰਭਵ ਕਰਦਾ ਹੈ — ਸਾਫ਼, ਅਤਿ-ਆਧੁਨਿਕ SQL ਲਈ ਇੱਕ ਅਕਸਰ-ਪ੍ਰਸੰਗਿਕ ਯੋਗਤਾ ਅਤੇ ਇੱਕ ਆਮ ਵਿਸ਼ਾ, ਖਾਸ ਕਰਕੇ recursive ਰੂਪ tree-ਤਰਜੀਹੀ ਡੇਟਾ ਦੀ ਪ੍ਰਸ਼ਨ ਕਰਨ ਦੀ ਦੁਹਰਾਈ ਜਾਣ ਵਾਲੀ ਲੋੜ ਲਈ।