CTE (Common Table Expression), zdefiniowane za pomocą WITH, to nazwany tymczasowy zestaw wyników, który czyni złożone zapytania czytelnym. PostgreSQL w pełni obsługuje CTE, w tym rekurencyjne CTE (WITH RECURSIVE) do przeglądania danych hierarchicznych/drzewiastych.
active_users (
id, name users active
),
order_counts (
user_id, () cnt orders user_id
)
u.name, oc.cnt
active_users u
order_counts oc u.id oc.user_id;
CTE dzielą złożone zapytanie na nazwane, czytelne kroki (połączone w łańcuch, każdy odwołujący się do wcześniejszych) — znacznie jaśniej niż zagnieżdżone podzapytania.
-- 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
Rekurencyjne CTE ma przypadek bazowy (wiersze początkowe) i krok rekurencyjny (UNION ALL łączący się z samym CTE), iterujący aż do braku nowych wierszy — idealne dla drzew i hierarchii (schematy organizacyjne, drzewa kategorii, struktury listy materiałów, ścieżki grafów).
-- 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)
Przed Postgres 12, CTE były zawsze "ogrodzeniem optymalizacji" (materializowane); teraz mogą być wbudowywane dla lepszej wydajności — MATERIALIZED/NOT MATERIALIZED kontroluje to.
CTE i zapytania rekurencyjne to cenne funkcje PostgreSQL dla czytelności i danych hierarchicznych, dlatego zrozumienie ich jest przydatne do pisania łatwych w utrzymaniu złożonych zapytań i obsługi danych o strukturze drzewiastej.
Codzienną korzyścią jest czytelność: złożone zapytania z wieloma poziomami podzapytań stają się trudne w utrzymaniu, podczas gdy CTE pozwalają rozbić je na nazwane, sekwencyjne kroki logiczne (czytane od góry do dołu), dramatycznie poprawiając jasność — czyniąc CTE ważnym narzędziem do pisania łatwego w utrzymaniu SQL.
Wyróżniającą się możliwością są rekurencyjne CTE (WITH RECURSIVE), które umożliwiają wykonywanie zapytań na danych hierarchicznych i o strukturze drzewiastej (schematy organizacyjne, drzewa kategorii, struktury listy materiałów, komentarze wątkowe, przechodzenie grafów) — klasa zapytań w innym przypadku bardzo trudna do zrealizowania w SQL, czyniąc rekurencyjne CTE standardowym rozwiązaniem do przechodzenia hierarchii.
Zrozumienie struktury rekurencyjnego CTE (przypadek bazowy + krok rekurencyjny łączący się z sobą) jest kluczową wiedzą do tego powszechnego potrzeby.
Przydatnym szczegółem specyficznym dla Postgresa jest zachowanie materializacji CTE (Postgres 12+ może wbudowywać CTE dla lepszej wydajności, kontrolowalne za pomocą MATERIALIZED/NOT MATERIALIZED) — istotne dla optymalizacji.
Ponieważ złożone zapytania są powszechne (a czytelność ma znaczenie dla utrzymania), a hierarchiczne dane (które rekurencyjne CTE obsługują wyjątkowo dobrze) pojawiają się często (struktury organizacyjne, zagnieżdżone kategorie), zrozumienie CTE i zapytań rekurencyjnych to cenna, praktycznie istotna wiedza PostgreSQL, która poprawia łatwość utrzymania zapytań i umożliwia przeglądanie hierarchii — często stosowana możliwość do czystego, zaawansowanego SQL i powszechny temat, szczególnie forma rekurencyjna dla powtarzającej się potrzeby przeglądania danych o strukturze drzewiastej.