Si funksionojnë CTEs dhe pyetjet rekursive në PostgreSQL?

Question

Accepted Answer

Një **CTE** (Common Table Expression), e përcaktuar me **`WITH`**, është një grup rezultatesh i përkohshëm i emërtuar që e bën një pyetje komplekse të lexueshme. PostgreSQL mbështet plotësisht CTEs, përfshirë CTEs **rekursive** (`WITH RECURSIVE`) për të përshkuar të dhëna hierarkike/pemë.

## CTEs bazike — ndërtimi i pyetjeve të lexueshme

```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 e ndajnë një pyetje komplekse në hapa të emërtuar, të lexueshëm (të zinxhiruar, secila referencon atë paraardhëse) — shumë më e qartë se nënpyetjet e zagare.

## CTEs rekursive — përshko hierarkitë

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

Një **CTE rekursive** ka një rast bazik (rreshtat fillestare) dhe një hap rekursiv (`UNION ALL` që lidhet përsëri me vetë CTE-në), duke përsëritur derisa nuk ka rreshta të rinj — ideale për **pemë dhe hierarki** (organigramë, pemë kategorish, bill-of-materials, shtigje grafi).

## Një shënim për materializimin e CTE (specifik për 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)
```

Para Postgres 12, CTEs ishin gjithmonë një "pengesë optimizimi" (materializuar); tani mund të jenë inline për performancë më të mirë — `MATERIALIZED`/`NOT MATERIALIZED` kontrollon këtë.

## Pse ka rëndësi

CTEs dhe pyetjet rekursive janë veçori të vlefshme të PostgreSQL për **lexueshmërinë dhe të dhënat hierarkike**, kështu që t'i kuptosh ato është i dobishëm për të shkruar pyetje komplekse të mirëmbajtshme dhe për të trajtuar të dhëna të strukturuara si pemë.

Beneficiu i përditshëm është **lexueshmëria**: pyetjet komplekse me nivele të shumta nënpyetjesh bëhen të vështira në mirëmbajtje, ndërsa CTEs të lejojnë të dekompozoni ato në hapa të emërtuar, vijues logjikë (lexo nga lart-poshtë), që përmirëson ndjeshëm qartësinë — duke e bërë CTEs një mjet të rëndësishëm për të shkruar SQL të mirëmbajtshëm.

Evenimi dalluese është **CTEs rekursive** (`WITH RECURSIVE`), e cila mundëson pyetjen e **të dhënave hierarkike dhe të strukturuara si pemë** (organigramet, pemë kategorish, bill-of-materials, komente të fileto, përshkim grafet) — një klasë pyetjesh në të kundërt shumë të vështira në SQL, duke bërë CTEs rekursive zgjidhjen standarde për përshkimin hierarkik.

Përkuptimi i strukturës CTE rekursive (rasti bazik + hapi rekursiv që lidhet përsëri me veten) është njohuri kyçe për këtë nevojë të zakonshme.

Un detaje i dobishëm specifik për Postgres është sjellja e **materializimit të CTE** (Postgres 12+ mund të inline CTEs për performancë më të mirë, e kontrollueshme me `MATERIALIZED`/`NOT MATERIALIZED`) — relevante për optimizimin.

Pasi pyetjet komplekse janë të zakonshme (dhe lexueshmëria ka rëndësi për mirëmbajtjen), dhe pasi të dhënat hierarkike (të cilat CTEs rekursive i trajtojnë mirë në mënyrë unike) shfaqen shpesh (struktura org, kategori të zagare), t'i kuptosh CTEs dhe pyetjet rekursive është njohuri e vlefshme, praktikisht relevante PostgreSQL që përmirëson mirëmbajtjen e pyetjeve dhe hap aftësinë për përshkimin hierarkik — një aftësi të shpeshtë relevante për SQL të pastër, të sofistikuar dhe një temë e zakonshme, veçanërisht forma rekursive për nevojën e përsëritur të pyetjes së të dhënave të strukturuara si pemë.