Una common table expression (CTE) è una subquery con un nome, scritta in una clausola WITH prima dell'istruzione principale: WITH name AS (SELECT ...) SELECT ... FROM name. La query principale legge la CTE come una tabella, così le subquery annidate diventano passaggi che leggi dall'alto verso il basso.
La CTE totals somma gli ordini di ogni cliente, e la query principale tiene i clienti sopra 100: cy con 300 e ana con 180. Una CTE esiste solo per l'istruzione a cui appartiene.
Concatenare più CTE
Separa le CTE con delle virgole. Ognuna può leggere le CTE definite prima di lei.
average legge totals, e la query principale le legge entrambe. I totali dei clienti sono 180, 70 e 300 e la loro media è circa 183, quindi solo cy la supera.
Percorrere un albero con WITH RECURSIVE
WITH RECURSIVE permette a una CTE di riferirsi a sé stessa. Ha due parti unite da UNION ALL:
- la query di partenza (anchor), che restituisce le righe iniziali;
- la query ricorsiva, che unisce la CTE alla tabella per trovare il livello successivo.
PostgreSQL esegue la parte ricorsiva sulle righe prodotte dal passaggio precedente, e si ferma quando un passaggio non restituisce righe.
Ogni passaggio trova le persone che riportano alle righe del passaggio precedente. Mia arriva a profondità 3 con il percorso Rosa > Omar > Ivo > Mia. Per elencare tutti quelli sotto un solo manager invece dell'intero albero, cambia il WHERE della query di partenza in id = 2.
Per risalire, da un dipendente fino al vertice, inverti la condizione di join in s.id = c.manager_id.
Fermare una CTE ricorsiva infinita
Una CTE ricorsiva termina solo quando un passaggio non restituisce niente. Con dati che formano un ciclo (A riporta a B, B riporta ad A), non termina mai. Aggiungi un limite di profondità come WHERE c.depth < 20, oppure usa la clausola CYCLE (PostgreSQL 14+), che segna la prima riga ripetuta e smette di seguirla:
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;
Generare righe con una CTE ricorsiva
La parte ricorsiva non ha bisogno di una tabella. Questa query conta da 1 a 5 rimettendo in ingresso il valore di ogni riga:
Per semplici serie di numeri o di date, generate_series è più breve.
Spostare righe con una CTE che modifica i dati
Una CTE può essere un INSERT, UPDATE o DELETE con una clausola RETURNING, e l'istruzione principale legge le righe restituite. Questa istruzione sposta i vecchi ordini in una tabella di archivio in un colpo solo:
Gli ordini 1 e 3 vengono eliminati e inseriti nell'archivio insieme, quindi in caso di errore entrambe le tabelle restano com'erano. Tutte le parti dell'istruzione vedono lo stesso snapshot: la query principale non vede l'effetto del DELETE su orders, solo quello che RETURNING le passa (vedi RETURNING).
Controllare l'inlining con MATERIALIZED
Da PostgreSQL 12, una CTE senza effetti collaterali che la query principale referenzia una sola volta viene incorporata (inlining). Il planner la ottimizza insieme alla query esterna, quindi un WHERE scritto fuori dalla CTE può comunque usare un indice. Una CTE referenziata due o più volte viene calcolata una volta e il suo risultato viene riutilizzato. Puoi forzare l'uno o l'altro comportamento:
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 calcola la CTE una volta, da sola, e tiene fuori il WHERE esterno. Usalo quando il piano con inlining risulta più lento. NOT MATERIALIZED chiede l'inlining anche quando la CTE è referenziata più di una volta. Le CTE ricorsive e quelle che modificano i dati vengono sempre calcolate a parte.
Domande frequenti
In PostgreSQL una CTE è più veloce di una subquery?
Di solito nessuna delle due è più veloce. Da PostgreSQL 12 una CTE referenziata una sola volta e senza effetti collaterali viene incorporata nella query principale, quindi ottiene lo stesso piano della subquery equivalente. Prima della versione 12 ogni CTE veniva calcolata a parte, e questo poteva impedire l'uso degli indici.
Posso usare una CTE in un UPDATE o in un DELETE?
Sì. WITH può precedere INSERT, UPDATE, DELETE e MERGE, e la CTE stessa può essere un INSERT, UPDATE o DELETE con RETURNING, le cui righe vengono lette dall'istruzione principale.
Come fermo un ciclo infinito in una CTE ricorsiva?
Dai alla parte ricorsiva una condizione che prima o poi non restituisce più righe, come un limite di profondità (WHERE depth < 10). Per dati che possono tornare su sé stessi, la clausola CYCLE (PostgreSQL 14+) segna la riga ripetuta e si ferma lì.
Si può usare una CTE in più di una query?
No. Una CTE esiste solo per l'istruzione in cui è scritta. Per riutilizzare una query in più istruzioni, crea una vista o una tabella temporanea.