PostgreSQL funksiyalari va PL/pgSQL: CREATE FUNCTION, PROCEDURE va xavfsizlik

Qisqacha: CREATE FUNCTION bazaning ichida nomli, qayta ishlatiladigan mantiq yaratadi. Oddiy hisob uchun — LANGUAGE sql. Shart, sikl va xatoni ushlash kerak bo'lsa — LANGUAGE plpgsql. Volatility belgisi (IMMUTABLE/STABLE/VOLATILE) — planner'ga bergan va'dangiz: noto'g'ri va'da indeksni o'chiradi yoki yolg'on natija beradi. PROCEDURE ichida COMMIT qilish mumkin, funksiyada — yo'q. SECURITY DEFINER funksiya egasining huquqi bilan ishlaydi — SET search_path siz u hujumchiga eshik.

Bu darsda

• LANGUAGE sql va LANGUAGE plpgsql funksiyalarini yozasiz, jadval qaytaradigan funksiya va nomli argumentlarni ishlatasiz.
• PL/pgSQL'da o'zgaruvchi, IF, sikl, RAISE va EXCEPTION bloklarini o'qiy va yoza olasiz.
• IMMUTABLE, STABLE, VOLATILE farqini EXPLAIN orqali ko'rsatasiz.
• PROCEDURE, CALL va DO blokini qachon ishlatishni bilasiz.
• SECURITY DEFINER funksiyani qanday "o'g'irlashini" ko'rib, himoyasini yozasiz.

Oldin bilishingiz kerak: View va materialized view, Denormalizatsiya va hisoblangan maydonlar (trigger funksiyasi bilan birinchi tanishuv), Tranzaksiya asoslari.

1. Nega bu kerak?

Oldingi darsda murakkab so'rovga nom berdik — view. View — "saqlangan SELECT". Lekin ba'zan bitta SELECT yetmaydi: argument olish, shartga qarab boshqa yo'ldan yurish, xatoni ushlab, tushunarli xabar qaytarish kerak bo'ladi.

Siz buni 26-qismda bir marta ko'rgansiz. izohlar_soni ni yangilaydigan trigger aslida funksiya edi — PL/pgSQL tilida yozilgan. U yerda "chuqur o'rganmaymiz" degandik. Endi vaqti keldi.

Hayotiy o'xshatish: oshxonadagi retsept daftari. Oshpaz har safar "guruchni yuv, sabzini to'g'ra, zirvakni tayyorla…" deb boshidan aytib o'tirmaydi. Retseptga nom beradi — "to'y oshi" — va xohlagan payt "to'y oshi, 50 kishilik" deydi. Funksiya — bazaning retsept daftari: nomi, argumenti va natijasi bor.

Lekin darhol ogohlantiramiz: mantiqni bazaga ko'chirish ikki tomonlama qilich. Foydasi: barcha ilovalar bir xil qoidani ishlatadi, ma'lumot tarmoq bo'ylab yurmaydi. Zarari: kod ikki joyda (TypeScript va SQL), test qilish qiyinroq, versiyalash migratsiya orqali. Darsning oxirida "qachon foydali, qachon xavfli" jadvalini beramiz.

Bu darsdagi barcha so'rovlarni biz ishga tushirdik: PostgreSQL 17.6, kanon holat 04 sxemasi. Kanon (IlmHamroh, 2026-10, PostgreSQL 18.6) xuddi shu so'rovlarda bir xil chiqish olgan. Hamma o'zgarish oxirida o'chiriladi — loyiha sxemasi o'zgarmaydi.

2. LANGUAGE sql — eng oddiy funksiya

2.1 Birinchi funksiya

Vazifa: har foydalanuvchining faol (o'chirilmagan) vazifalari sonini ko'rsatish. So'rovni har safar yozish o'rniga funksiya yaratamiz:

CREATE FUNCTION faol_vazifalar_soni(p_egasi bigint)
RETURNS bigint
LANGUAGE sql STABLE
RETURN (SELECT count(*) FROM vazifalar
        WHERE egasi_id = p_egasi AND ochirilgan_vaqt IS NULL);

CREATE FUNCTION

Qatorma-qator:

Argument nomidagi p_ prefiksi — odat: egasi_id ustuni bilan p_egasi argumenti chalkashmasin. Funksiyani oddiy ustun kabi ishlatamiz:

SELECT id, ism, faol_vazifalar_soni(id) AS faol
FROM foydalanuvchilar ORDER BY id LIMIT 3;

 id |       ism        | faol
----+------------------+------
  1 | Aziz Karimov     |   12
  2 | Dilnoza Rahimova |    6
  3 | Sardor Tursunov  |    6
(3 rows)

Aziz'da 13 ta vazifa bor, bittasi Savatda — shuning uchun 12.

2.2 RETURN va AS $$ … $$ — ikki shakl

Tanani yozishning ikki yo'li bor:

• RETURN ifoda (yoki BEGIN ATOMIC … END) — PostgreSQL 14 dan. Tana yaratish paytida tahlil qilinadi: jadval yoki ustun yo'q bo'lsa — xato darhol chiqadi. Funksiya ishlatgan jadvallarga bog'liqlik yoziladi: jadvalni tasodifan o'chirib bo'lmaydi.
• AS $$ … $$ — tana oddiy satr. Faqat chaqirilganda tekshiriladi. Ko'p qatorli tana va PL/pgSQL uchun shu shakl.

$$ — "dollar qo'shtirnoq" (dollar quoting). Ichidagi ' ni ikkilantirish shart emas: 'qo''shildi' o'rniga tana ichida bemalol yoziladi. Trigger darsida ko'rgansiz.

2.3 Jadval qaytaradigan funksiya va nomli argumentlar

Funksiya bitta qiymat emas, qatorlar to'plamini ham qaytara oladi — RETURNS TABLE. DEFAULT — argument berilmasa olinadigan qiymat:

CREATE FUNCTION oxirgi_vazifalar(p_egasi bigint, p_soni int DEFAULT 3)
RETURNS TABLE (id bigint, matn text)
LANGUAGE sql STABLE
AS $$
  SELECT v.id, v.matn FROM vazifalar v
  WHERE v.egasi_id = p_egasi AND v.ochirilgan_vaqt IS NULL
  ORDER BY v.id DESC
  LIMIT p_soni
$$;

CREATE FUNCTION

Bunday funksiya FROM da jadval kabi turadi. Ikkinchi chaqiruvda argumentlarni nomi bilan beramiz (=>) — tartib muhim emas, o'qish oson:

SELECT * FROM oxirgi_vazifalar(1);
SELECT * FROM oxirgi_vazifalar(p_egasi => 2, p_soni => 1);

 id |                   matn
----+------------------------------------------
 48 | Bozordan kartoshka va piyoz olish #bozor
 45 | Rasmlarni siqish
 44 | Qoralama yozish
(3 rows)

 id |          matn
----+------------------------
 36 | Rezyume yangilash #ish
(1 row)

Tanadagi v. taxalluslariga e'tibor bering. RETURNS TABLE (id …, matn …) funksiya ichida id va matn nomli o'zgaruvchilar yaratadi. Ustunni taxallussiz yozsangiz, Postgres "bu ustunmi yoki o'zgaruvchimi?" deb chalkashadi (PL/pgSQL'da bu column reference "id" is ambiguous xatosi). Qoida: tanada har ustunga jadval taxallusi.

Funksiyani SELECT ro'yxatida chaqirsangiz, har qator bitta composite qiymat bo'lib chiqadi — kamdan-kam kerak:

SELECT oxirgi_vazifalar(1, 2);

                oxirgi_vazifalar
-------------------------------------------------
 (48,"Bozordan kartoshka va piyoz olish #bozor")
 (45,"Rasmlarni siqish")
(2 rows)

2.4 Tur qat'iy tekshiriladi

Argument turi e'lon qilingan — noto'g'ri qiymat chaqiruvdayoq to'xtaydi:

SELECT faol_vazifalar_soni('ikki');

ERROR:  invalid input syntax for type bigint: "ikki"
LINE 1: SELECT faol_vazifalar_soni('ikki');
                                   ^

Tarjimasi: "bigint turi uchun noto'g'ri kiritish sintaksisi: «ikki»". ^ belgisi aynan muammoli qiymatni ko'rsatadi.

Tekshirib ko'ring: RETURN (SELECT …) shaklidagi funksiya vazifalar jadvalidan o'qiydi. Kimdir DROP TABLE vazifalar qilsa nima bo'ladi? AS $$ … $$ shaklida-chi?

3. PL/pgSQL — bazaning ichidagi dasturlash tili

3.1 Tuzilma

PL/pgSQL (Procedural Language / PostgreSQL) — SQL ustiga o'zgaruvchi, shart, sikl va xato ushlashni qo'shgan til. Sintaksisi Pascal'ga o'xshaydi, lekin mantiq JavaScript'dagidek: e'lon qil, hisobla, qaytar.

CREATE FUNCTION salomlash(p_ism text) RETURNS text
LANGUAGE plpgsql IMMUTABLE AS $$
BEGIN
  IF p_ism IS NULL OR p_ism = '' THEN
    RETURN 'Assalomu alaykum!';
  END IF;
  RETURN 'Assalomu alaykum, ' || p_ism || '!';
END $$;
SELECT salomlash('Aziz'), salomlash('');

CREATE FUNCTION
        salomlash        |     salomlash
-------------------------+-------------------
 Assalomu alaykum, Aziz! | Assalomu alaykum!
(1 row)

Tana BEGIN … END ichida. IF … THEN … END IF; — JavaScript'dagi if ning o'zi, faqat qavslar o'rniga kalit so'zlar. Har buyruq ; bilan tugaydi.

3.2 O'zgaruvchi va sikl

O'zgaruvchilar DECLARE bo'limida e'lon qilinadi, qiymat := bilan beriladi:

CREATE FUNCTION faktorial(n int) RETURNS bigint
LANGUAGE plpgsql IMMUTABLE AS $$
DECLARE
  natija bigint := 1;
BEGIN
  FOR i IN 2..n LOOP
    natija := natija * i;
  END LOOP;
  RETURN natija;
END $$;
SELECT faktorial(5), faktorial(20);

CREATE FUNCTION
 faktorial |      faktorial
-----------+---------------------
       120 | 2432902008176640000
(1 row)

FOR i IN 2..n LOOP — JS'dagi for (let i = 2; i <= n; i++). 21! esa bigint'ga sig'maydi:

SELECT faktorial(21);

ERROR:  bigint out of range
CONTEXT:  PL/pgSQL function faktorial(integer) line 6 at assignment

CONTEXT qatori — PL/pgSQL'ning eng foydali yordamchisi: qaysi funksiya, nechanchi qator (line 6 — tananing $$ dan keyingi 6-qatori), qaysi buyruq.

Yana bir tuzoq — NULL argument:

SELECT faktorial(-3), faktorial(NULL);

ERROR:  upper bound of FOR loop cannot be null
CONTEXT:  PL/pgSQL function faktorial(integer) line 5 at FOR with integer loop variable

Tarjimasi: "FOR siklining yuqori chegarasi NULL bo'lishi mumkin emas". Yechim — STRICT kalit so'zi: argumentlardan biri NULL bo'lsa, funksiya umuman chaqirilmaydi va darhol NULL qaytadi:

CREATE FUNCTION faktorial2(n int) RETURNS bigint
LANGUAGE plpgsql IMMUTABLE STRICT AS $$
DECLARE natija bigint := 1;
BEGIN
  FOR i IN 2..n LOOP natija := natija * i; END LOOP;
  RETURN natija;
END $$;
SELECT faktorial2(-3) AS manfiy, faktorial2(NULL) IS NULL AS nul;

CREATE FUNCTION
 manfiy | nul
--------+-----
      1 | t
(1 row)

-3 uchun sikl bir marta ham aylanmadi va 1 qaytdi — bu mantiqiy xato, lekin xato xabari yo'q. Haqiqiy kodda manfiy son uchun RAISE EXCEPTION qiling (keyingi bo'limda).

3.3 So'rov natijasi bo'ylab sikl

FOR r IN SELECT … LOOP — har qatorni record turidagi o'zgaruvchiga oladi:

CREATE FUNCTION t_hisob(p_egasi bigint) RETURNS text
LANGUAGE plpgsql STABLE AS $$
DECLARE
  r record;
  natija text := '';
BEGIN
  FOR r IN SELECT ustuvorlik, count(*) AS n FROM vazifalar
           WHERE egasi_id = p_egasi GROUP BY 1 ORDER BY 1 LOOP
    natija := natija || r.ustuvorlik || '=' || r.n || ' ';
  END LOOP;
  RETURN btrim(natija);
END $$;
SELECT t_hisob(1);

CREATE FUNCTION
        t_hisob
------------------------
 past=1 orta=9 yuqori=3
(1 row)

Diqqat: Bu misol sintaksisni ko'rsatish uchun. Aslida bu ish bitta SELECT … GROUP BY bilan, siklsiz bo'ladi. PL/pgSQL'dagi qatorma-qator sikl — JS'dagi for ichida await db.query() ga o'xshaydi (N+1 muammosi). SQL to'plamlar bilan ishlaganda tez; siklni faqat haqiqatan qadamma-qadam mantiq kerak bo'lganda yozing.

Tekshirib ko'ring: faktorial uchun STRICT qo'shdik. oxirgi_vazifalar(1, NULL) chaqirilsa nima bo'ladi (u STRICT emas)? Bilmasangiz — LIMIT NULL nimani anglatishini eslang.

4. Xatolar: RAISE va EXCEPTION

4.1 Xato ko'tarish

Kanon misoli: vazifani "bajarildi" qilish. Vazifa topilmasa yoki allaqachon bajarilgan bo'lsa — jim o'tmasdan, aniq xato:

CREATE FUNCTION t_bajarilgan_qil(p_egasi bigint, p_id bigint)
RETURNS integer LANGUAGE plpgsql AS $$
DECLARE
  v_versiya integer;
  n integer;
BEGIN
  UPDATE vazifalar SET bajarildi = true, bajarilgan_vaqt = now()
  WHERE id = p_id AND egasi_id = p_egasi AND NOT bajarildi
  RETURNING versiya INTO v_versiya;
  GET DIAGNOSTICS n = ROW_COUNT;
  IF n = 0 THEN
    RAISE EXCEPTION 'vazifa % topilmadi yoki allaqachon bajarilgan',
      p_id USING ERRCODE = 'P0002', HINT = 'id va egasini tekshiring';
  END IF;
  RETURN v_versiya;
END $$;

CREATE FUNCTION

Yangi qismlar:

• RETURNING versiya INTO v_versiya — o'zgargan qatordan qiymatni o'zgaruvchiga oladi (INSERT va RETURNING dagi RETURNING ning PL/pgSQL shakli).
• GET DIAGNOSTICS n = ROW_COUNT — oxirgi buyruq nechta qatorga tegdi.
• RAISE EXCEPTION '… % …', p_id — xato ko'tarish; % o'rniga argument qo'yiladi (JS'dagi ${p_id}).
• USING ERRCODE = 'P0002' — xato kodi (no_data_found); HINT — maslahat qatori.

Azizning 2-vazifasi allaqachon bajarilgan. Tranzaksiya ichida chaqiramiz:

BEGIN;
SELECT t_bajarilgan_qil(1, 2) AS yangi_versiya;
SELECT t_bajarilgan_qil(1, 2);
ROLLBACK;

BEGIN
ERROR:  vazifa 2 topilmadi yoki allaqachon bajarilgan
HINT:  id va egasini tekshiring
CONTEXT:  PL/pgSQL function t_bajarilgan_qil(bigint,bigint) line 11 at RAISE
ERROR:  current transaction is aborted, commands ignored until end of transaction block
ROLLBACK

Xato uchta qismdan iborat: matn, bizning HINT va CONTEXT. Ikkinchi xato tanish: Tranzaksiya asoslari darsidagidek, xatodan keyin tranzaksiya "buzilgan" holatda — ROLLBACK gacha hech narsa bajarilmaydi.

Node'da pg bu xatoni err.code === 'P0002', err.hint bilan beradi. Shuning uchun xato kodini o'ylab tanlang: API qatlami kodga qarab 404 yoki 409 qaytaradi (Parametrli so'rovlar va SQL injection darsidagi err.code tahlili kabi).

4.2 Xatoni ushlash — EXCEPTION bloki

BEGIN … EXCEPTION WHEN … THEN … END — PL/pgSQL'dagi try/catch. Kanon misoli — "takror bo'lsa yiqilma, sababini qaytar":

CREATE FUNCTION t_xavfsiz_qosh(p_egasi bigint, p_matn text)
RETURNS text LANGUAGE plpgsql AS $$
BEGIN
  INSERT INTO vazifalar (egasi_id, matn) VALUES (p_egasi, p_matn);
  RETURN 'qo''shildi';
EXCEPTION
  WHEN unique_violation OR exclusion_violation THEN
    RETURN 'takror: ' || SQLERRM;
  WHEN check_violation THEN
    RETURN 'cheklov: ' || (SELECT 'matn');
END $$;

CREATE FUNCTION

Bitta so'rovda uch chaqiruv: yangi matn, registri boshqa takror va bo'sh matn:

BEGIN;
SELECT t_xavfsiz_qosh(1, 'Yangi vazifa') AS a,
       t_xavfsiz_qosh(1, 'yangi VAZIFA') AS b,
       t_xavfsiz_qosh(1, '') AS c;
ROLLBACK;

BEGIN
     a     |                                             b                                              |       c
-----------+--------------------------------------------------------------------------------------------+---------------
 qo'shildi | takror: conflicting key value violates exclusion constraint "vazifalar_egasi_id_matn_excl" | cheklov: matn
(1 row)

ROLLBACK

Uchala natija keldi, so'rov yiqilmadi. SQLERRM — ushlangan xatoning matni. Nega exclusion_violation ham yozilgan? 26-qism yakunida faol vazifa matnining noyobligi oddiy UNIQUE indeksdan istisno cheklovi (exclusion constraint)ga o'tgan (014 migratsiyasi). Uning xato kodi boshqa — 23P01. Faqat unique_violation deb yozilganida, xato ushlanmay so'rov yiqilardi. Kanonda aynan shunday bo'lgan va tuzatilgan. Saboq: ushlaydigan xato nomini haqiqiy xatoga qarab tanlang, taxmin bilan emas.

Diqqat: Har EXCEPTION bloki ichida yashirin SAVEPOINT ochiladi — xato bo'lsa, faqat blok ichidagi o'zgarishlar bekor bo'ladi. Bu bepul emas: sikl ichida minglab marta chaqirilsa, sezilarli sekinlashadi. Xatoni oldindan tekshirish (masalan INSERT … ON CONFLICT DO NOTHING, UPSERT va MERGE) ko'pincha arzonroq.

Tekshirib ko'ring: t_xavfsiz_qosh ichida WHEN OTHERS THEN RETURN 'xato'; yozib qo'ysak, qanday muammo tug'iladi?

5. Volatility: planner'ga bergan va'dangiz

5.1 Uch daraja

Har funksiyaning volatility (o'zgaruvchanlik) belgisi bor. Bu — planner'ga va'da:

Belgi berilmasa — VOLATILE (eng ehtiyotkor, eng sekin). Tizim funksiyalarida qanday turganini pg_proc katalogidan ko'ramiz (i, s, v):

SELECT proname, provolatile FROM pg_proc
WHERE proname IN ('now', 'random', 'lower', 'teglarni_ajrat')
ORDER BY proname;

    proname     | provolatile
----------------+-------------
 lower          | i
 lower          | i
 lower          | i
 now            | s
 random         | v
 random         | v
 random         | v
 random         | v
 teglarni_ajrat | i
(9 rows)

Bir nom bir necha marta chiqdi — turli argument turlari uchun alohida funksiyalar (overloading: lower(text), lower(anyrange) …). now() — STABLE: tranzaksiya boshlangan vaqtni qaytaradi, bitta so'rov ichida o'zgarmaydi. teglarni_ajrat — 26-qismdagi teglarni matndan ajratuvchi, IMMUTABLE.

5.2 EXPLAIN'da farq

Ikki bir xil funksiya, faqat belgisi boshqa:

CREATE FUNCTION t_vol(x bigint) RETURNS bigint
LANGUAGE plpgsql VOLATILE AS $$ BEGIN RETURN x; END $$;
CREATE FUNCTION t_stab(x bigint) RETURNS bigint
LANGUAGE plpgsql STABLE AS $$ BEGIN RETURN x; END $$;

CREATE FUNCTION
CREATE FUNCTION

Kichik jadvalda planner baribir Seq Scan tanlaydi, shuning uchun uni vaqtincha taqiqlaymiz (enable_seqscan = off — faqat tajriba uchun, EXPLAIN ANALYZE'ni o'qish darsidagidek):

SET enable_seqscan = off;
EXPLAIN (COSTS OFF) SELECT id FROM vazifalar WHERE id = t_vol(5);
EXPLAIN (COSTS OFF) SELECT id FROM vazifalar WHERE id = t_stab(5);

SET
                      QUERY PLAN
------------------------------------------------------
 Bitmap Heap Scan on vazifalar
   Filter: (id = t_vol('5'::bigint))
   ->  Bitmap Index Scan on vazifalar_id_egasi_id_key
(3 rows)

                          QUERY PLAN
--------------------------------------------------------------
 Index Only Scan using vazifalar_id_egasi_id_key on vazifalar
   Index Cond: (id = t_stab('5'::bigint))
(2 rows)

Farqqa qarang:

• VOLATILE: shart Filter da — indeks butun jadvalni berdi, keyin har qatorda t_vol(5) qayta chaqirildi. Planner "har qatorda boshqa natija bo'lishi mumkin" deb hisoblaydi va qiymatni indeks qidiruviga qo'ya olmaydi.
• STABLE: shart Index Cond da — funksiya bir marta hisoblandi, indeks to'g'ridan-to'g'ri 5-qatorga bordi.

Million qatorli jadvalda bu farq — millisekund va soniyalar farqi.

Kanon topilmasi: LANGUAGE sql funksiya RETURN x kabi oddiy bo'lsa, Postgres uni so'rovga inline qiladi (tanasini chaqiruv o'rniga qo'yib yuboradi) va belgi yo'qoladi — ikkalasi ham Index Cond bergan. Shuning uchun bu tajribada PL/pgSQL ishlatildi: u hech qachon inline bo'lmaydi.

5.3 Yolg'on va'da — eng xavflisi

Belgini "tezroq bo'lsin" deb oshirib yozish mumkin. Masalan:

CREATE FUNCTION t_yolgon() RETURNS timestamptz
LANGUAGE sql IMMUTABLE RETURN now();

CREATE FUNCTION

Postgres va'dani tekshirmaydi — CREATE FUNCTION muvaffaqiyatli. Lekin endi u now() ni o'zgarmas deb hisoblaydi. Oqibatlari:

• Bunday funksiya ifoda indeksida yoki generated ustunda ishlatilsa — indeksga yaratilgan paytdagi qiymat yozilib qoladi, keyin hech qachon yangilanmaydi.
• Planner tayyorlangan rejada (prepared statement) natijani bir marta hisoblab, keshlab qo'yishi mumkin.

Natija — jim, noto'g'ri ma'lumot. Qoida: belgini eng qat'iy, lekin rost qilib qo'ying. Ishonchingiz komil bo'lmasa — STABLE. Ilg'or constraint'lar va generated ustunlar darsida now() ni generated ustunga qo'yishga urinish generation expression is not immutable xatosini bergandi — Postgres o'z funksiyalarini tekshiradi, lekin sizning va'dangizni emas.

Tekshirib ko'ring: faol_vazifalar_soni ni IMMUTABLE deb e'lon qilsak, qachon noto'g'ri natija ko'rishimiz mumkin?

6. PROCEDURE, CALL va DO

6.1 Funksiya ichida COMMIT yo'q

Funksiya har doim chaqiruvchining tranzaksiyasi ichida ishlaydi. Shuning uchun u tranzaksiyani yakunlay olmaydi:

CREATE FUNCTION t_commit() RETURNS void
LANGUAGE plpgsql AS $$ BEGIN COMMIT; END $$;
SELECT t_commit();

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

Tarjimasi: "tranzaksiyani noto'g'ri yakunlash".

6.2 PROCEDURE — partiyalab ishlash uchun

Protsedura (procedure) — PostgreSQL 11 dan bor. Qiymat qaytarmaydi, CALL bilan chaqiriladi va ichida COMMIT qila oladi. Bu nima uchun kerak? Million qatorni bitta tranzaksiyada o'zgartirsangiz — uzoq qulflar, katta WAL va bir xato hammasini bekor qiladi. Protsedura ishni bo'laklab, har bo'lakdan keyin saqlaydi:

CREATE TABLE t_log (n int);
CREATE PROCEDURE t_partiya() LANGUAGE plpgsql AS $$
BEGIN
  FOR i IN 1..3 LOOP
    INSERT INTO t_log VALUES (i);
    COMMIT;
  END LOOP;
END $$;
CALL t_partiya();
SELECT count(*) FROM t_log;

CREATE TABLE
CREATE PROCEDURE
CALL
 count
-------
     3
(1 row)

Uch marta COMMIT — uch alohida tranzaksiya. Lekin bitta shart bor: CALL ning o'zi tranzaksiya blokida bo'lmasligi kerak.

BEGIN; CALL t_partiya(); ROLLBACK;

BEGIN
ERROR:  invalid transaction termination
CONTEXT:  PL/pgSQL function t_partiya() line 5 at COMMIT
ROLLBACK

Node'dan chaqirganda buni yodda tuting: tranzaksiya ichidagi client.query('CALL …') xuddi shu xatoni beradi.

6.3 DO — bir martalik nomsiz blok

DO — saqlanmaydigan, nomsiz PL/pgSQL bloki. Migratsiya skriptlarida shartli amal uchun qulay ("rol yo'q bo'lsa — yarat"):

DO $$
BEGIN
  IF NOT EXISTS (SELECT 1 FROM pg_roles WHERE rolname = 't_rol') THEN
    RAISE NOTICE 't_rol yo''q — yaratilardi';
  END IF;
END $$;

NOTICE:  t_rol yo'q — yaratilardi
DO

RAISE NOTICE — xato emas, xabar (JS'dagi console.log ga o'xshaydi). Kanonning 105-rls.sql migratsiyasi aynan shu naqsh bilan klaster rolini "bo'lmasa yaratadi" — Row Level Security darsida ko'rasiz. DO ichidagi '' — tana $$ ichida bo'lsa ham, '…' satr ichida apostrof ikkilanadi.

Tekshirib ko'ring: Har kecha 30 kundan eski loglarni 10 000 tadan o'chirish kerak. Funksiya, protsedura yoki DO — qaysi biri?

7. Hujumchi nigohi: SECURITY DEFINER

7.1 Kimning huquqi bilan ishlaydi

Sukut bo'yicha funksiya chaqiruvchining huquqlari bilan ishlaydi (SECURITY INVOKER). SECURITY DEFINER esa — funksiya egasining huquqlari bilan. Bu sudo'ga o'xshaydi: oddiy foydalanuvchi jadvalni o'qiy olmaydi, lekin superuser yozgan funksiya orqali — o'qiy oladi.

Foydali holat: kimgadir butun jadvalni emas, faqat bitta aniq amalni ruxsat berish. Xavfli tomoni — funksiya ichidagi har nom (jadval, funksiya, operator) chaqiruvchining search_path i bo'yicha topiladi.

7.2 Hujum: vaqtinchalik jadval bilan "o'g'irlash"

Huquqsiz rol m05_mehmon va superuser yozgan SECURITY DEFINER funksiya yaratamiz. Rollar haqida to'liq — Rollar va huquqlar darsida; hozir CREATE ROLE — "yangi foydalanuvchi", SET ROLE — "vaqtincha shu foydalanuvchi bo'lib ishlash" (SQL tili va buyruq turlari darsida ko'rgansiz).

CREATE ROLE m05_mehmon;
CREATE FUNCTION umumiy_soni() RETURNS bigint
LANGUAGE sql STABLE SECURITY DEFINER
AS $$ SELECT count(*) FROM vazifalar $$;
GRANT EXECUTE ON FUNCTION umumiy_soni() TO m05_mehmon;

CREATE ROLE
CREATE FUNCTION
GRANT

Endi hujumchi bo'lamiz. Jadvalni to'g'ridan-to'g'ri o'qiy olmaymiz, funksiya orqali esa — 48. Keyin vaqtinchalik jadval (temporary table) yaratamiz. U faqat shu ulanishda yashaydi va uni yaratish uchun maxsus huquq kerak emas:

SET ROLE m05_mehmon;
SELECT count(*) FROM vazifalar;
SELECT umumiy_soni();
CREATE TEMP TABLE vazifalar AS SELECT 999 AS id;
SELECT umumiy_soni();
RESET ROLE;

SET
ERROR:  permission denied for table vazifalar
 umumiy_soni
-------------
          48
(1 row)

SELECT 1
 umumiy_soni
-------------
           1
(1 row)

RESET

Funksiya hujumchining jadvalini o'qidi — 48 o'rniga 1. Sabab: vaqtinchalik jadvallar sxemasi (pg_temp) jadval nomlarini qidirishda sukut bo'yicha birinchi turadi. Funksiya superuser huquqi bilan ishlasa ham, nomni chaqiruvchining muhitida qidirdi.

Diagrammada vazifalar nomi qanday "topilgani"ga qarang — chap tomonda himoyasiz funksiya, o'ngda keyingi bo'limdagi himoyalangani:

flowchart TD
  F["umumiy_soni()<br/>egasi huquqi bilan"] --> Q{"vazifalar<br/>qayerda?"}
  Q -->|"search_path yo'q"| T["pg_temp.vazifalar<br/>hujumchiniki"]
  Q -->|"SET search_path<br/>+ public.vazifalar"| P["public.vazifalar<br/>haqiqiy jadval"]
  T --> N1["natija: 1"]
  P --> N2["natija: 48"]

Bu misolda hujumchi faqat sonni soxtaladi. Lekin funksiya shu jadvalga yozsa yoki natijaga qarab qaror qilsa (masalan "foydalanuvchi admin'mi?"), hujumchi superuser nomidan o'zi xohlagan ishni qildiradi. Xuddi shu yo'l bilan operator yoki funksiyani ham almashtirish mumkin — shuning uchun bu PostgreSQL hujjatida alohida ogohlantirilgan.

7.3 Himoya

Uch qadam:

CREATE OR REPLACE FUNCTION umumiy_soni() RETURNS bigint
LANGUAGE sql STABLE SECURITY DEFINER
SET search_path = pg_catalog, pg_temp
AS $$ SELECT count(*) FROM public.vazifalar $$;
REVOKE EXECUTE ON FUNCTION umumiy_soni() FROM PUBLIC;
SET ROLE m05_mehmon;
CREATE TEMP TABLE vazifalar AS SELECT 999 AS id;
SELECT umumiy_soni();
RESET ROLE;

CREATE FUNCTION
REVOKE
SET
SELECT 1
 umumiy_soni
-------------
          48
(1 row)

RESET

1. SET search_path = pg_catalog, pg_temp — funksiya ichida qidiruv yo'li qat'iy: avval tizim katalogi, vaqtinchalik sxema oxirida. Chaqiruvchining sozlamasi ta'sir qilmaydi.
2. To'liq nom — public.vazifalar: noaniqlik qolmaydi.
3. REVOKE EXECUTE … FROM PUBLIC — funksiyalar sukut bo'yicha hammaga chaqirishga ochiq. DEFINER funksiyani faqat kerakli rolga bering. (m05_mehmon ga oldin alohida GRANT bergan edik — u qoldi.)

Kanon ham proconfig ustunida farqni ko'rsatgan: himoyasiz funksiyada (null), himoyalangan funksiyada {"search_path=pg_catalog, pg_temp"}.

Tajriba rolini tozalaymiz:

DROP FUNCTION umumiy_soni();
DROP ROLE m05_mehmon;

DROP FUNCTION
DROP ROLE

8. Qachon bazada, qachon ilovada?

Xavfli tomonlari, qisqacha: kodni ikki tilda saqlash; testlash qiyinroq (funksiya testi uchun haqiqiy Postgres kerak — Testcontainers darsida); versiyalash faqat migratsiya orqali; debugger yo'q — RAISE NOTICE va CONTEXT bilan ishlaysiz.

9. Ko'p uchraydigan xatolar

9.1 Volatility'ni yozmaslik

Belgisiz funksiya VOLATILE — WHERE id = f(x) indeksni ishlata olmaydi. Har funksiyaga rost belgi: o'qiydigan — STABLE, toza hisob — IMMUTABLE.

9.2 CREATE OR REPLACE bilan qaytarish turini o'zgartirish

CREATE OR REPLACE FUNCTION faol_vazifalar_soni(p_egasi bigint)
RETURNS integer
LANGUAGE sql STABLE
RETURN 0;

ERROR:  cannot change return type of existing function
HINT:  Use DROP FUNCTION faol_vazifalar_soni(bigint) first.

Tarjimasi: "mavjud funksiyaning qaytarish turini o'zgartirib bo'lmaydi; avval DROP FUNCTION qiling". REPLACE faqat tanani almashtiradi. Argument turi o'zgarsa esa — umuman yangi funksiya yaratiladi (overloading), eskisi qoladi. Migratsiyada: DROP FUNCTION nom(eski_turlar), keyin CREATE.

9.3 Argument nomi ustun nomi bilan bir xil

egasi_id bigint argumenti va egasi_id ustuni — PL/pgSQL'da column reference "egasi_id" is ambiguous. Argumentlarga p_ prefiksi, ustunlarga jadval taxallusi.

9.4 SECURITY DEFINER + search_path siz

Yuqoridagi hujum. Har DEFINER funksiyada SET search_path = pg_catalog, pg_temp, to'liq nomlar va REVOKE … FROM PUBLIC.

9.5 Tranzaksiya ichida CALL qilingan COMMIT'li protsedura

invalid transaction termination. Partiyali protsedurani alohida, autocommit rejimida chaqiring.

Darsda yaratilgan obyektlarni o'chiramiz (kanon ham shunday qilgan):

DROP FUNCTION faol_vazifalar_soni, oxirgi_vazifalar, salomlash,
  faktorial, faktorial2, t_hisob, t_bajarilgan_qil,
  t_xavfsiz_qosh, t_vol, t_stab, t_yolgon, t_commit;
DROP PROCEDURE t_partiya;
DROP TABLE t_log;

DROP FUNCTION
DROP PROCEDURE
DROP TABLE

10. Mashqlar

Mashqlar kurs/mashqlar/27/05-plpgsql/ da. Ular o'z jadvallaringiz bilan, istalgan bo'sh bazada (masalan tajriba) bajariladi; yechimlar tekshirilgan (biz ishga tushirdik, PostgreSQL 17.6).

1-mashq (oson): QQS bilan narx

qqs_bilan(narx numeric) funksiyasini yozing: narxga 12 % QQS qo'shib, butun so'mgacha yaxlitlasin (round, Ifodalar va o'rnatilgan funksiyalar). Bazani o'qimaydi — qaysi volatility belgisi to'g'ri? Osh (35 000), lag'mon (28 000) va manti (30 000) uchun natijani chiqaring.

CREATE FUNCTION qqs_bilan(narx numeric) RETURNS numeric
LANGUAGE sql IMMUTABLE STRICT
RETURN round(narx * 1.12);

SELECT nom || ': ' || qqs_bilan(narx)
FROM (VALUES ('osh', 35000), ('lag''mon', 28000),
             ('manti', 30000)) AS t(nom, narx);
DROP FUNCTION qqs_bilan;

osh: 39200
lag'mon: 31360
manti: 33600

2-mashq (o'rta): Xavfsiz qo'shish

m05_mahsulot (id int GENERATED ALWAYS AS IDENTITY, nom text UNIQUE, narx int CHECK (narx > 0)) jadvalini yarating. mahsulot_qosh(p_nom text, p_narx int) RETURNS text PL/pgSQL funksiyasi: muvaffaqiyatda 'qo''shildi: <id>' (RETURNING id INTO), takror nomda 'takror', noto'g'ri narxda 'narx xato'. Uch chaqiruvni bitta SELECT da sinang.

CREATE TABLE m05_mahsulot (
  id int GENERATED ALWAYS AS IDENTITY,
  nom text UNIQUE,
  narx int CHECK (narx > 0)
);
CREATE FUNCTION mahsulot_qosh(p_nom text, p_narx int)
RETURNS text LANGUAGE plpgsql AS $$
DECLARE
  v_id int;
BEGIN
  INSERT INTO m05_mahsulot (nom, narx) VALUES (p_nom, p_narx)
  RETURNING id INTO v_id;
  RETURN 'qo''shildi: ' || v_id;
EXCEPTION
  WHEN unique_violation THEN RETURN 'takror';
  WHEN check_violation THEN RETURN 'narx xato';
END $$;

SELECT mahsulot_qosh('osh', 35000), mahsulot_qosh('osh', 36000),
       mahsulot_qosh('manti', -1);
SELECT count(*) FROM m05_mahsulot;
DROP FUNCTION mahsulot_qosh;
DROP TABLE m05_mahsulot;

qo'shildi: 1|takror|narx xato
1

3-mashq (qiyin): Partiyalab o'chirish

m05_log (id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY, yaratilgan date) jadvaliga 25 000 qator qo'shing. Yarmi 2025-01-01, yarmi 2026-10-01 sanali bo'lsin (generate_series va CASE). eski_loglarni_ochir(p_sana date, p_partiya int) protsedurasini yozing: p_sana dan eski qatorlarni p_partiya tadan o'chirib, har partiyadan keyin COMMIT qilsin va partiyalar sonini m05_hisobot jadvaliga yozsin. CALL eski_loglarni_ochir('2026-01-01', 5000) dan keyin qolgan qatorlar va partiyalar sonini chiqaring. Ishora: DELETE … WHERE id IN (SELECT id … LIMIT p_partiya); GET DIAGNOSTICS n = ROW_COUNT; EXIT WHEN n = 0; — sikldan chiqish.

CREATE TABLE m05_log (
  id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  yaratilgan date NOT NULL
);
INSERT INTO m05_log (yaratilgan)
SELECT CASE WHEN g % 2 = 0 THEN date '2025-01-01'
            ELSE date '2026-10-01' END
FROM generate_series(1, 25000) g;
CREATE TABLE m05_hisobot (partiyalar int);

CREATE PROCEDURE eski_loglarni_ochir(p_sana date, p_partiya int)
LANGUAGE plpgsql AS $$
DECLARE
  n int;
  sanoq int := 0;
BEGIN
  LOOP
    DELETE FROM m05_log WHERE id IN (
      SELECT id FROM m05_log WHERE yaratilgan < p_sana
      LIMIT p_partiya);
    GET DIAGNOSTICS n = ROW_COUNT;
    EXIT WHEN n = 0;
    sanoq := sanoq + 1;
    COMMIT;
  END LOOP;
  INSERT INTO m05_hisobot VALUES (sanoq);
END $$;

CALL eski_loglarni_ochir('2026-01-01', 5000);
SELECT 'qoldi: ' || count(*) FROM m05_log;
SELECT 'partiyalar: ' || partiyalar FROM m05_hisobot;
DROP PROCEDURE eski_loglarni_ochir;
DROP TABLE m05_log, m05_hisobot;

qoldi: 12500
partiyalar: 3

11. Loyiha qadami: kanon tajribasi, loyihaga kirmaydi

Bu darsda vazifalar sxemasiga yangi migratsiya yo'q — barcha tajriba funksiyalari o'chirildi. Nega?

• Loyihada funksiyalar allaqachon bor: 26-qismdagi teglarni_ajrat (IMMUTABLE) va beshta trigger funksiyasi. Ular ma'lumot yaxlitligi uchun — bazaga tegishli joy.
• bajarildi qilish, takrorni tekshirish kabi qoidalar vazifalar-api xizmat qatlamida (TypeScript) qoladi: u yerda Zod validatsiyasi, Problem Details javoblari va node:test testlari bor (25-qism qatlamlari). Bir qoidani ikki joyda saqlash — ikki xil xatoga yo'l.
• SECURITY DEFINER funksiya loyihada yo'q. Ilova cheklangan rol bilan ishlaydi va qatorlarni Row Level Security himoya qiladi — Row Level Security va Rollar va huquqlar darslarida.

Keyingi darsda bu funksiyalarning eng ko'p uchraydigan "egasi" — trigger'larga to'liq qaytamiz.

12. Real ishda

• Migratsiyalarda: DO $$ … $$ bloklari va CREATE OR REPLACE FUNCTION — deyarli har jiddiy loyihada. Prisma va boshqa vositalar ham funksiyani xom SQL migratsiya sifatida qo'llaydi (Prisma Migrate chuqur).
• Supabase va shunga o'xshash platformalar API'ni to'g'ridan-to'g'ri bazaga ulaydi; u yerda biznes mantiq ko'pincha PL/pgSQL funksiyalarda, huquqlar SECURITY DEFINER va RLS bilan boshqariladi — bu darsdagi xavflar aynan o'sha yerda uchraydi.
• Kod ko'rib chiqishda (code review) birinchi qaraladigan joylar: volatility belgisi, EXCEPTION WHEN OTHERS, DEFINER funksiyadagi search_path.
• Intervyu: "STABLE va IMMUTABLE farqi?", "Funksiya bilan protsedura farqi?", "SECURITY DEFINER qanday xavfli?" — middle/senior backend savollari.

Xulosa

• CREATE FUNCTION — bazadagi nomli mantiq; oddiy hisob — LANGUAGE sql (RETURN shakli bog'liqlikni yozadi), murakkab — plpgsql.
• PL/pgSQL: DECLARE, :=, IF, FOR … LOOP, RAISE EXCEPTION … USING ERRCODE, EXCEPTION WHEN (yashirin SAVEPOINT bilan).
• Volatility — planner'ga va'da: VOLATILE indeks shartini o'chiradi, yolg'on IMMUTABLE jim noto'g'ri natija beradi.
• PROCEDURE ichida COMMIT mumkin (tranzaksiya blokidan tashqarida CALL); DO — bir martalik blok.
• SECURITY DEFINER — egasining huquqi bilan; har doim SET search_path, to'liq nomlar va REVOKE EXECUTE … FROM PUBLIC.

Keyingi dars: Triggerlar — funksiyani jadval hodisasiga ulash: BEFORE/AFTER, ROW/STATEMENT, audit va trigger'larning yashirin narxi.

Manbalar

• PostgreSQL 18 hujjati: CREATE FUNCTION (jumladan "Writing SECURITY DEFINER Functions Safely") — 2026-10-25 da ko'rildi.
• PostgreSQL 18 hujjati: PL/pgSQL — SQL Procedural Language, Errors and Messages.
• PostgreSQL 18 hujjati: Function Volatility Categories, CREATE PROCEDURE, DO.
• PostgreSQL 18 hujjati: PostgreSQL Error Codes.