PostgreSQL'de Function ve Procedure: SQL ve PL/pgSQL, RETURNS TABLE, Volatility, SECURITY DEFINER ve CALL ile Transaction Kontrolü

Sürüm notu: Bu yazıdaki örnekler PostgreSQL 18.7 (resmî postgres:18 Docker image'ı) üzerinde, Bookstore şeması yüklendikten hemen sonra çalıştırılmıştır; .NET örneği .NET 10, Npgsql 10.0.3 ve Dapper 2.1.89 ile derlenip denenmiştir.

Önce Tahmin Et

Sitedeki serilerin ortak örnek uygulaması Bookstore (bir online kitapçı), oturumdaki müşteriyi app.customer_id ayarında tutuyor; 14. yazıdaki RLS (Row-Level Security; satır bazında erişim kuralları) policy'si de bu ayarı okuyordu. Ayşe ayarı okuyan küçük bir function yazmış, Emre aynısını bir kelime farkla yazmış. İkisi de aynı değeri döndürüyor:

CREATE FUNCTION app_customer_id() RETURNS int
LANGUAGE plpgsql
AS $$ BEGIN RETURN current_setting('app.customer_id')::int; END $$;
CREATE FUNCTION app_customer_id_stable() RETURNS int
LANGUAGE plpgsql STABLE
AS $$ BEGIN RETURN current_setting('app.customer_id')::int; END $$;
SET app.customer_id = '2';
EXPLAIN (ANALYZE, BUFFERS, COSTS OFF, TIMING OFF)
SELECT count(*) FROM orders WHERE customer_id = app_customer_id();
EXPLAIN (ANALYZE, BUFFERS, COSTS OFF, TIMING OFF)
SELECT count(*) FROM orders WHERE customer_id = app_customer_id_stable();

orders'ta customer_id üzerinde index var. İki sorgu da bu index'i kullanır mı?

                       QUERY PLAN
---------------------------------------------------------
 Aggregate (actual rows=1.00 loops=1)
   Buffers: shared hit=21 read=10310 written=1
   ->  Seq Scan on orders (actual rows=17359.00 loops=1)
         Filter: (customer_id = app_customer_id())
         Rows Removed by Filter: 982641
         Buffers: shared hit=21 read=10310 written=1
 Planning:
   Buffers: shared hit=68 read=3 dirtied=1
 Planning Time: 0.567 ms
 Execution Time: 235.939 ms

                                         QUERY PLAN
--------------------------------------------------------------------------------------------
 Aggregate (actual rows=1.00 loops=1)
   Buffers: shared hit=18 read=1
   ->  Index Only Scan using ix_orders_customer_id on orders (actual rows=17359.00 loops=1)
         Index Cond: (customer_id = app_customer_id_stable())
         Heap Fetches: 0
         Index Searches: 1
         Buffers: shared hit=18 read=1
 Planning:
   Buffers: shared hit=6
 Planning Time: 0.130 ms
 Execution Time: 0.752 ms

Kullanmıyor. İşaretsiz function VOLATILE sayılıyor: PostgreSQL, sonucunun her çağrıda değişebileceğini varsayıyor, bu yüzden değeri bir kez hesaplayıp index'te aratamıyor. Bir milyon satırın her biri için function'ı çağırdı, 10.331 buffer okudu, 236 ms sürdü. STABLE işaretli kopya "aynı sorgu içinde aynı girdiye aynı sonucu veririm" diyor; planlayıcı değeri bir kez alıp index'e gidiyor: 19 buffer, 0,75 ms. Tek kelimenin farkı 300 kat. Bu yazıda PostgreSQL'de function ve procedure yazmayı görüyoruz: LANGUAGE sql ile PostgreSQL'in procedural dili (PL/pgSQL; değişken, koşul ve döngü içeren, sunucuda çalışan dil) arasındaki fark, volatility, 18. yazıda söz verdiğimiz ISBN (kitapların uluslararası standart numarası) kontrol hanesini bir CHECK constraint'e taşımak, stok rezervasyonu, CALL ile transaction kontrolü, SECURITY DEFINER ve .NET'ten çağırmak.

LANGUAGE sql: Inline Edilen Function'lar, RETURNS TABLE ve OUT Parametreleri

Aynı function'ı LANGUAGE sql ile ve hiçbir işaret koymadan yazınca sonuç değişiyor:

CREATE FUNCTION app_customer_id_sql() RETURNS int
LANGUAGE sql
AS $$ SELECT current_setting('app.customer_id')::int $$;
EXPLAIN (COSTS OFF)
SELECT count(*) FROM orders WHERE customer_id = app_customer_id_sql();

                                       QUERY PLAN
-----------------------------------------------------------------------------------------
 Aggregate
   ->  Index Only Scan using ix_orders_customer_id on orders
         Index Cond: (customer_id = (current_setting('app.customer_id'::text))::integer)

Plan'da function adı yok; gövdesi sorgunun içine açılmış (inline edilmiş). Tek bir SELECT ifadesinden oluşan basit bir SQL function'ı planlayıcı makro gibi yerine koyuyor, sonra ifadenin kendi volatility'sine bakıyor: current_setting STABLE olduğu için index kullanılabildi. Inline edilme şartları dar: gövde tek bir ifade olmalı, subquery, FROM, aggregate içermemeli, SECURITY DEFINER ya da SET seçeneği olmamalı, function'a verilen işaret gövdeden daha "güvenli" olmamalı. Satır döndüren SQL function'ları da (aşağıda) sorgunun içine açılabiliyor. PL/pgSQL function'ları hiçbir zaman inline edilmiyor; planlayıcı için onlar içini göremediği bir kutu.

Satır döndüren function, SQL Server'daki table-valued function'ın karşılığı. Bir müşterinin son siparişleri için:

CREATE FUNCTION customer_recent_orders(p_customer_id int, p_limit int DEFAULT 5)
RETURNS TABLE (order_number varchar, status text, created_at timestamptz, total_amount numeric)
LANGUAGE sql STABLE
AS $$
    SELECT o.order_number, o.status, o.created_at, o.total_amount
    FROM orders AS o
    WHERE o.customer_id = p_customer_id
    ORDER BY o.created_at DESC
    LIMIT p_limit
$$;
SELECT * FROM customer_recent_orders(2, p_limit => 3);
SELECT c.customer_id, c.first_name, r.order_number, r.total_amount
FROM customers AS c
CROSS JOIN LATERAL customer_recent_orders(c.customer_id, 1) AS r
WHERE c.customer_id IN (2, 3);

 order_number | status  |       created_at       | total_amount
--------------+---------+------------------------+--------------
 BK-00999989  | Pending | 2026-10-20 20:56:00+00 |        67.36
 BK-00999983  | Pending | 2026-10-20 20:44:00+00 |        56.75
 BK-00999934  | Pending | 2026-10-20 19:06:00+00 |        61.89

 customer_id | first_name | order_number | total_amount
-------------+------------+--------------+--------------
           2 | Linus      | BK-00999989  |        67.36
           3 | Margaret   | BK-00999864  |        39.37

RETURNS TABLE, RETURNS SETOF record ile OUT parametrelerinin kısa yazımı; aynı işi var olan bir tablonun satır tipiyle RETURNS SETOF orders da yapıyor. DEFAULT verilen parametre çağrıda atlanabiliyor, p_limit => 3 ile isimle verilebiliyor. LATERAL, function'ı her müşteri satırı için çağırıyor; SQL Server'daki CROSS APPLY. Parametre adlarına p_ öneki koymak alışkanlık değil gereklilik: RETURNS TABLE'daki kolon adları function içinde değişken sayılıyor, tablo kolonlarıyla çakışınca aşağıda göreceğimiz hatayı veriyor. Tek satır, birden çok değer için OUT parametreleri:

CREATE FUNCTION book_stock(p_book_id int, OUT on_hand int, OUT available int)
LANGUAGE sql STABLE
AS $$
    SELECT quantity_on_hand, quantity_on_hand - quantity_reserved FROM stock_items WHERE book_id = p_book_id
$$;
CREATE FUNCTION book_available(p_book_id int) RETURNS int
LANGUAGE sql STABLE
RETURN (SELECT quantity_on_hand - quantity_reserved FROM stock_items WHERE book_id = p_book_id);
SELECT * FROM book_stock(1);
BEGIN;
ALTER TABLE stock_items RENAME COLUMN quantity_reserved TO reserved;
SELECT book_available(1);
SELECT * FROM book_stock(1);
ROLLBACK;
DROP TABLE stock_items;

 on_hand | available
---------+-----------
      36 |        36

 book_available
----------------
             36

ERROR:  column "quantity_reserved" does not exist
LINE 2:     SELECT quantity_on_hand, quantity_on_hand - quantity_res...
                                                        ^
QUERY:
    SELECT quantity_on_hand, quantity_on_hand - quantity_reserved FROM stock_items WHERE book_id = p_book_id

CONTEXT:  SQL function "book_stock" statement 1

ERROR:  cannot drop table stock_items because other objects depend on it
DETAIL:  function book_available(integer) depends on table stock_items
HINT:  Use DROP ... CASCADE to drop the dependent objects too.

İki function'ın gövdesi farklı biçimde saklanıyor. $$ ... $$ içindeki gövde bir string: PostgreSQL onu çağrı anında parse ediyor, kolon adı değişince ilk çağrıda hata veriyor. PostgreSQL 14 ile gelen SQL standardı biçimi (RETURN ... ya da BEGIN ATOMIC ... END) ise oluşturulurken parse ediliyor ve ağaç olarak saklanıyor: kolonun adı değişince function yeni adı izliyor, tabloyu silmek isteyince de 21. yazıdaki view'lar gibi bağımlılık hatası çıkıyor. Yalnızca SQL ile yazılabilen küçük function'lar için bu biçim daha güvenli. PL/pgSQL gövdesi her zaman string.

Bir not: PostgreSQL'de function adı tek başına kimlik değil, parametre tipleriyle birlikte kimlik (overloading). DROP FUNCTION, ALTER FUNCTION ve GRANT yazarken imzayı, örneğin line_total(int, numeric), yazman gerekiyor; aynı adla farklı tipte ikinci bir function oluşturmak ise hata vermiyor, sessizce yeni bir function açıyor.

Volatility ve PARALLEL SAFE: Planlayıcıya Verilen Söz

Her function üç kategoriden birinde ve işaret koymazsan en kötüsünde, VOLATILE'da:

Söz, PostgreSQL tarafından kontrol edilmiyor; yalnızca index tanımında ve generated column'da IMMUTABLE şartı aranıyor. 19. yazıda date_trunc'ın iki argümanlı hâlinin index'e konamadığını görmüştük. Kendi function'ını yanlış işaretlersen ne olduğunu, sipariş tarihini gün olarak döndüren bir function ile gördük:

CREATE FUNCTION order_day(p_created_at timestamptz) RETURNS date
LANGUAGE sql
RETURN p_created_at::date;
CREATE INDEX ix_orders_order_day ON orders (order_day(created_at));
CREATE OR REPLACE FUNCTION order_day(p_created_at timestamptz) RETURNS date
LANGUAGE sql IMMUTABLE
RETURN p_created_at::date;
CREATE INDEX ix_orders_order_day ON orders (order_day(created_at));
SET TimeZone = 'Europe/Istanbul';
SELECT count(*), min(created_at), max(created_at) FROM orders WHERE order_day(created_at) = '2026-10-01';
SET enable_indexscan = off;
SET enable_bitmapscan = off;
SELECT count(*), min(created_at), max(created_at) FROM orders WHERE order_day(created_at) = '2026-10-01';

ERROR:  functions in index expression must be marked IMMUTABLE

CREATE INDEX

 count |          min           |          max
-------+------------------------+------------------------
   720 | 2026-10-01 03:00:00+03 | 2026-10-02 02:58:00+03

 count |          min           |          max
-------+------------------------+------------------------
   720 | 2026-10-01 00:00:00+03 | 2026-10-01 23:58:00+03

timestamptz'yi date'e çevirmek oturumun TimeZone ayarına bağlı, yani bu function aslında STABLE. IMMUTABLE yazınca index oluştu, ama index UTC (eşgüdümlü evrensel saat) ile çalışan oturumda hesaplanmış günleri tutuyor. İstanbul saatiyle çalışan oturumda aynı sorgu index'le UTC gününü (İstanbul'a göre 03:00'ten ertesi gün 02:58'e), index kapatılınca İstanbul gününü döndürdü. Hata yok, uyarı yok; plan değişince cevap değişiyor. Doğru yol 19'daki gibi saat dilimini argüman yapmak: (p_created_at AT TIME ZONE 'Europe/Istanbul')::date gerçekten IMMUTABLE.

Volatility'nin yanında bir söz daha var: paralel güvenlik. Function'lar varsayılan olarak PARALLEL UNSAFE ve sorguda böyle bir function varsa planlayıcı paralel plan hiç düşünmüyor. Satır toplamını üç ve üzeri adette %5 indirimle hesaplayan bir function ile 2,5 milyon satırlık order_items'ı topladık:

CREATE FUNCTION line_total(p_quantity int, p_unit_price numeric) RETURNS numeric
LANGUAGE plpgsql IMMUTABLE
AS $$
BEGIN
    IF p_quantity >= 3 THEN
        RETURN round(p_quantity * p_unit_price * 0.95, 2);
    END IF;
    RETURN p_quantity * p_unit_price;
END
$$;
EXPLAIN (COSTS OFF) SELECT sum(line_total(quantity, unit_price)) FROM order_items;
SELECT sum(line_total(quantity, unit_price)) FROM order_items;
ALTER FUNCTION line_total(int, numeric) PARALLEL SAFE;
EXPLAIN (COSTS OFF) SELECT sum(line_total(quantity, unit_price)) FROM order_items;
SELECT sum(line_total(quantity, unit_price)) FROM order_items;

          QUERY PLAN
-------------------------------
 Aggregate
   ->  Seq Scan on order_items

     sum
-------------
 78426823.79
Time: 665.607 ms

                     QUERY PLAN
----------------------------------------------------
 Finalize Aggregate
   ->  Gather
         Workers Planned: 2
         ->  Partial Aggregate
               ->  Parallel Seq Scan on order_items

     sum
-------------
 78426823.79
Time: 224.062 ms

PARALLEL SAFE ile sorgu iki worker'a bölündü ve üç kat hızlandı. PARALLEL SAFE demek, function'ın veri yazmadığı, sequence ilerletmediği, oturum durumunu değiştirmediği anlamına geliyor; bu doğru değilse paralel sorguda hata ya da yanlış sonuç alırsın. RESTRICTED ara seçenek: paralel planda yalnızca leader process'te çalışır. Kural: hesaplama yapan saf function'ları IMMUTABLE PARALLEL SAFE, tablo okuyanları STABLE PARALLEL SAFE işaretle, veri değiştirenleri varsayılanda bırak.

PL/pgSQL: ISBN Kontrol Hanesi ve CHECK Constraint

18. yazıda ISBN-13 kontrol hanesini generate_series ve CASE ile tek sorguda hesaplamıştık. Aynı kural PL/pgSQL'de okunur bir function oluyor:

CREATE FUNCTION isbn13_is_valid(p_isbn text) RETURNS boolean
LANGUAGE plpgsql IMMUTABLE STRICT PARALLEL SAFE
AS $$
DECLARE
    v_sum int := 0;
BEGIN
    IF p_isbn !~ '^97[89][0-9]{10}$' THEN
        RETURN false;
    END IF;
    FOR i IN 1..13 LOOP
        v_sum := v_sum + substr(p_isbn, i, 1)::int * CASE WHEN i % 2 = 0 THEN 3 ELSE 1 END;
    END LOOP;
    RETURN v_sum % 10 = 0;
END
$$;
SELECT isbn, isbn13_is_valid(isbn) AS valid
FROM (VALUES ('9780441172719'), ('9780441172718'), ('978044117271X'), (NULL)) AS v(isbn);
SELECT count(*) FILTER (WHERE isbn13_is_valid(isbn)) AS valid, count(*) AS total FROM books;

     isbn      | valid
---------------+--------
 9780441172719 | t
 9780441172718 | f
 978044117271X | f
 (null)        | (null)

 valid | total
-------+-------
  4000 | 40000
Time: 40.550 ms

PL/pgSQL bloğunun iskeleti DECLARE (değişkenler, ilk değerleriyle) ve BEGIN ... END. FOR i IN 1..13 LOOP sayaçlı döngü; i'yi ayrıca tanımlamak gerekmiyor. Biçim kontrolü IF ile önce yapıldığı için "X" içeren değerde ::int dönüşümüne hiç gelinmiyor. STRICT (RETURNS NULL ON NULL INPUT), NULL argümanda function'ı hiç çalıştırmadan NULL döndürüyor. 40.000 kitabın kontrolü 41 ms sürdü ve 18'deki sonuçla aynı: 4.000 geçerli. Şimdi kuralı tabloya bağlayalım:

ALTER TABLE books ADD CONSTRAINT ck_books_isbn13 CHECK (isbn13_is_valid(isbn));
ALTER TABLE books ADD CONSTRAINT ck_books_isbn13 CHECK (isbn13_is_valid(isbn)) NOT VALID;
INSERT INTO books (isbn, title, author_id, category, price, published_year)
VALUES ('9780441172718', 'Dune #40001', 1, 'SciFi', 24.90, 1965);
UPDATE books SET price = price + 1 WHERE book_id = 1;
UPDATE books SET price = price + 1 WHERE book_id = 2;
ALTER TABLE books VALIDATE CONSTRAINT ck_books_isbn13;

ERROR:  check constraint "ck_books_isbn13" of relation "books" is violated by some row

ALTER TABLE

ERROR:  new row for relation "books" violates check constraint "ck_books_isbn13"
DETAIL:  Failing row contains (40001, 9780441172718, Dune #40001, 1, SciFi, 24.90, 1965).

ERROR:  new row for relation "books" violates check constraint "ck_books_isbn13"
DETAIL:  Failing row contains (1, 9780000000001, Emma #1, 1818, Classic, 33.77, 1885).

UPDATE 1

ERROR:  check constraint "ck_books_isbn13" of relation "books" is violated by some row

Tablodaki 36.000 geçersiz ISBN yüzünden constraint doğrudan eklenemedi. NOT VALID ile eklenince var olan satırlar kontrol edilmedi, yeni satırlar ediliyor: geçersiz numaralı yeni kitap reddedildi. Dikkat edilecek yer üçüncü komut: ISBN'e dokunmayan bir fiyat güncellemesi de geçersiz ISBN'li eski satırda (kitap 1) hata verdi, çünkü UPDATE yeni bir satır versiyonu yazıyor ve kontrol ona uygulanıyor. Geçerli ISBN'li kitap 2 sorunsuz güncellendi. Yani NOT VALID eski veriyi serbest bırakmıyor, yalnızca eklenme anındaki toplu kontrolü erteliyor. Gerçek katalogda sıra şu: constraint'i NOT VALID ekle, veriyi düzelt, sonra VALIDATE CONSTRAINT çalıştır; bu adım tabloyu tararken yazmaları engellemeyen bir lock alıyor.

Bir uyarı: PostgreSQL bir CHECK'in kullandığı function'ın gerçekten IMMUTABLE olup olmadığına bakmıyor ve function'ı CREATE OR REPLACE ile değiştirdiğinde var olan satırları yeniden kontrol etmiyor. Kuralı değiştirirsen constraint'i düşürüp yeniden ekle. Function'ı başka tabloya bakan bir sorgu ile yazmak da (örneğin "yazar aktif mi?") bu yüzden yanlış; o tür kurallar foreign key ya da trigger işi.

Bookstore: reserve_stock ve Hata Yönetimi

04. yazıda stok rezervasyonunu tek kitap için koşullu bir UPDATE ile yapmıştık. Bir siparişin bütün kalemlerini tek çağrıda rezerve eden function'ın ilk denemesi:

CREATE FUNCTION reserve_stock(p_order_id int)
RETURNS TABLE (book_id int, quantity int, available int)
LANGUAGE plpgsql
AS $$
DECLARE
    v_item record;
BEGIN
    FOR v_item IN
        SELECT book_id, sum(quantity)::int AS quantity
        FROM order_items
        WHERE order_id = p_order_id
        GROUP BY book_id
    LOOP
        RAISE NOTICE 'book %', v_item.book_id;
    END LOOP;
END
$$;
SELECT * FROM reserve_stock(1000000);

CREATE FUNCTION

ERROR:  column reference "book_id" is ambiguous
LINE 1: SELECT book_id, sum(quantity)::int AS quantity
               ^
DETAIL:  It could refer to either a PL/pgSQL variable or a table column.
QUERY:  SELECT book_id, sum(quantity)::int AS quantity
        FROM order_items
        WHERE order_id = p_order_id
        GROUP BY book_id
CONTEXT:  PL/pgSQL function reserve_stock(integer) line 5 at FOR over SELECT rows

İki ders var. CREATE FUNCTION başarılı oldu: PL/pgSQL gövdeyi oluşturulurken yalnızca syntax açısından kontrol ediyor, içindeki SQL'i ilk çalıştığında parse edip planlıyor. Hata production'da, o satıra ilk gelindiğinde çıkıyor; bu yüzden her function'ın en az bir testi olmalı (plpgsql_check extension'ı bu tür hataları çalıştırmadan bulmaya yardım ediyor). İkincisi, RETURNS TABLE'daki book_id function içinde bir değişken ve tablonun book_id kolonuyla çakışıyor. Çözüm kolonları tablo alias'ıyla yazmak:

CREATE OR REPLACE FUNCTION reserve_stock(p_order_id int)
RETURNS TABLE (book_id int, quantity int, available int)
LANGUAGE plpgsql
AS $$
DECLARE
    v_status text;
    v_item   record;
BEGIN
    SELECT o.status INTO v_status FROM orders AS o WHERE o.order_id = p_order_id FOR UPDATE;
    IF NOT FOUND THEN
        RAISE EXCEPTION 'order % not found', p_order_id USING ERRCODE = 'no_data_found';
    ELSIF v_status <> 'Pending' THEN
        RAISE EXCEPTION 'order % is %, not Pending', p_order_id, v_status USING ERRCODE = 'BK002';
    END IF;

    FOR v_item IN
        SELECT oi.book_id, sum(oi.quantity)::int AS quantity
        FROM order_items AS oi
        WHERE oi.order_id = p_order_id
        GROUP BY oi.book_id
        ORDER BY oi.book_id
    LOOP
        UPDATE stock_items AS s
        SET quantity_reserved = s.quantity_reserved + v_item.quantity,
            updated_at = now()
        WHERE s.book_id = v_item.book_id
          AND s.quantity_on_hand - s.quantity_reserved >= v_item.quantity
        RETURNING s.book_id, v_item.quantity, s.quantity_on_hand - s.quantity_reserved
        INTO book_id, quantity, available;

        IF NOT FOUND THEN
            RAISE EXCEPTION 'not enough stock for book % (order %)', v_item.book_id, p_order_id
                USING ERRCODE = 'BK001',
                      DETAIL = format('requested %s', v_item.quantity),
                      HINT = 'The order stays Pending; nothing was reserved.';
        END IF;
        RETURN NEXT;
    END LOOP;

    UPDATE orders AS o SET status = 'PaymentPending' WHERE o.order_id = p_order_id;
END
$$;
SELECT * FROM reserve_stock(1000000);
SELECT * FROM reserve_stock(1000000);
SELECT * FROM reserve_stock(999893);
SELECT book_id, quantity_on_hand, quantity_reserved FROM stock_items WHERE book_id IN (2332, 2956, 5809) ORDER BY book_id;

 book_id | quantity | available
---------+----------+-----------
    1583 |        1 |       108
    7800 |        1 |        42

ERROR:  order 1000000 is PaymentPending, not Pending
CONTEXT:  PL/pgSQL function reserve_stock(integer) line 10 at RAISE

ERROR:  not enough stock for book 5809 (order 999893)
DETAIL:  requested 3
HINT:  The order stays Pending; nothing was reserved.
CONTEXT:  PL/pgSQL function reserve_stock(integer) line 29 at RAISE

 book_id | quantity_on_hand | quantity_reserved
---------+------------------+-------------------
    2332 |              187 |                 0
    2956 |              107 |                 0
    5809 |                0 |                 0

Function'ı adım adım okuyalım. SELECT ... INTO ... FOR UPDATE siparişi okuyup satırını kilitliyor; aynı siparişi aynı anda iki istek rezerve edemiyor. FOUND, son SQL komutunun satır bulup bulmadığını tutan özel değişken. FOR v_item IN query LOOP sorgunun satırlarını tek tek dolaşıyor. Kalemleri book_id sırasıyla işlemek bilinçli: iki sipariş aynı kitapları farklı sırayla kilitlerse deadlock oluşur (09. yazı), herkes aynı sırayla kilitleyince oluşmaz. Koşullu UPDATE ... RETURNING ... INTO stoğu kontrol edip artırıyor ve sonucu doğrudan RETURNS TABLE değişkenlerine yazıyor; RETURN NEXT o değerleri sonuç kümesine bir satır olarak ekliyor. Tek bir sorgunun bütün sonucunu döndürmek için RETURN QUERY SELECT ... da var.

999893 numaralı siparişin üç kitabından ilk ikisinin stoğu vardı, üçüncüsünün yoktu. Function ilk iki kitabı rezerve etti, üçüncüde RAISE EXCEPTION ile durdu. Stok tablosunda ilk iki kitabın quantity_reserved'ı yine 0: hata, function'ı çağıran statement'ı ve onun bütün değişikliklerini geri aldı. Uygulamanın ayırt edebilmesi için hataya kendi SQLSTATE kodumuzu (hatanın beş karakterlik standart kodu) verdik: stok yok BK001, yanlış durum BK002. Kendi kodunu standart sınıflarla çakışmayacak bir önekle seç; vermezsen RAISE EXCEPTION P0001 kullanıyor ve uygulama mesaj metnini ayrıştırmak zorunda kalıyor. RAISE NOTICE ise hata değil, istemciye giden bir mesaj; psql ekrana yazıyor, Npgsql bir event olarak veriyor.

DO Bloğu ve EXCEPTION

18. yazıda kullandığımız DO, adsız ve bir kez çalışan PL/pgSQL bloğu: değer döndüremiyor, parametre almıyor, ama tek seferlik bakım işleri için function oluşturmaya gerek bırakmıyor. 20 Ekim'deki bütün bekleyen siparişleri rezerve edip stoğu yetmeyenleri raporlayan blok:

DO $$
DECLARE
    v_order_id int;
    v_ok       int := 0;
    v_failed   int := 0;
    v_message  text;
    v_detail   text;
BEGIN
    FOR v_order_id IN
        SELECT order_id FROM orders
        WHERE status = 'Pending' AND created_at >= '2026-10-20'
        ORDER BY order_id
    LOOP
        BEGIN
            PERFORM reserve_stock(v_order_id);
            v_ok := v_ok + 1;
        EXCEPTION
            WHEN SQLSTATE 'BK001' THEN
                GET STACKED DIAGNOSTICS v_message = MESSAGE_TEXT, v_detail = PG_EXCEPTION_DETAIL;
                v_failed := v_failed + 1;
                IF v_failed <= 3 THEN
                    RAISE NOTICE '%, %', v_message, v_detail;
                END IF;
        END;
    END LOOP;
    RAISE NOTICE 'reserved: %, not enough stock: %', v_ok, v_failed;
END
$$;
SELECT status, count(*) FROM orders WHERE created_at >= '2026-10-20' GROUP BY status ORDER BY status;

NOTICE:  not enough stock for book 2057 (order 999378), requested 1
NOTICE:  not enough stock for book 28303 (order 999439), requested 1
NOTICE:  not enough stock for book 2057 (order 999535), requested 1
NOTICE:  reserved: 604, not enough stock: 10
DO

     status     | count
----------------+-------
 Cancelled      |    25
 PaymentPending |   605
 Pending        |    10

604 sipariş rezerve edildi (biri, 1000000, zaten yukarıda edilmişti), 10 siparişte stok yetmedi ve bunlar Pending kaldı. EXCEPTION kısmı olan iç blok bir savepoint gibi çalışıyor: blok içinde hata olunca yalnızca o bloğun değişiklikleri geri alınıyor, dış döngü devam ediyor. GET STACKED DIAGNOSTICS yakalanan hatanın mesajını, detayını, SQLSTATE'ini ve hangi satırda oluştuğunu (PG_EXCEPTION_CONTEXT) okuyor. Yalnızca beklediğin kodu yakala: WHEN OTHERS her hatayı, deadlock'u ve iptal edilen sorguyu bile yutar. Bedeli de var: EXCEPTION kısmı olan her blok girişte bir subtransaction açıyor; dokümantasyon bunun belirgin biçimde pahalı olduğunu söylüyor. Bir milyon satırlık döngüde her satır için böyle bir blok, hem yavaş hem de subtransaction sayacını hızla tüketiyor.

Procedure ve CALL: Transaction Kontrolü

Function her zaman onu çağıran statement'ın transaction'ı içinde çalışıyor; COMMIT yapamıyor. PostgreSQL 11 ile gelen procedure ise CALL ile çağrılıyor ve kendi içinde transaction'ı bitirip yenisini başlatabiliyor. Gece çalışan bir iş için tam bu gerekiyor: teslim edilmiş eski kargo kayıtlarını archive schema'sına 10.000'lik parçalar hâlinde taşıyan, her parçadan sonra commit eden bir procedure:

CREATE SCHEMA archive;
CREATE TABLE archive.shipments (LIKE public.shipments, PRIMARY KEY (shipment_id));
CREATE PROCEDURE archive_shipments(p_delivered_before timestamptz, p_batch_size int DEFAULT 10000)
LANGUAGE plpgsql
AS $$
DECLARE
    v_moved int;
    v_total int := 0;
BEGIN
    LOOP
        WITH batch AS (
            SELECT shipment_id FROM public.shipments
            WHERE delivered_at < p_delivered_before
            ORDER BY shipment_id
            LIMIT p_batch_size
        ), moved AS (
            DELETE FROM public.shipments AS s
            USING batch AS b
            WHERE s.shipment_id = b.shipment_id
            RETURNING s.*
        )
        INSERT INTO archive.shipments SELECT * FROM moved;

        GET DIAGNOSTICS v_moved = ROW_COUNT;
        EXIT WHEN v_moved = 0;
        v_total := v_total + v_moved;
        COMMIT;
        RAISE NOTICE '% rows moved, % in total', v_moved, v_total;
    END LOOP;
END
$$;
CALL archive_shipments('2023-03-01');
BEGIN;
CALL archive_shipments('2023-04-01');
ROLLBACK;
CREATE FUNCTION commit_inside() RETURNS void LANGUAGE plpgsql AS $$ BEGIN COMMIT; END $$;
SELECT commit_inside();
SELECT (SELECT count(*) FROM shipments) AS live, (SELECT count(*) FROM archive.shipments) AS archived;

NOTICE:  10000 rows moved, 10000 in total
NOTICE:  10000 rows moved, 20000 in total
NOTICE:  10000 rows moved, 30000 in total
NOTICE:  7336 rows moved, 37336 in total
CALL
Time: 356.868 ms

ERROR:  invalid transaction termination
CONTEXT:  PL/pgSQL function archive_shipments(timestamp with time zone,integer) line 23 at COMMIT

ERROR:  invalid transaction termination
CONTEXT:  PL/pgSQL function commit_inside() line 1 at COMMIT

  live  | archived
--------+----------
 917419 |    37336

37.336 kayıt dört transaction'da taşındı. Her parça ayrı commit edildiği için lock'lar kısa tutuluyor, autovacuum silinen satırları iş bitmeden temizlemeye başlayabiliyor (08. yazı) ve iş ortada kesilirse taşınan parçalar kalıcı, kalanı bir sonraki gece devam ediyor. DELETE ... RETURNING'i INSERT'e bağlayan CTE (Common Table Expression; WITH ile adlandırılan ara sorgu) taşımayı tek statement'ta yapıyor: satır ya iki tabloda birden değişiyor ya hiç değişmiyor. Arşiv tablosunu LIKE ... INCLUDING ALL ile oluşturmadık; o seçenek identity'yi de kopyalıyor ve GENERATED ALWAYS kolona değer yazmak hata veriyor.

İki hata, procedure'ün sınırını gösteriyor. CALL, açık bir transaction bloğunun (BEGIN) içinden yapılınca procedure içindeki COMMIT reddediliyor: dışarıdaki transaction'ın sahibi çağıran taraf. Function içinde COMMIT ise hiçbir koşulda olmuyor. Procedure satır döndüremiyor; sonuç vermesi gerekiyorsa INOUT (PostgreSQL 14'ten beri OUT) parametre kullanılıyor. Bu işi pg_cron'a bağlamak 21. yazıdaki gibi tek satır: cron.schedule('archive-shipments', '30 2 * * *', $$CALL archive_shipments(now() - interval '3 years')$$).

SECURITY DEFINER ve search_path

Function varsayılan olarak onu çağıranın yetkisiyle çalışıyor (SECURITY INVOKER). SECURITY DEFINER ise function'ın owner'ının yetkisiyle çalışıyor; SQL Server'daki EXECUTE AS OWNER gibi. Bu, uygulama rolüne bir tabloya doğrudan yazma yetkisi vermeden, yalnızca kontrollü bir yoldan yazdırmayı sağlıyor. Depo sayımından sonra stok düzeltmeyi yalnızca gerekçesiyle ve kayıt altına alarak yapan bir function:

CREATE TABLE stock_adjustments
(
    stock_adjustment_id int GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    book_id     int         NOT NULL REFERENCES books (book_id),
    delta       int         NOT NULL,
    reason      text        NOT NULL,
    adjusted_by text        NOT NULL,
    adjusted_at timestamptz NOT NULL DEFAULT now()
);
REVOKE INSERT, UPDATE, DELETE ON stock_items, stock_adjustments FROM bookstore_app;
CREATE FUNCTION adjust_stock(p_book_id int, p_delta int, p_reason text) RETURNS int
LANGUAGE plpgsql
SECURITY DEFINER
SET search_path = public, pg_temp
AS $$
DECLARE
    v_on_hand int;
BEGIN
    IF p_reason IS NULL OR length(trim(p_reason)) = 0 THEN
        RAISE EXCEPTION 'a reason is required' USING ERRCODE = 'BK003';
    END IF;
    UPDATE stock_items
    SET quantity_on_hand = quantity_on_hand + p_delta, updated_at = now()
    WHERE book_id = p_book_id
    RETURNING quantity_on_hand INTO v_on_hand;
    INSERT INTO stock_adjustments (book_id, delta, reason, adjusted_by)
    VALUES (p_book_id, p_delta, p_reason, session_user);
    RETURN v_on_hand;
END
$$;
SELECT proacl IS NULL AS default_acl FROM pg_proc WHERE proname = 'adjust_stock';
REVOKE EXECUTE ON FUNCTION adjust_stock(int, int, text) FROM PUBLIC;
GRANT EXECUTE ON FUNCTION adjust_stock(int, int, text) TO bookstore_app;
SELECT proname, prosecdef, proconfig, proacl FROM pg_proc WHERE proname = 'adjust_stock';

 default_acl
-------------
 t

   proname    | prosecdef |            proconfig            |                               proacl
--------------+-----------+---------------------------------+---------------------------------------------------------------------
 adjust_stock | t         | {"search_path=public, pg_temp"} | {bookstore_owner=X/bookstore_owner,bookstore_app=X/bookstore_owner}

Aynı oturumu uygulama rolüyle açtık:

UPDATE stock_items SET quantity_on_hand = 500 WHERE book_id = 1;
SELECT adjust_stock(1, 5, 'warehouse count 2026-10-21');
SELECT adjust_stock(1, -2, '');
SELECT book_id, delta, reason, adjusted_by FROM stock_adjustments;

ERROR:  permission denied for table stock_items

 adjust_stock
--------------
           41

ERROR:  a reason is required
CONTEXT:  PL/pgSQL function adjust_stock(integer,integer,text) line 6 at RAISE

 book_id | delta |           reason           |  adjusted_by
---------+-------+----------------------------+---------------
       1 |     5 | warehouse count 2026-10-21 | bookstore_app

Uygulama rolü tabloyu doğrudan güncelleyemedi, function üzerinden gerekçeyle güncelleyebildi; gerekçesiz çağrı reddedildi. Function içinde current_user owner'ı gösteriyor, bu yüzden kayıtta çağıranı session_user ile tuttuk. Üç ayrıntı bu tür function'ların güvenliği için şart. Birincisi SET search_path: owner yetkisiyle çalışan kodun tablo ve function adlarını hangi schema'larda arayacağı, çağıranın oturum ayarına bırakılmamalı; dokümantasyonun önerisi, çağıranların yazabildiği schema'ları listeye koymamak ve pg_temp'i en sona yazmak (yazılmazsa geçici tablolar ilk sırada aranıyor). İkincisi yetki: yeni function'ların EXECUTE yetkisi varsayılan olarak PUBLIC'te (proacl'ın boş olması bu demek), yani veritabanına bağlanabilen herkes çağırabiliyor. REVOKE ... FROM PUBLIC ve hedefli bir GRANT ile kapat; dokümantasyon bunu function'ı oluşturduğun transaction içinde yapmanı öneriyor, migration'lar zaten öyle çalışıyor. Üçüncüsü kapsam: function yalnızca gereken işi yapsın, dinamik SQL ile çağıranın verdiği tablo adını çalıştırmasın.

reserve_stock da stock_items'a yazdığı için aynı yolu izledi: ALTER FUNCTION reserve_stock(int) SECURITY DEFINER SET search_path = public, pg_temp, ardından REVOKE ve GRANT. Rollerin ve yetkilerin genel modeli 14. yazıda.

Npgsql ve Dapper'dan Çağırmak; SQL Server ile Karşılaştırma

Satır döndüren function uygulama tarafında sıradan bir sorgu: SELECT ... FROM reserve_stock(@orderId). Procedure CALL ile çağrılıyor ve RAISE NOTICE mesajları bağlantının Notice event'iyle geliyor:

public sealed record ReservedLine(int BookId, int Quantity, int Available);

public static async Task ReserveAsync(NpgsqlDataSource dataSource, int orderId)
{
    DefaultTypeMap.MatchNamesWithUnderscores = true;
    await using NpgsqlConnection connection = await dataSource.OpenConnectionAsync();
    try
    {
        IEnumerable<ReservedLine> lines = await connection.QueryAsync<ReservedLine>(
            "SELECT book_id, quantity, available FROM reserve_stock(@orderId)", new { orderId });
        foreach (ReservedLine line in lines)
        {
            Console.WriteLine($"order {orderId}: book {line.BookId} x{line.Quantity}, available {line.Available}");
        }
    }
    catch (PostgresException ex) when (ex.SqlState is "BK001" or "BK002")
    {
        Console.WriteLine($"order {orderId}: {ex.SqlState} {ex.MessageText}");
        Console.WriteLine($"  detail: {ex.Detail ?? "-"}, hint: {ex.Hint ?? "-"}");
    }
}

public static async Task ArchiveAsync(NpgsqlDataSource dataSource, DateTime deliveredBefore)
{
    await using NpgsqlConnection connection = await dataSource.OpenConnectionAsync();
    connection.Notice += (_, e) => Console.WriteLine($"NOTICE: {e.Notice.MessageText}");

    await using NpgsqlCommand call = new("CALL archive_shipments(@deliveredBefore, @batchSize)", connection);
    call.Parameters.AddWithValue("deliveredBefore", deliveredBefore);
    call.Parameters.AddWithValue("batchSize", 5_000);
    await call.ExecuteNonQueryAsync();

    // CommandType.StoredProcedure: Npgsql 7+ function için de CALL gönderiyor
    await using NpgsqlCommand asProcedure = new("reserve_stock", connection)
    {
        CommandType = CommandType.StoredProcedure,
    };
    asProcedure.Parameters.AddWithValue("p_order_id", 999360);
    try
    {
        await asProcedure.ExecuteNonQueryAsync();
    }
    catch (PostgresException ex)
    {
        Console.WriteLine($"{ex.SqlState}: {ex.MessageText} | {ex.Hint}");
    }
}

dotnet run -- reserve 999360 999893 1000000 uygulama rolüyle, dotnet run -- archive gece işini çalıştıran owner rolüyle (2023-04-01'den önce teslim edilenler):

order 999360: book 4810 x2, available 180
order 999360: book 36521 x1, available 2
order 999893: BK001 not enough stock for book 5809 (order 999893)
  detail: Detail redacted as it may contain sensitive data. Specify 'Include Error Detail' in the connection string to include this information., hint: The order stays Pending; nothing was reserved.
order 1000000: BK002 order 1000000 is PaymentPending, not Pending
  detail: -, hint: -

NOTICE: 5000 rows moved, 5000 in total
NOTICE: 5000 rows moved, 10000 in total
NOTICE: 5000 rows moved, 15000 in total
NOTICE: 5000 rows moved, 20000 in total
NOTICE: 1425 rows moved, 21425 in total
42883: procedure reserve_stock(p_order_id => integer) does not exist | No procedure matches the given name and argument types. You might need to add explicit type casts.

Üç şey dikkat çekiyor. Kendi SQLSTATE kodlarımız PostgresException.SqlState'te geldi ve catch ... when ile ayırt edildi; mesaj metnini ayrıştırmaya gerek yok. DETAIL ise gizlendi: Npgsql, hata detayında hassas veri olabileceği için onu varsayılan olarak göstermiyor; connection string'e Include Error Detail=true eklenince aynı çağrı detail: requested 3 yazdı. Bunu geliştirme ortamında aç, production log'larına müşteri verisi taşıyabileceğini unutma. Son satır SQL Server'dan gelenlerin en sık takıldığı yer: CommandType.StoredProcedure, Npgsql 7.0'dan beri procedure çağırmak için CALL üretiyor, eskiden function'lar için SELECT üretiyordu. Function'ı bu şekilde çağırmak 42883 hatası veriyor. Eski davranışa Npgsql.EnableStoredProcedureCompatMode AppContext switch'i ile dönülebiliyor; ama düz SELECT ... FROM f(...) yazmak hem açık hem de Dapper ile doğrudan çalışıyor. Npgsql ve Dapper'ın genel kullanımı 11. yazıda.

SQL Server'ın stored procedure'üyle yan yana:

Plan tarafında da fark var: SQL Server procedure'ün planını sunucu genelindeki plan cache'te tutuyor; PL/pgSQL ise her statement'ın planını bağlantı başına, o bağlantıda ilk çalıştığında hazırlıyor ve aynı bağlantıda yeniden kullanıyor. Connection pool'da her fiziksel bağlantı bu hazırlığı bir kez yapıyor. SQL Server'daki inline table-valued function'ın karşılığı inline edilen LANGUAGE sql function'ı; multi-statement function'ın karşılığı ise planlayıcının içini göremediği PL/pgSQL.

Üretimde Dikkat

Function'lar şemanın parçası: Entity Framework Core (EF Core) migration'larında migrationBuilder.Sql(...) ile, ya da ayrı ve sürümlenen SQL dosyalarıyla yönetilmeli. CREATE OR REPLACE dönüş tipini ya da parametre adlarını değiştiremiyor; değişince DROP ve CREATE gerekiyor ve o arada yetkiler (GRANT) de yeniden verilmeli.

Function içindeki sorgular pg_stat_statements'ta görünsün istiyorsan pg_stat_statements.track = all gerekiyor; varsayılan top yalnızca istemcinin gönderdiği statement'ı sayıyor (07. yazı). track_functions = pl ile pg_stat_user_functions her function'ın çağrı sayısını ve toplam süresini tutuyor. Yavaş bir function'ın içindeki planı görmek için 07'deki auto_explain'in log_nested_statements ayarı var.

Uzun süren procedure'lerde her COMMIT'ten sonra yeni bir snapshot alınıyor; bu yüzden döngünün ortasında başka oturumların yaptığı değişiklikler görünüyor. Parçalı taşımada bu sorun değil, ama "işin başındaki durum" üzerinden hesap yapıyorsan değerleri döngüden önce bir değişkene al.

Sık Düşülen Tuzaklar

STABLE olması gereken function'ı işaretsiz bırakmak: "Önce Tahmin Et"teki gibi index kaybolur ve function her satırda çağrılır. Tersi daha kötü: STABLE bir function'ı IMMUTABLE işaretlemek sessizce yanlış sonuç verdirir.

SECURITY DEFINER'da search_path'i sabitlememek ve EXECUTE'u PUBLIC'ten almamak. İkisi de varsayılan olarak açık; ikisini de her SECURITY DEFINER function'da yaz.

RETURNS TABLE kolon adlarının tablo kolonlarıyla çakışması: hata oluşturma anında değil, ilk çağrıda çıkar. Kolonları alias'la yaz ya da çıktı adlarını farklı seç.

Her satır için EXCEPTION bloğu açmak ve WHEN OTHERS ile her hatayı yutmak: hem yavaş hem de gerçek sorunları (deadlock, iptal) gizler.

İş mantığını kontrolsüzce veritabanına taşımak. Stok rezervasyonu gibi, veriye yakın ve tek transaction'da tutarlı olması gereken bir kural için function iyi bir yer. İndirim kampanyası, bildirim, ödeme sağlayıcısıyla konuşmak gibi sık değişen ve test edilmesi gereken kurallar uygulama kodunda kalmalı: PL/pgSQL'in debugger'ı, unit test araçları ve deploy süreci C# kadar rahat değil, ve veritabanı yatay ölçeklenmesi en zor katman.

Küçük Bir Deneme

★☆☆ isbn13_is_valid'i LANGUAGE sql ve RETURN ile, 18. yazıdaki generate_series ifadesini kullanarak yeniden yaz. 40.000 kitap üzerinde iki sürümün süresini \timing ile karşılaştır; sonuçta inline edilme beklemeli misin? Gövdede subquery olduğunu hatırla.

★★☆ reserve_stock'un tersini yap: release_stock(p_order_id) iptal edilen bir siparişin rezervasyonlarını geri bıraksın ve siparişi Cancelled yapsın. Aynı siparişi iki kez iptal etmeye çalışınca ne olmalı? Kendi SQLSTATE kodunu ver ve .NET tarafında yakala.

★★★ archive_shipments'ı iki psql oturumuyla dene: A procedure'ü p_batch_size => 2000 ile çalıştırırken B, taşınacak kargolardan birinin satırını FOR UPDATE ile kilitleyip 10 saniye beklesin. A'nın davranışını pg_stat_activity'deki wait_event ile izle; FOR UPDATE SKIP LOCKED'i (09. yazı) batch CTE'sine eklemek neyi değiştirir?

Bir sonraki yazıda trigger'lara geçiyoruz: BEFORE ve AFTER, row ve statement seviyesi, transition table'lar, updated_at ve audit trigger'ı, 21. yazıda güncellenemeyen view'ı yazılabilir yapan INSTEAD OF trigger ve DDL (Data Definition Language; tablo ve index gibi yapıları değiştiren komutlar) komutlarını kaydeden event trigger.