PostgreSQL WITH Sorguları: CTE, WITH RECURSIVE, SEARCH ve CYCLE

WITH içinde SELECT: sorguyu adımlara bölmek

Kılavuz, WITH içindeki SELECT ifadesinin temel değerinin karmaşık sorguları daha basit parçalara ayırmak olduğunu söyler. Her yardımcı ifade, ana sorgu boyunca var olan geçici bir tablo gibi düşünülebilir. Aşağıdaki örnekte önce bölge toplamlarını hesaplıyoruz, sonra toplam cironun dörtte birinden fazlasını yapan bölgeleri seçiyoruz ve ana sorguda yalnızca o bölgelerin ürün kırılımını gösteriyoruz:

CREATE TABLE siparis (id integer PRIMARY KEY, bolge text, urun text, adet integer, tutar numeric);
INSERT INTO siparis VALUES
    (1, 'Marmara', 'Defter', 10, 450), (2, 'Marmara', 'Kalem', 40, 500),
    (3, 'Ege', 'Defter', 4, 180), (4, 'Ege', 'Çay', 2, 420),
    (5, 'İç Anadolu', 'Kalem', 8, 100), (6, 'Marmara', 'Çay', 3, 630),
    (7, 'Karadeniz', 'Çay', 1, 210);

WITH bolge_satis AS (
    SELECT bolge, sum(tutar) AS toplam
    FROM siparis
    GROUP BY bolge
), en_iyi AS (
    SELECT bolge
    FROM bolge_satis
    WHERE toplam > (SELECT sum(toplam) / 4 FROM bolge_satis)
)
SELECT bolge, urun, sum(adet) AS adet, sum(tutar) AS tutar
FROM siparis
WHERE bolge IN (SELECT bolge FROM en_iyi)
GROUP BY bolge, urun
ORDER BY bolge, urun;

  bolge  |  urun  | adet | tutar
---------+--------+------+-------
 Marmara | Defter |   10 |   450
 Marmara | Kalem  |   40 |   500
 Marmara | Çay    |    3 |   630
(3 rows)

Toplam ciro 2490, dörtte biri 622.5; yalnızca Marmara (1580) bu eşiği geçiyor. Aynı sorguyu CTE olmadan iki seviye iç içe alt sorguyla da yazabilirdiniz, ama adımları adlandırmak okumayı kolaylaştırır. Kurallar şöyle: her CTE'ye şema öneki olmayan bir ad verilir; isteğe bağlı olarak ad(sutun1, sutun2) biçiminde sütun adları yazılabilir, yazılmazsa sütun adları alt sorgudan çıkarılır. RECURSIVE kullanılmadığında bir CTE yalnızca listede kendisinden önce tanımlanmış CTE'lere başvurabilir; yukarıda en_iyi, bolge_satis'i bu yüzden kullanabiliyor.

WITH RECURSIVE: kendine başvuran sorgu

RECURSIVE değiştiricisi, kılavuzun ifadesiyle WITH'i salt bir yazım kolaylığından, standart SQL'de başka türlü yapılamayan işleri yapan bir özelliğe dönüştürür: sorgu kendi çıktısına başvurabilir. Genel biçim her zaman aynıdır: özyinelemesiz bir başlangıç terimi, ardından UNION veya UNION ALL, ardından kendine başvuran özyinelemeli terim. Kılavuzun en basit örneği 1'den 100'e kadar sayıları toplar:

WITH RECURSIVE t(n) AS (
    VALUES (1)
  UNION ALL
    SELECT n + 1 FROM t WHERE n < 100
)
SELECT sum(n) FROM t;

 sum
------
 5050
(1 row)

Adı "özyinelemeli" olsa da kılavuz bu sorguların içeride yinelemeli (iteratif) değerlendirildiğini belirtir. Adımlar şunlardır:

• Başlangıç terimi değerlendirilir. UNION kullanılıyorsa tekrar eden satırlar atılır. Kalan satırlar hem sonuca hem de geçici bir çalışma tablosuna yazılır.
• Çalışma tablosu boş olmadığı sürece özyinelemeli terim, kendine başvurunun yerine çalışma tablosunun içeriği konarak değerlendirilir. UNION ile önceki sonuçların tekrarı olan satırlar da atılır. Kalanlar sonuca ve bir ara tabloya yazılır.
• Çalışma tablosunun içeriği ara tablonunkiyle değiştirilir, ara tablo boşaltılır ve adım tekrarlanır.

Örnekte çalışma tablosu her adımda tek satır içerir ve 1'den 100'e kadar değer alır; 100. adımda WHERE n < 100 hiçbir satır üretmediği için sorgu sona erer. RECURSIVE sözcüğünü unutursanız PostgreSQL ne yapmanız gerektiğini ipucuyla söyler:

WITH t(n) AS (
    VALUES (1)
  UNION ALL
    SELECT n + 1 FROM t WHERE n < 100
)
SELECT sum(n) FROM t;

ERROR:  relation "t" does not exist
LINE 4:     SELECT n + 1 FROM t WHERE n < 100
                              ^
DETAIL:  There is a WITH item named "t", but it cannot be referenced from this part of the query.
HINT:  Use WITH RECURSIVE, or re-order the WITH items to remove forward references.

WITH içinde birden fazla CTE varsa RECURSIVE yalnızca bir kez, hemen WITH'ten sonra yazılır ve hepsine uygulanır; özyineleme kullanmayanları etkilemez. RECURSIVE ayrıca CTE'lerin listede ileriye doğru başvurmasına da izin verir. Kendine başvuru yalnızca UNION'ın sağ tarafında olabilir ve sorgu başına bir kez yer alabilir; karşılıklı özyineleme desteklenmez.

Ağaç dolaşmak: kategori hiyerarşisi

Özyinelemeli sorguların en tipik kullanımı ağaç yapısındaki verilerdir. Her kategorinin üst kategorisini ust_id ile tutan bir tabloda, kök kategorilerden başlayıp her adımda bir alt seviyeye inerek bütün kategorileri derinlik ve tam yol bilgisiyle listeleyelim:

CREATE TABLE kategori (id integer PRIMARY KEY, ust_id integer REFERENCES kategori, ad text);
INSERT INTO kategori VALUES
    (1, NULL, 'Elektronik'), (2, 1, 'Bilgisayar'), (3, 2, 'Dizüstü'), (4, 2, 'Masaüstü'),
    (5, 1, 'Telefon'), (6, NULL, 'Kırtasiye'), (7, 6, 'Defter');

WITH RECURSIVE agac AS (
    SELECT id, ad, 1 AS derinlik, ad AS yol
    FROM kategori WHERE ust_id IS NULL
  UNION ALL
    SELECT k.id, k.ad, a.derinlik + 1, a.yol || ' > ' || k.ad
    FROM kategori k
    JOIN agac a ON k.ust_id = a.id
)
SELECT id, derinlik, yol FROM agac ORDER BY yol;

 id | derinlik |                yol
----+----------+------------------------------------
  1 |        1 | Elektronik
  2 |        2 | Elektronik > Bilgisayar
  3 |        3 | Elektronik > Bilgisayar > Dizüstü
  4 |        3 | Elektronik > Bilgisayar > Masaüstü
  5 |        2 | Elektronik > Telefon
  6 |        1 | Kırtasiye
  7 |        2 | Kırtasiye > Defter
(7 rows)

Burada yol metnine göre sıralamak sonucu derinlik öncelikli bir sıraya sokuyor. Kılavuz bu tür sıralamalar için bir uyarı yapar: sıralama sütunu yalnızca sonucu sonradan düzenlemenin bir yoludur, sorgunun satırları hangi sırayla ziyaret ettiğini kontrol etmez. Mevcut uygulama sonucu genişlik öncelikli sırayla üretse de bu bir gerçekleme ayrıntısıdır ve güvenilmemelidir.

SEARCH DEPTH FIRST ve BREADTH FIRST

Sıralama sütununu elle hesaplamak yerine yerleşik SEARCH yan tümcesini kullanabilirsiniz. SEARCH DEPTH FIRST BY id SET sira, CTE çıktısına ziyaret edilen anahtarların dizisini tutan bir sütun ekler; bu sütuna göre sıralamak derinlik öncelikli sırayı verir:

WITH RECURSIVE agac AS (
    SELECT id, ad, ust_id FROM kategori WHERE ust_id IS NULL
  UNION ALL
    SELECT k.id, k.ad, k.ust_id FROM kategori k JOIN agac a ON k.ust_id = a.id
) SEARCH DEPTH FIRST BY id SET sira
SELECT id, ad, sira FROM agac ORDER BY sira;

 id |     ad     |     sira
----+------------+---------------
  1 | Elektronik | {(1)}
  2 | Bilgisayar | {(1),(2)}
  3 | Dizüstü    | {(1),(2),(3)}
  4 | Masaüstü   | {(1),(2),(4)}
  5 | Telefon    | {(1),(5)}
  6 | Kırtasiye  | {(6)}
  7 | Defter     | {(6),(7)}
(7 rows)

BREADTH FIRST ise sütunu bir derinlik sayacı ve anahtardan oluşan bir değerle doldurur; önce bütün kökler, sonra bütün ikinci seviye, sonra üçüncü seviye gelir:

WITH RECURSIVE agac AS (
    SELECT id, ad, ust_id FROM kategori WHERE ust_id IS NULL
  UNION ALL
    SELECT k.id, k.ad, k.ust_id FROM kategori k JOIN agac a ON k.ust_id = a.id
) SEARCH BREADTH FIRST BY id SET sira
SELECT id, ad, sira FROM agac ORDER BY sira;

 id |     ad     | sira
----+------------+-------
  1 | Elektronik | (0,1)
  6 | Kırtasiye  | (0,6)
  2 | Bilgisayar | (1,2)
  5 | Telefon    | (1,5)
  7 | Defter     | (1,7)
  3 | Dizüstü    | (2,3)
  4 | Masaüstü   | (2,4)
(7 rows)

Kılavuza göre bu sözdizimi içeride elle yazılmış biçimlere benzer şekilde açılır. SEARCH ve CYCLE yalnızca özyinelemeli CTE'lerde geçerlidir ve sorgu, iç içe UNION içermeyen iki SELECT'in birleşimi olmalıdır.

Döngü tespiti: CYCLE

Özyinelemeli bir sorgunun güvenli olması için özyinelemeli kısmın eninde sonunda hiç satır döndürmemesi gerekir; aksi halde sorgu sonsuza kadar döner. Ağaçta bu sorun çıkmaz, ama şehirler arası yollar gibi bir grafikte Ankara, Konya, Antalya ve tekrar Ankara şeklinde bir döngü olabilir. Kılavuz, UNION ALL yerine UNION kullanmanın tamamen aynı satırları ayıklayarak bazen işe yaradığını, ama derinlik gibi her adımda değişen bir sütun varsa bunun yetmediğini açıklar. Çözüm, ziyaret edilen düğümleri izlemektir; CYCLE yan tümcesi bunu otomatik yapar:

CREATE TABLE yol (kaynak text, hedef text);
INSERT INTO yol VALUES ('Ankara', 'Konya'), ('Konya', 'Antalya'), ('Antalya', 'Ankara'), ('Konya', 'Karaman');

WITH RECURSIVE gezi(sehir, adim) AS (
    SELECT 'Ankara'::text, 0
  UNION ALL
    SELECT y.hedef, g.adim + 1
    FROM yol y JOIN gezi g ON y.kaynak = g.sehir
) CYCLE sehir SET dongu USING iz
SELECT sehir, adim, dongu, iz FROM gezi ORDER BY adim, sehir;

  sehir  | adim | dongu |                  iz
---------+------+-------+---------------------------------------
 Ankara  |    0 | f     | {(Ankara)}
 Konya   |    1 | f     | {(Ankara),(Konya)}
 Antalya |    2 | f     | {(Ankara),(Konya),(Antalya)}
 Karaman |    2 | f     | {(Ankara),(Konya),(Karaman)}
 Ankara  |    3 | t     | {(Ankara),(Konya),(Antalya),(Ankara)}
(5 rows)

CYCLE sehir, döngü kontrolünde hangi sütunun izleneceğini; SET dongu, döngü bulunduğunda true olacak işaret sütununu; USING iz ise ziyaret yolunu tutan sütunu adlandırır. Döngü tespit edilen satır sonuca yine eklenir, ama o yoldan ilerleme durur. İşaret değerleri varsayılan olarak true ve false'tur; SET dongu TO 'E' DEFAULT 'H' gibi başka sabitler de verilebilir. Kılavuzun ipucuna göre bir sorguda hem SEARCH hem CYCLE kullanılabilir; ancak derinlik öncelikli arama ile döngü tespiti aynı yolu iki kez hesaplayacağından, yalnızca CYCLE yazıp yol sütununa göre sıralamak daha verimlidir.

WITH RECURSIVE sonsuz(n) AS (
    SELECT 1
  UNION ALL
    SELECT n + 1 FROM sonsuz
)
SELECT n FROM sonsuz LIMIT 3;

 n
---
 1
 2
 3
(3 rows)

MATERIALIZED ve NOT MATERIALIZED

CTE'lerin önemli bir özelliği, ana sorgu onlara birden fazla kez başvursa bile normalde yalnızca bir kez hesaplanmalarıdır. Bu, pahalı bir hesaplamayı tekrarlamamak ya da yan etkili fonksiyonların birden çok çalışmasını önlemek için yararlıdır. Bedeli ise şudur: planlayıcı, birden çok kez başvurulan bir CTE'ye dış sorgudaki koşulları itemez ve CTE yazıldığı gibi bütünüyle hesaplanır.

Kılavuza göre bir CTE özyinelemesizse ve yan etkisizse (volatile fonksiyon içermeyen düz bir SELECT ise) ve ana sorgu ona yalnızca bir kez başvuruyorsa, varsayılan olarak ana sorguya katlanır ve iki seviye birlikte optimize edilir. Bu kararı MATERIALIZED ile ayrı hesaplamaya zorlayarak ya da NOT MATERIALIZED ile ana sorguya katlamaya zorlayarak değiştirebilirsiniz. Farkı EXPLAIN ile görmek kolaydır:

EXPLAIN (COSTS OFF)
WITH w AS (SELECT * FROM siparis)
SELECT * FROM w WHERE id = 3;

EXPLAIN (COSTS OFF)
WITH w AS MATERIALIZED (SELECT * FROM siparis)
SELECT * FROM w WHERE id = 3;

                QUERY PLAN
------------------------------------------
 Index Scan using siparis_pkey on siparis
   Index Cond: (id = 3)
(2 rows)

         QUERY PLAN
-----------------------------
 CTE Scan on w
   Filter: (id = 3)
   CTE w
     ->  Seq Scan on siparis
(4 rows)

İlk planda CTE tamamen kaybolmuş, id = 3 koşulu doğrudan tabloya itilmiş ve birincil anahtar indeksi kullanılmış. İkincisinde ise önce bütün tablo okunup geçici bir sonuca yazılıyor, koşul ondan sonra uygulanıyor. Kılavuz MATERIALIZED'in, planlayıcının kötü bir plan seçmesini engelleyen bir "optimizasyon çiti" olarak kullanılabileceğini ve PostgreSQL 12 öncesindeki sürümlerin bu katlamayı hiç yapmadığını, dolayısıyla eski sorguların bu davranışa güvenmiş olabileceğini not eder. Planın nasıl okunacağı EXPLAIN ANALYZE yazısında anlatılıyor.

NOT MATERIALIZED ters yönde işe yarar: bir CTE ana sorguda iki kez kullanılıyorsa normalde materyalize edilir; her kullanım sonucun küçük bir parçasına ihtiyaç duyuyorsa katlamaya zorlamak indekslerin kullanılmasını sağlayabilir. Ama CTE pahalı bir fonksiyon çağırıyorsa katlamak, o fonksiyonun her kullanım için ayrı ayrı çalışması anlamına gelir. NOT MATERIALIZED, özyinelemeli ya da yan etkili CTE'lerde yok sayılır.

Veri değiştiren WITH ifadeleri

PostgreSQL, standartta bulunmayan bir uzantı olarak WITH içinde INSERT, UPDATE, DELETE ve MERGE kullanmanıza izin verir. Kılavuzun klasik örneği satırları bir tablodan arşive taşımaktır: DELETE satırları siler ve RETURNING ile içeriklerini döndürür, ana sorgu da bu çıktıyı arşiv tablosuna ekler:

CREATE TABLE siparis_arsiv (LIKE siparis);

WITH tasinan AS (
    DELETE FROM siparis
    WHERE bolge = 'Karadeniz'
    RETURNING *
)
INSERT INTO siparis_arsiv
SELECT * FROM tasinan;

SELECT * FROM siparis_arsiv;

INSERT 0 1
 id |   bolge   | urun | adet | tutar
----+-----------+------+------+-------
  7 | Karadeniz | Çay  |    1 |   210
(1 row)

Ana sorgunun okuduğu geçici tablo, değiştirilen hedef tablo değil, RETURNING çıktısıdır. RETURNING yazmazsanız komut yine çalışır ama başvurulabilecek bir tablo oluşturmaz. Bu ifadeler, ana sorgunun çıktılarının ne kadarını okuduğundan bağımsız olarak tam olarak bir kez ve sonuna kadar çalıştırılır; WITH içindeki SELECT ise yalnızca ana sorgunun istediği kadar yürütülür.

Hepsi aynı anlık görüntüyü görür

Kılavuzun en önemli uyarılarından biri şudur: WITH içindeki alt ifadeler birbirleriyle ve ana sorguyla eşzamanlı çalışır ve hepsi aynı anlık görüntüyü (snapshot) kullanır. Bu yüzden birbirlerinin hedef tablolar üzerindeki etkisini göremezler; değişiklikleri aktarmanın tek yolu RETURNING çıktısıdır. Aynı sorguda tablodan doğrudan okumak eski değeri, CTE'den okumak yeni değeri verir:

WITH t AS (
    UPDATE siparis SET tutar = tutar * 2 WHERE id = 1
    RETURNING *
)
SELECT (SELECT tutar FROM siparis WHERE id = 1) AS tablodan,
       (SELECT tutar FROM t) AS returning_ten;

 tablodan | returning_ten
----------+---------------
      450 |           900
(1 row)

Aynı satırı tek komutta iki kez değiştirmeye çalışmak desteklenmez: değişikliklerden yalnızca biri gerçekleşir ve hangisinin olacağını güvenilir biçimde tahmin etmek kolay değildir. Kılavuz, ana komutun ya da kardeş bir alt ifadenin değiştirdiği satırlara dokunabilecek WITH alt ifadeleri yazmaktan kaçınmanızı önerir. Ayrıca veri değiştiren ifadeler yalnızca en üst seviyedeki komuta bağlı WITH içinde olabilir:

SELECT * FROM (
    WITH d AS (DELETE FROM siparis WHERE id = 5 RETURNING *) SELECT * FROM d
) x;

ERROR:  WITH clause containing a data-modifying statement must be at the top level
LINE 2:     WITH d AS (DELETE FROM siparis WHERE id = 5 RETURNING *)...
                 ^

Özyinelemeli veri değiştiren ifadeler de desteklenmez; ama özyinelemeli bir SELECT CTE'sinin sonucunu bir DELETE ya da UPDATE içinde kullanabilirsiniz. Kılavuzun örneği, bir ürünün bütün alt parçalarını özyinelemeli CTE ile bulup ardından ana DELETE ile silmektir. RETURNING'in ayrıntıları ve PostgreSQL 18 ile gelen old / new desteği DML yazısında, MERGE UPSERT yazısında anlatılıyor.

CTE ile alt sorgu karşılaştırması

Sık yapılan hatalar

• RECURSIVE yazmayı unutmak: Kendine başvuran CTE relation does not exist hatası verir.
• Durma koşulu olmayan özyineleme: Döngüsel veride sorgu bitmez. CYCLE kullanın ya da derinliğe bir üst sınır koyun.
• Sonuç sırasına güvenmek: Özyinelemeli sorgunun ürettiği sıra garanti değildir; SEARCH sütununa veya kendi yol sütununuza göre ORDER BY yazın.
• CTE'nin her zaman çit olduğunu sanmak: PostgreSQL 12'den beri tek başvurulan yan etkisiz CTE'ler katlanır; eski davranış gerekiyorsa MATERIALIZED yazın.
• Veri değiştiren CTE'nin etkisini aynı sorguda tablodan okumak: Bütün alt ifadeler aynı anlık görüntüyü görür; yeni değeri RETURNING üzerinden okuyun.

Sık Sorulan Sorular

CTE geçici tablo mu oluşturur?

Kavramsal olarak öyle davranır, ama fiziksel bir tablo oluşturmak zorunda değildir. Tek başvurulan yan etkisiz bir CTE ana sorguya katlanabilir; birden çok başvurulan ya da MATERIALIZED işaretli CTE ise bir kez hesaplanıp sonucu saklanır ve plan çıktısında CTE Scan olarak görünür.

UNION mı UNION ALL mı kullanmalıyım?

UNION, her adımda önceki sonuçların tekrarı olan satırları atar; bu, bazı döngüleri doğal olarak keser ama ek iş demektir. Satırlarda adım sayısı gibi değişen bir sütun varsa tekrar oluşmaz ve UNION döngüyü durduramaz. Çoğu ağaç sorgusunda UNION ALL ve gerektiğinde CYCLE daha açık bir tercihtir.

WITH RECURSIVE ile kaç seviye derine inebilirim?

Kılavuz sabit bir seviye sınırı tanımlamaz; sorgu, çalışma tablosu boşalana kadar devam eder. Bu yüzden durma koşulunu sizin sağlamanız gerekir: WHERE derinlik < 10 gibi bir koşul ya da CYCLE yan tümcesi.

WITH sorgusu UPDATE veya DELETE ile kullanılabilir mi?

Evet. WITH yan tümcesi SELECT'e olduğu gibi INSERT, UPDATE, DELETE ve MERGE komutlarına da bağlanabilir ve hepsinde ana komutun başvurabileceği geçici tablolar sağlar.

Marka adları sahiplerine aittir. Bu yazı bağımsız bir teknik rehberdir; adı geçen kuruluşlarla bayilik veya ortaklık ilişkisi bulunduğu anlamına gelmez.

İlgili içerikler: PostgreSQL öğrenme rehberi · JOIN türleri ve LATERAL · GROUP BY ve aggregate fonksiyonlar · INSERT, UPDATE, DELETE ve RETURNING · EXPLAIN ANALYZE ile plan okuma · Window function rehberi