Common table expression (CTE) adalah subquery bernama di klausa WITH sebelum pernyataan utama: WITH name AS (SELECT ...) SELECT ... FROM name. Query utama membaca CTE seperti tabel, jadi subquery bersarang berubah menjadi langkah-langkah yang dibaca dari atas ke bawah.
CTE totals menjumlahkan pesanan setiap pelanggan, dan query utama menyimpan pelanggan di atas 100: cy dengan 300 dan ana dengan 180. CTE hanya ada untuk pernyataan tempat ia berada.
Merangkai beberapa CTE
Pisahkan CTE dengan koma. Setiap CTE bisa membaca CTE yang didefinisikan sebelumnya.
average membaca totals, dan query utama membaca keduanya. Total per pelanggan adalah 180, 70, dan 300 dan rata-ratanya sekitar 183, jadi hanya cy yang di atasnya.
Menelusuri pohon dengan WITH RECURSIVE
WITH RECURSIVE memungkinkan CTE merujuk dirinya sendiri. Ia punya dua bagian yang digabung dengan UNION ALL:
- query anchor, yang mengembalikan baris awal;
- query rekursif, yang melakukan join CTE ke tabel untuk menemukan level berikutnya.
PostgreSQL menjalankan bagian rekursif pada baris yang dihasilkan langkah sebelumnya, dan berhenti saat sebuah langkah tidak mengembalikan baris.
Setiap langkah menemukan orang-orang yang melapor ke baris dari langkah sebelumnya. Mia berakhir di kedalaman 3 dengan path Rosa > Omar > Ivo > Mia. Untuk menampilkan semua orang di bawah satu manajer alih-alih seluruh pohon, ubah WHERE di anchor menjadi id = 2.
Untuk menelusuri ke atas, dari seorang karyawan sampai puncak, tukar kondisi join-nya menjadi s.id = c.manager_id.
Menghentikan CTE rekursif yang tak berujung
CTE rekursif hanya berakhir saat sebuah langkah tidak mengembalikan apa pun. Dengan data yang berputar (A melapor ke B, B melapor ke A), ia tidak pernah berakhir. Tambahkan batas kedalaman seperti WHERE c.depth < 20, atau pakai klausa CYCLE (PostgreSQL 14+), yang menandai baris pertama yang berulang dan berhenti mengikutinya:
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;
Membuat baris dengan CTE rekursif
Bagian rekursifnya tidak butuh tabel. Contoh ini menghitung dari 1 sampai 5 dengan memasukkan kembali nilai setiap baris:
Untuk deret angka atau tanggal biasa, generate_series lebih singkat.
Memindahkan baris dengan CTE yang mengubah data
CTE bisa berupa INSERT, UPDATE, atau DELETE dengan klausa RETURNING, dan pernyataan utama membaca baris yang dikembalikan. Contoh ini memindahkan pesanan lama ke tabel arsip dalam satu pernyataan:
Pesanan 1 dan 3 dihapus dan dimasukkan ke arsip secara bersamaan, jadi kegagalan membiarkan kedua tabel seperti semula. Semua bagian pernyataan melihat snapshot yang sama: query utama tidak melihat efek DELETE pada orders, hanya apa yang diteruskan RETURNING (lihat RETURNING).
Mengatur inlining dengan MATERIALIZED
Sejak PostgreSQL 12, CTE tanpa efek samping yang dirujuk sekali oleh query utama akan di-inline. Planner mengoptimalkannya bersama query luar, jadi WHERE yang ditulis di luar CTE tetap bisa memakai index. CTE yang dirujuk dua kali atau lebih dihitung sekali dan hasilnya dipakai ulang. Kamu bisa memaksakan salah satu perilaku:
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 menghitung CTE sekali, secara terpisah, dan menjaga WHERE luar tidak masuk ke dalamnya. Pakai saat rencana yang di-inline ternyata lebih lambat. NOT MATERIALIZED meminta inlining bahkan saat CTE dirujuk lebih dari sekali. CTE rekursif dan CTE yang mengubah data selalu dihitung terpisah.
Pertanyaan yang sering diajukan
Apakah CTE lebih cepat daripada subquery di PostgreSQL?
Biasanya tidak ada yang lebih cepat. Sejak PostgreSQL 12, CTE yang dirujuk sekali dan tidak punya efek samping di-inline ke query utama, jadi mendapat rencana yang sama dengan subquery yang setara. Sebelum versi 12, setiap CTE dihitung terpisah, yang bisa menghalangi pemakaian index.
Bisakah memakai CTE di UPDATE atau DELETE?
Bisa. WITH bisa ditulis sebelum INSERT, UPDATE, DELETE, dan MERGE, dan CTE-nya sendiri bisa berupa INSERT, UPDATE, atau DELETE dengan RETURNING, yang barisnya dibaca oleh pernyataan utama.
Bagaimana cara menghentikan perulangan tanpa akhir di CTE rekursif?
Beri bagian rekursifnya kondisi yang pada akhirnya tidak mengembalikan baris, seperti batas kedalaman (WHERE depth < 10). Untuk data yang bisa berputar kembali ke dirinya sendiri, klausa CYCLE (PostgreSQL 14+) menandai baris yang berulang dan berhenti di sana.
Bisakah satu CTE dipakai di lebih dari satu query?
Tidak. CTE hanya ada untuk pernyataan tempat ia ditulis. Untuk memakai ulang query di beberapa pernyataan, buat view atau tabel temporary.