Πώς λειτουργούν οι CTEs και οι αναδρομικές ερωτήματα στο PostgreSQL;

Question

Accepted Answer

Ένα **CTE** (Common Table Expression), που ορίζεται με **`WITH`**, είναι ένα ονοματισμένο προσωρινό σύνολο αποτελεσμάτων που κάνει τα σύνθετα ερωτήματα ευανάγνωστα. Το PostgreSQL υποστηρίζει πλήρως τα CTEs, συμπεριλαμβανομένων των **αναδρομικών** CTEs (`WITH RECURSIVE`) για τη διάσχιση ιεραρχικών/δενδρικών δεδομένων.

## Basic 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 χωρίζουν ένα σύνθετο ερώτημα σε ονοματισμένα, ευανάγνωστα βήματα (συνδεδεμένα, καθένα αναφερόμενο σε προηγούμενα) — πολύ σαφέστερα από τα ένθετα subqueries.

## Αναδρομικά 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
```

Ένα **αναδρομικό CTE** έχει μια βασική περίπτωση (οι γραμμές εκκίνησης) και ένα αναδρομικό βήμα (`UNION ALL` που ενώνεται πάλι στο ίδιο το CTE), επαναλαμβάνοντας έως ότου δεν υπάρχουν νέες γραμμές — ιδανικό για **δέντρα και ιεραρχίες** (org charts, δέντρα κατηγοριών, bill-of-materials, graph paths).

## Μια σημείωση σχετικά με την CTE materialization (Postgres-specific)

```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 και τα αναδρομικά ερωτήματα είναι πολύτιμα χαρακτηριστικά του PostgreSQL για **ευανάγνωστη και ιεραρχικά δεδομένα**, επομένως η κατανόησή τους είναι χρήσιμη για τη σύνταξη συντηρήσιμων σύνθετων ερωτημάτων και τον χειρισμό δενδρικών δεδομένων.

Το καθημερινό όφελος είναι η **ευανάγνωστη**: τα σύνθετα ερωτήματα με πολλά επίπεδα subqueries γίνονται δύσκολα στη συντήρηση, ενώ τα CTEs σας επιτρέπουν να τα αναλύσετε σε ονοματισμένα, διαδοχικά λογικά βήματα (ανάγνωση από πάνω προς τα κάτω), βελτιώνοντας δραματικά τη σαφήνεια — κάνοντας τα CTEs ένα σημαντικό εργαλείο για τη σύνταξη συντηρήσιμου SQL.

Η εξέχουσα δυνατότητα είναι τα **αναδρομικά CTEs** (`WITH RECURSIVE`), που επιτρέπουν την αναζήτηση **ιεραρχικών και δενδρικών δεδομένων** (org charts, δέντρα κατηγοριών, bill-of-materials, threaded comments, graph traversal) — μια κατηγορία ερωτημάτων διαφορετικά πολύ δύσκολη στο SQL, κάνοντας τα αναδρομικά CTEs την τυπική λύση για ιεραρχική διάσχιση.

Η κατανόηση της δομής του αναδρομικού CTE (βασική περίπτωση + αναδρομικό βήμα που ενώνεται πάλι στον εαυτό του) είναι βασική γνώση για αυτή την κοινή ανάγκη.

Ένα χρήσιμο Postgres-specific detail είναι η συμπεριφορά **CTE materialization** (Postgres 12+ μπορεί να ενσωματώσει CTEs για καλύτερη απόδοση, ελεγχόμενο με `MATERIALIZED`/`NOT MATERIALIZED`) — σχετικό για βελτιστοποίηση.

Επειδή τα σύνθετα ερωτήματα είναι συνηθισμένα (και η ευανάγνωστη έχει σημασία για τη συντήρηση), και επειδή τα ιεραρχικά δεδομένα (τα οποία τα αναδρομικά CTEs χειρίζονται μοναδικά καλά) εμφανίζονται συχνά (org structures, nested categories), η κατανόηση των CTEs και των αναδρομικών ερωτημάτων είναι πολύτιμη, πρακτικά σχετική γνώση του PostgreSQL που βελτιώνει τη συντηρησιμότητα του ερωτήματος και ξεκλειδώνει ιεραρχικές αναζητήσεις — μια συχνά σχετική δυνατότητα για καθαρό, sophisticated SQL και ένα κοινό θέμα, ειδικά η αναδρομική μορφή για την επαναλαμβανόμενη ανάγκη αναζήτησης δενδρικών δεδομένων.