PostgreSQL-இல் CTE-கள் மற்றும் recursive queries எவ்வாறு செயல்படுகின்றன?

Question

Accepted Answer

**CTE** (Common Table Expression), **`WITH`** உடன் வரையறுக்கப்பட்டது, ஒரு பெயரிடப்பட்ட தற்காலிக முடிவுத் தொகுப்பாகும், இது சிக்கலான வினவல்களை படிக்கக்கூடியதாக்குகிறது. PostgreSQL CTEs-ஐ முழுவதுமாக ஆதரிக்கிறது, தொடர்ச்சியான/மரம் தரவுகளை கடக்க **recursive** CTEs (`WITH RECURSIVE`) உட்பட.

## அடிப்படை CTE-கள் — படிக்கக்கூடிய வினவல் கட்டமைப்பு

```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
```

CTE-கள் ஒரு சிக்கலான வினவலை பெயரிடப்பட்ட, படிக்கக்கூடிய படிகளாக உடைக்கின்றன (இணைக்கப்பட்ட, ஒவ்வொன்றும் முந்தையவற்றை குறிப்பிடுகிறது) — உள்ளிணைக்கப்பட்ட subqueries-ஐ விட மிகத் தெளிவாக உள்ளது.

## Recursive CTE-கள் — பதிவுசெய்யப்பட்ட கட்டமைப்புகளை கடக்கவும்

```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 படி (`UNION ALL` CTE-ஐ தன்னுள்ளே இணைக்க) உள்ளது, புதிய வரிசைகள் இல்லாத வரை மீண்டும் செய்கிறது — **மரங்கள் மற்றும் பதிவுசெய்யப்பட்ட கட்டமைப்புகளுக்கு** சிறந்தது (நிறுவன விளக்கப்படங்கள், பிரிவு மரங்கள், பொருட்களின் மசோதா, வரைபடப் பாதைகள்).

## CTE பொருளாதாரம் குறிப்பு (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-க்கு முன், CTE-கள் எப்போதும் ஒரு "optimization fence" (பொருளாக்கம்) ஆக இருந்தன; இப்போது அவை சிறந்த செயல்பாட்டிற்கு உள்ளிணைக்கப்படலாம் — `MATERIALIZED`/`NOT MATERIALIZED` இதைக் கட்டுப்படுத்துகிறது.

## இது ஏன் முக்கியமானது

CTE-கள் மற்றும் recursive வினவல்கள் **படிக்கக்கூடியது மற்றும் பதிவுசெய்யப்பட்ட தரவு** புனையப்பட்ட PostgreSQL-ஆகும், எனவே அவற்றைப் புரிந்துகொள்வது பராமரிக்கக்கூடிய சிக்கலான வினவல்களை எழுதுவதற்கும் மரம்-கட்டமைக்கப்பட்ட தரவைக் கையாள்வதற்கும் பயனுள்ளதாக இருக்கிறது.

நாள்தோறும் நன்மை **படிக்கக்கூடியது**: பல subquery நிலைகளைக் கொண்ட சிக்கலான வினவல்கள் பராமரிக்க கடினமாக்குகின்றன, வேறுவிதமாக CTE-கள் உங்களை பெயரிடப்பட்ட, வரிசையான தர্க்கக் படிகளாக சிதைக்க அனுமதிக்கின்றன (மேலிலிருந்து கீழாக படிக்கவும்), தெளிவுத்தைப் பெரிதும் மேம்படுத்துகிறது — CTE-களை பராமரிக்கக்கூடிய SQL எழுதுவதற்கான முக்கிய கருவியாக்குகிறது.

மிக சிறந்த திறன் **recursive CTE-கள்** (`WITH RECURSIVE`), இது **பதிவுசெய்யப்பட்ட மற்றும் மரம்-கட்டமைக்கப்பட்ட தரவு** (அமைப்பிணய விளக்கப்படங்கள், பிரிவு மரங்கள், பொருட்களின் மசோதா, நூல்பவைத்த கருத்துக்கள், வரைபடப் பயணம்) வினவுவதை செயல்படுத்துகிறது — SQL இல் வேறுவிதமாக மிகவும் கடினமான வினவல்களின் வகுப்பு, recursive CTE-களை பதிவுசெய்யப்பட்ட பயணத்திற்கான நிலையான தீர்வாக்குகிறது.

Recursive CTE கட்டமைப்புப் புரிந்துகொள்வது (அடிப்படை வழக்கு + தன்னுள்ளே இணைக்க recursive படி) இந்த பொதுவான தேவைக்கான முக்கிய அறிவாகும்.

பயனுள்ள Postgres-குறிப்பிட்ட விவரம் **CTE பொருளாதாரம்** நடத்தை (Postgres 12+ சிறந்த செயல்பாட்டிற்கு CTE-களை உள்ளிணைக்க முடியும், `MATERIALIZED`/`NOT MATERIALIZED` உடன் கட்டுப்படுத்தக்கூடியது) — உச்சயளவிற்கு பொருத்தமானது.

சிக்கலான வினவல்கள் பொதுவாக உள்ளன (மற்றும் படிக்கக்கூடியது பராமரிப்பிற்கு முக்கியமாகும்), மற்றும் பதிவுசெய்யப்பட்ட தரவு (recursive CTE-கள் நன்றாக கையாள்ளுகின்றன) அடிக்கடி தோன்றுவதால் (அমைப்புக் கட்டமைப்புகள், உள்ளிணைக்கப்பட்ட பிரிவுகள்), CTE-கள் மற்றும் recursive வினவல்களைப் புரிந்துகொள்வது மதிப்புக்குரியது, நடைமுறையாக-பொருத்தமான PostgreSQL அறிவு வினவல் பராமரிக்கவோகையை மேம்படுத்துகிறது மற்றும் பதிவுசெய்யப்பட்ட வினவுவதை திறந்துவிடுகிறது — தூய, அதிவேக SQL மற்றும் ஒரு பொதுவான பாடத்திற்கான அடிக்கடி-பொருத்தமான திறன், குறிப்பாக மரம்-கட்டமைக்கப்பட்ட தரவுக்கு வினவ பொதுவான தேவைக்கான recursive வடிவம்.