Common table expression (CTE), ana komuttan önce WITH cümlesinde yazılan adlandırılmış bir alt sorgudur: WITH name AS (SELECT ...) SELECT ... FROM name. Ana sorgu CTE'yi bir tablo gibi okur; böylece iç içe alt sorgular yukarıdan aşağıya okunan adımlara dönüşür.
totals CTE'si her müşterinin siparişlerini toplar, ana sorgu da 100'ün üzerindeki müşterileri tutar: 300 ile cy ve 180 ile ana. Bir CTE yalnızca ait olduğu komut boyunca vardır.
Birkaç CTE'yi zincirlemek
CTE'leri virgülle ayır. Her biri kendinden önce tanımlanan CTE'leri okuyabilir.
average, totals'ı okur; ana sorgu ise ikisini de okur. Müşteri toplamları 180, 70 ve 300, ortalamaları da yaklaşık 183; bu yüzden yalnızca cy ortalamanın üzerinde.
WITH RECURSIVE ile ağaçta gezinmek
WITH RECURSIVE, bir CTE'nin kendine başvurmasını sağlar. UNION ALL ile birleşen iki kısmı vardır:
- başlangıç satırlarını döndüren çapa (anchor) sorgusu;
- bir sonraki seviyeyi bulmak için CTE'yi tabloyla birleştiren recursive sorgu.
PostgreSQL recursive kısmı bir önceki adımın ürettiği satırlar üzerinde çalıştırır ve bir adım hiç satır döndürmediğinde durur.
Her adım, bir önceki adımdaki satırlara bağlı çalışan kişileri bulur. Mia, Rosa > Omar > Ivo > Mia yoluyla 3. derinlikte çıkar. Tüm ağaç yerine tek bir yöneticinin altındaki herkesi listelemek için çapanın WHERE koşulunu id = 2 yap.
Yukarı doğru, bir çalışandan en tepeye gitmek için join koşulunu s.id = c.manager_id olarak değiştir.
Sonsuz bir recursive CTE'yi durdurmak
Bir recursive CTE ancak bir adım hiçbir şey döndürmediğinde biter. Döngü içeren verilerde (A, B'ye bağlı; B de A'ya bağlı) hiç bitmez. WHERE c.depth < 20 gibi bir derinlik sınırı ekle ya da ilk tekrar eden satırı işaretleyip onu izlemeyi bırakan CYCLE cümlesini (PostgreSQL 14+) kullan:
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;
Recursive CTE ile satır üretmek
Recursive kısmın bir tabloya ihtiyacı yoktur. Bu sorgu her satırın değerini geri besleyerek 1'den 5'e kadar sayar:
Düz sayı ya da tarih dizileri için generate_series daha kısadır.
Veri değiştiren CTE ile satır taşımak
Bir CTE, RETURNING cümlesi olan bir INSERT, UPDATE ya da DELETE olabilir; ana komut da döndürülen satırları okur. Bu komut eski siparişleri tek seferde bir arşiv tablosuna taşır:
1 ve 3 numaralı siparişler birlikte silinir ve arşive eklenir; bir hata olursa iki tablo da eski haliyle kalır. Komutun tüm parçaları aynı anlık görüntüyü (snapshot) görür: ana sorgu DELETE'in orders üzerindeki etkisini görmez, yalnızca RETURNING'in aktardığını görür (RETURNING sayfasına bak).
MATERIALIZED ile gömmeyi kontrol etmek
PostgreSQL 12'den beri, ana sorgunun bir kez başvurduğu yan etkisiz bir CTE ana sorguya gömülür. Planlayıcı onu dış sorguyla birlikte optimize eder, böylece CTE'nin dışında yazılan bir WHERE yine de indeks kullanabilir. İki ya da daha fazla kez başvurulan bir CTE bir kez hesaplanır ve sonucu yeniden kullanılır. İki davranışı da zorlayabilirsin:
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, CTE'yi tek başına bir kez hesaplar ve dıştaki WHERE koşulunu içine almaz. Gömülü plan daha yavaş çıktığında bunu kullan. NOT MATERIALIZED ise CTE birden fazla kez başvurulsa bile gömülmesini ister. Recursive ve veri değiştiren CTE'ler her zaman ayrı hesaplanır.
Sıkça Sorulan Sorular
PostgreSQL'de CTE alt sorgudan daha mı hızlı?
Genellikle ikisi de daha hızlı değildir. PostgreSQL 12'den beri bir kez kullanılan ve yan etkisi olmayan bir CTE ana sorguya gömülür (inline), bu yüzden eşdeğer alt sorguyla aynı planı alır. 12. sürümden önce her CTE ayrı hesaplanırdı ve bu, indeks kullanımını engelleyebiliyordu.
CTE'yi UPDATE ya da DELETE içinde kullanabilir miyim?
Evet. WITH, INSERT, UPDATE, DELETE ve MERGE komutlarının önüne gelebilir. CTE'nin kendisi de RETURNING içeren bir INSERT, UPDATE ya da DELETE olabilir; ana komut onun döndürdüğü satırları okur.
Recursive CTE'deki sonsuz döngüyü nasıl durdururum?
Recursive kısma sonunda hiç satır döndürmeyecek bir koşul ver, örneğin bir derinlik sınırı (WHERE depth < 10). Kendi üzerine dönebilen verilerde CYCLE cümlesi (PostgreSQL 14+) tekrar eden satırı işaretler ve orada durur.
Bir CTE birden fazla sorguda kullanılabilir mi?
Hayır. CTE yalnızca içinde yazıldığı komut boyunca vardır. Bir sorguyu komutlar arasında yeniden kullanmak için bir görünüm (view) ya da geçici tablo oluştur.