Menu

PostgreSQL CTE: zapytania WITH i WITH RECURSIVE

Wyrażenie CTE nadaje nazwę podzapytaniu w klauzuli WITH, żeby główne zapytanie czytało je jak tabelę. WITH RECURSIVE pozwala mu odwołać się do siebie i przechodzić drzewa oraz grafy.

Na tej stronie są działające edytory: edytuj, uruchamiaj i od razu zobacz wynik.

Wspólne wyrażenie tablicowe (CTE) to nazwane podzapytanie w klauzuli WITH przed głównym poleceniem: WITH name AS (SELECT ...) SELECT ... FROM name. Główne zapytanie czyta CTE jak tabelę, więc zagnieżdżone podzapytania stają się krokami czytanymi z góry na dół.

CTE totals sumuje zamówienia każdego klienta, a główne zapytanie zostawia klientów powyżej 100: cy z 300 i ana ze 180. CTE istnieje tylko w poleceniu, do którego należy.

Kilka CTE po kolei

Rozdzielaj CTE przecinkami. Każde może czytać CTE zdefiniowane przed nim.

average czyta totals, a główne zapytanie czyta oba. Sumy klientów to 180, 70 i 300, a ich średnia to około 183, więc powyżej jest tylko cy.

Przechodzenie drzewa przez WITH RECURSIVE

WITH RECURSIVE pozwala CTE odwołać się do samego siebie. Ma dwie części połączone przez UNION ALL:

  • zapytanie początkowe (kotwicę), które zwraca wiersze startowe;
  • zapytanie rekurencyjne, które złącza CTE z tabelą, żeby znaleźć kolejny poziom.

PostgreSQL uruchamia część rekurencyjną na wierszach z poprzedniego kroku i kończy, gdy krok nie zwróci żadnego wiersza.

Każdy krok znajduje osoby podlegające wierszom z poprzedniego kroku. Mia kończy na głębokości 3 ze ścieżką Rosa > Omar > Ivo > Mia. Żeby wypisać wszystkich pod jednym kierownikiem zamiast całego drzewa, zmień WHERE kotwicy na id = 2.

Żeby iść w górę, od pracownika do szczytu, odwróć warunek złączenia na s.id = c.manager_id.

Zatrzymanie nieskończonego rekurencyjnego CTE

Rekurencyjne CTE kończy się dopiero wtedy, gdy krok nic nie zwróci. Przy danych z pętlą (A podlega B, B podlega A) nigdy się nie skończy. Dodaj limit głębokości, na przykład WHERE c.depth < 20, albo użyj klauzuli CYCLE (PostgreSQL 14+), która oznacza pierwszy powtórzony wiersz i przestaje za nim podążać:

WITH RECURSIVE chain AS (
  SELECT id, manager_id FROM staff WHERE id = 5
  UNION ALL
  SELECT s.id, s.manager_id FROM staff s JOIN chain c ON s.id = c.manager_id
) CYCLE id SET is_cycle USING visited
SELECT * FROM chain;

Generowanie wierszy rekurencyjnym CTE

Część rekurencyjna nie potrzebuje tabeli. To zapytanie liczy od 1 do 5, przekazując wartość każdego wiersza dalej:

Dla zwykłych ciągów liczb albo dat krótsze jest generate_series.

Przenoszenie wierszy CTE modyfikującym dane

CTE może być poleceniem INSERT, UPDATE albo DELETE z klauzulą RETURNING, a główne polecenie czyta zwrócone wiersze. To przenosi stare zamówienia do tabeli archiwum w jednym poleceniu:

Zamówienia 1 i 3 są razem usuwane i wstawiane do archiwum, więc błąd zostawia obie tabele bez zmian. Wszystkie części polecenia widzą ten sam snapshot: główne zapytanie nie widzi efektu DELETE na orders, tylko to, co przekazuje RETURNING (zobacz RETURNING).

Sterowanie wbudowywaniem przez MATERIALIZED

Od PostgreSQL 12 CTE bez efektów ubocznych, do którego główne zapytanie odwołuje się raz, jest wbudowywane. Planer optymalizuje je razem z zewnętrznym zapytaniem, więc WHERE zapisane poza CTE nadal może użyć indeksu. CTE użyte dwa razy lub więcej jest liczone raz, a jego wynik jest używany ponownie. Możesz wymusić każde z tych zachowań:

WITH recent AS MATERIALIZED (
  SELECT * FROM events WHERE created_at > now() - interval '1 day'
)
SELECT * FROM recent WHERE user_id = 42;

WITH recent AS NOT MATERIALIZED (...)

MATERIALIZED liczy CTE raz, osobno, i nie wpuszcza do niego zewnętrznego WHERE. Użyj go, gdy wbudowany plan okaże się wolniejszy. NOT MATERIALIZED wymusza wbudowanie nawet wtedy, gdy CTE jest użyte więcej niż raz. Rekurencyjne CTE i CTE modyfikujące dane są zawsze liczone osobno.

Najczęściej zadawane pytania

Czy CTE jest w PostgreSQL szybsze od podzapytania?

Zwykle żadne nie jest szybsze. Od PostgreSQL 12 CTE użyte raz i bez efektów ubocznych jest wbudowywane w główne zapytanie, więc dostaje ten sam plan co równoważne podzapytanie. Przed wersją 12 każde CTE było liczone osobno, co mogło blokować użycie indeksu.

Czy można użyć CTE w UPDATE albo DELETE?

Tak. WITH może poprzedzać INSERT, UPDATE, DELETE i MERGE, a samo CTE może być poleceniem INSERT, UPDATE albo DELETE z RETURNING, którego wiersze czyta główne polecenie.

Jak zatrzymać nieskończoną pętlę w rekurencyjnym CTE?

Daj części rekurencyjnej warunek, który w końcu nie zwróci żadnych wierszy, na przykład limit głębokości (WHERE depth < 10). Dla danych, które mogą zapętlić się same w sobie, klauzula CYCLE (PostgreSQL 14+) oznacza powtórzony wiersz i na nim się zatrzymuje.

Czy CTE można użyć w więcej niż jednym zapytaniu?

Nie. CTE istnieje tylko w poleceniu, w którym je zapisano. Żeby wielokrotnie używać zapytania w różnych poleceniach, utwórz widok albo tabelę tymczasową.

Ilustracja języków programowania w Coddy

Ucz się programowania z Coddy

Zacznij