Функции, Процедури и Тригери

Во оваа фаза беа имплементирани три функции, три процедури и три тригери кои ја поддржуваат основната бизнис логика на системот IDontKnow. Тие автоматизираат процеси како пресметка на кредитно-пондериран просек и отворен долг, проверка на слободни места, регистрирање наплата со автоматска алокација кон обврски, упис на студент на курс, сериско генерирање обврски за школарина, како и валидација на оценки, ажурирање на отворената сума и контрола на капацитетот на курсевите преку тригери.

Сите рутини се креирани во базата advdb_202526l_prj_idontknow (PostgreSQL 17.9).

13_routines.sql​

Функции

Функциите се користат за пресметување и враќање на вредности кои се потребни во повеќе делови од системот и може да се вградат директно во прашалник, поглед или друга рутина.

1. fn_prosek_student

CREATE OR REPLACE FUNCTION fn_prosek_student(
    p_student_id INTEGER,
    p_sistem_ocenuvanje_id INTEGER DEFAULT NULL
)
RETURNS NUMERIC
LANGUAGE sql
STABLE
AS $$
    SELECT ROUND(SUM(sov.ocena_vrednost::numeric * ak.krediti)
                     / NULLIF(SUM(ak.krediti), 0), 2)
    FROM sm_student_ocena_zapis soz
        JOIN sm_student_upisan_kurs suk ON soz.student_upisan_kurs_id = suk.id
        JOIN sm_student_semestar_upis ssu ON suk.student_semestar_upis_id = ssu.id
        JOIN sm_studenti_smerovi ss ON ssu.studenti_smerovi_id = ss.id
        JOIN sm_kurs_realizacija kr ON suk.kurs_realizacija_id = kr.id
        JOIN sm_akreditacija ak ON kr.akreditacija_id = ak.id
        JOIN sm_sistem_ocenuvanje_vrednost sov ON soz.sistem_ocenuvanje_vrednost_id = sov.id
    WHERE ss.student_id = p_student_id
      AND sov.polozitelna
      AND (p_sistem_ocenuvanje_id IS NULL OR sov.sistem_ocenuvanje_id = p_sistem_ocenuvanje_id);
$$;

Функцијата fn_prosek_student го пресметува кредитно-пондерираниот просек од положените оценки на даден студент. Како влезни параметри прима p_student_id и опционално p_sistem_ocenuvanje_id, со кој пресметката може да се ограничи на еден систем на оценување. Поминувајќи низ синџирот на табели од sm_student_ocena_zapis до sm_akreditacija, ја дели сумата на (вредност на оценка × кредити) со вкупните кредити и резултатот го заокружува на две децимали.

Реализирана е како функција бидејќи враќа единечна вредност што директно се вградува во прашалници — на пример листа на студенти со просек над праг, услов за стипендија или уверение (SELECT indeks, fn_prosek_student(s.id) FROM sm_studenti s), што не е возможно со процедура. За масовни извештаи постои погледот vw_studentski_oceni; функцијата е за поединечна, параметризирана употреба во произволен контекст.

2. fn_otvoren_dolg

CREATE OR REPLACE FUNCTION fn_otvoren_dolg(
    p_user_id INTEGER,
    p_vid_valuta_id INTEGER
)
RETURNS NUMERIC
LANGUAGE sql
STABLE
AS $$
    SELECT COALESCE(SUM(fo.otvorena_suma), 0)
    FROM fin_finansiska_obvrska fo
    WHERE fo.user_id = p_user_id
      AND fo.vid_valuta_id = p_vid_valuta_id
      AND fo.otvorena_suma > 0
      AND fo.status_finansiska_obvrska_id IN
          (SELECT id FROM fin_status_finansiska_obvrska WHERE ime IN ('Izdadena', 'Delumno platena'));
$$;

Функцијата fn_otvoren_dolg ја враќа вкупната отворена сума на корисник во дадена валута. Прима p_user_id и p_vid_valuta_id, и собира otvorena_suma само од обврските во статус Izdadena или Delumno platena, враќајќи 0 ако нема такви.

Истата проверка ја користат повеќе места — блокада при упис, опомени и работа на шалтер — па е издвоена како функција со повратна вредност што може да се вгради дури и во услов (WHERE fn_otvoren_dolg(...) > 0). Како процедура би морала да враќа преку OUT-параметар и не би можела да се користи на тој начин.

3. fn_slobodni_mesta

CREATE OR REPLACE FUNCTION fn_slobodni_mesta(p_kurs_realizacija_id INTEGER)
RETURNS INTEGER
LANGUAGE sql
STABLE
AS $$
    SELECT kr.kapacitet_studenti
               - (SELECT COUNT(*)::int
                  FROM sm_student_upisan_kurs suk
                  WHERE suk.kurs_realizacija_id = kr.id
                    AND suk.status_slushanje = 'UPISAN')
    FROM sm_kurs_realizacija kr
    WHERE kr.id = p_kurs_realizacija_id;
$$;

Функцијата fn_slobodni_mesta го враќа бројот на слободни места на дадена реализација на курс — kapacitet_studenti намалено за бројот на активни (UPISAN) уписи. Доколку капацитетот е NULL, тоа значи неограничен број места.

Се прикажува при пребарување курсеви и се користи внатре во процедурата pr_zapisi_student_na_kurs, па затоа е функција што враќа вредност. Контролата на капацитетот ја спроведува тригерот trg_kapacitet_kurs; функцијата само информира, додека тригерот е тој што го гарантира ограничувањето.

Процедури

Процедурите се користат за извршување деловни операции врз базата кои опфаќаат повеќе чекори и табели, се повикуваат експлицитно со CALL и единствени смеат да користат COMMIT / ROLLBACK внатре.

1. pr_registriraj_naplata

CREATE OR REPLACE PROCEDURE pr_registriraj_naplata(
    p_user_id INTEGER,
    p_suma NUMERIC,
    p_vid_valuta_id INTEGER,
    p_organizaciona_edinica_id INTEGER,
    p_referenca VARCHAR DEFAULT NULL,
    p_korisnik_naplata_id INTEGER DEFAULT NULL
)
LANGUAGE plpgsql
AS $$
DECLARE
    v_naplata_id INTEGER;
    v_smetka_id  INTEGER;
    v_ostatok    NUMERIC := p_suma;
    v_obvrska    RECORD;
    v_alocirano  NUMERIC;
BEGIN
    IF p_suma <= 0 THEN
        RAISE EXCEPTION 'Сумата мора да биде позитивна (добиено: %)', p_suma;
    END IF;

    -- активна трансакциска сметка на организационата единица (приоритет: иста валута)
    SELECT oes.transakciska_smetka_id
    INTO v_smetka_id
    FROM or_organizaciona_edinica_smetki oes
        JOIN fin_transakciski_smetki ts ON oes.transakciska_smetka_id = ts.id
    WHERE oes.organizaciona_edinica_id = p_organizaciona_edinica_id
      AND oes.aktivna
      AND ts.aktivna
    ORDER BY (ts.vid_valuta_id = p_vid_valuta_id) DESC, ts.id
    LIMIT 1;

    IF v_smetka_id IS NULL THEN
        RAISE EXCEPTION 'Организационата единица % нема активна трансакциска сметка',
            p_organizaciona_edinica_id;
    END IF;

    INSERT INTO fin_naplati (user_id, korisnik_naplata_id, transakciska_smetka_id,
                             organizaciona_edinica_id, naplatena_suma, vid_valuta_id,
                             status_transakcija_id, nadvoresna_referenca,
                             requested_at, confirmed_at, data)
    VALUES (p_user_id, COALESCE(p_korisnik_naplata_id, p_user_id), v_smetka_id,
            p_organizaciona_edinica_id, p_suma, p_vid_valuta_id,
            (SELECT id FROM fin_status_transakcija WHERE ime_status = 'ACCEPTED'),
            p_referenca, now(), now(), now())
    RETURNING id INTO v_naplata_id;

    FOR v_obvrska IN
        SELECT fo.id, fo.otvorena_suma
        FROM fin_finansiska_obvrska fo
        WHERE fo.user_id = p_user_id
          AND fo.vid_valuta_id = p_vid_valuta_id
          AND fo.otvorena_suma > 0
        ORDER BY fo.datum_dospevanje NULLS LAST, fo.id
        FOR UPDATE
    LOOP
        EXIT WHEN v_ostatok <= 0;
        v_alocirano := LEAST(v_ostatok, v_obvrska.otvorena_suma);
        INSERT INTO fin_naplata_alokacija (naplata_id, finansiska_obvrska_id,
                                           alocirana_suma, created_at)
        VALUES (v_naplata_id, v_obvrska.id, v_alocirano, now());
        v_ostatok := v_ostatok - v_alocirano;
    END LOOP;

    IF v_ostatok > 0 THEN
        RAISE NOTICE 'Наплата %: % останува неалоцирано (преплата).', v_naplata_id, v_ostatok;
    END IF;
END;
$$;

Процедурата pr_registriraj_naplata регистрира наплата и автоматски ја алоцира кон отворените обврски на корисникот. Откако ќе валидира дека сумата е позитивна и ќе ја најде активната трансакциска сметка на организационата единица (со приоритет на иста валута), внесува запис во fin_naplati, па во циклус ги покрива обврските по редослед на најстаро доспевање, внесувајќи редови во fin_naplata_alokacija. Ако остане непокриен дел (преплата), тој останува неалоциран и се пријавува со NOTICE.

Ова е деловна операција составена од еден INSERT во fin_naplati и повеќе INSERT-и во fin_naplata_alokacija, која се извршува атомски и не враќа вредност за прашалник — затоа е процедура, а не функција. Намерно не ја менува otvorena_suma: тоа го прави тригерот trg_azuriraj_otvorena_suma, така што изведената вредност останува конзистентна без оглед дали алокацијата ја внела оваа процедура, друга апликација или рачен SQL. Процедурата одлучува како се распределува наплатата, а тригерот гарантира книговодствена конзистентност.

2. pr_zapisi_student_na_kurs

CREATE OR REPLACE PROCEDURE pr_zapisi_student_na_kurs(
    p_student_id INTEGER,
    p_kurs_realizacija_id INTEGER
)
LANGUAGE plpgsql
AS $$
DECLARE
    v_ssu_id   INTEGER;
    v_user_id  INTEGER;
    v_dolg     NUMERIC;
    v_valuta   INTEGER;
    v_slobodni INTEGER;
BEGIN
    SELECT ssu.id
    INTO v_ssu_id
    FROM sm_student_semestar_upis ssu
        JOIN sm_studenti_smerovi ss ON ssu.studenti_smerovi_id = ss.id
        JOIN sm_kurs_realizacija kr ON kr.id = p_kurs_realizacija_id
    WHERE ss.student_id = p_student_id
      AND ssu.akademski_semestar_id = kr.akademski_semestar_id
    ORDER BY ssu.datum_upis DESC
    LIMIT 1;

    IF v_ssu_id IS NULL THEN
        RAISE EXCEPTION 'Студентот % нема упис во семестарот на курсот %',
            p_student_id, p_kurs_realizacija_id;
    END IF;

    IF EXISTS (SELECT 1
               FROM sm_student_upisan_kurs
               WHERE student_semestar_upis_id = v_ssu_id
                 AND kurs_realizacija_id = p_kurs_realizacija_id) THEN
        RAISE EXCEPTION 'Студентот % е веќе запишан на курсот %',
            p_student_id, p_kurs_realizacija_id;
    END IF;

    SELECT s.user_id
    INTO v_user_id
    FROM sm_studenti s
    WHERE s.id = p_student_id;

    FOR v_valuta IN
        SELECT DISTINCT vid_valuta_id
        FROM fin_finansiska_obvrska
        WHERE user_id = v_user_id
          AND otvorena_suma > 0
    LOOP
        v_dolg := fn_otvoren_dolg(v_user_id, v_valuta);
        IF v_dolg > 0 THEN
            RAISE EXCEPTION 'Студентот % има отворен долг % (валута %) — уписот е блокиран',
                p_student_id, v_dolg, v_valuta;
        END IF;
    END LOOP;

    v_slobodni := fn_slobodni_mesta(p_kurs_realizacija_id);
    IF v_slobodni IS NOT NULL AND v_slobodni <= 0 THEN
        RAISE EXCEPTION 'Нема слободни места на курсот %', p_kurs_realizacija_id;
    END IF;

    INSERT INTO sm_student_upisan_kurs (student_semestar_upis_id, kurs_realizacija_id, status_slushanje)
    VALUES (v_ssu_id, p_kurs_realizacija_id, 'UPISAN');
END;
$$;

Процедурата pr_zapisi_student_na_kurs запишува студент на курс. Го наоѓа семестралниот упис на студентот за семестарот на курсот, одбива дупликат упис, одбива ако студентот има отворен долг (преку fn_otvoren_dolg) и одбива ако нема слободни места (преку fn_slobodni_mesta), а потоа внесува запис во sm_student_upisan_kurs со статус UPISAN.

Повеќечекорната валидациона секвенца со завршно запишување, повикана експлицитно од акција на корисникот, ја прави процедура; таа воедно демонстрира компонирање бидејќи ги повикува функциите fn_otvoren_dolg и fn_slobodni_mesta. Проверката на долг е политика на уписот, а не интегритет на податоците (администратор може по исклучок да запише студент со долг преку директен INSERT), па затоа не е тригер; капацитетот, напротив, е физичко ограничување и е заштитен со тригер.

3. pr_generiraj_obvrski_za_semestar

CREATE OR REPLACE PROCEDURE pr_generiraj_obvrski_za_semestar(p_akademski_semestar_id INTEGER)
LANGUAGE plpgsql
AS $$
DECLARE
    v_cenovnik RECORD;
    v_oe       INTEGER;
    v_opis     VARCHAR;
    v_student  RECORD;
    v_brojac   INTEGER := 0;
BEGIN
    SELECT asem.organizaciona_edinica_id,
           c.id AS cenovnik_id,
           c.iznos,
           c.vid_valuta_id,
           asem.tip_naplata_organizaciona_edinica_id
    INTO v_cenovnik
    FROM sm_akademski_semestar asem
        JOIN fin_tip_naplati_cenovnik c
            ON c.tip_naplata_organizaciona_edinica_id = asem.tip_naplata_organizaciona_edinica_id
                AND c.aktivna
    WHERE asem.id = p_akademski_semestar_id
    ORDER BY c.prioritet
    LIMIT 1;

    IF v_cenovnik IS NULL THEN
        RAISE EXCEPTION 'Нема активен ценовник за семестар %', p_akademski_semestar_id;
    END IF;

    v_oe := v_cenovnik.organizaciona_edinica_id;
    v_opis := 'Школарина семестар ' || p_akademski_semestar_id;

    FOR v_student IN
        SELECT DISTINCT s.user_id
        FROM sm_student_semestar_upis ssu
            JOIN sm_studenti_smerovi ss ON ssu.studenti_smerovi_id = ss.id
            JOIN sm_studenti s ON ss.student_id = s.id
        WHERE ssu.akademski_semestar_id = p_akademski_semestar_id
          AND NOT EXISTS (SELECT 1
                          FROM fin_finansiska_obvrska fo
                          WHERE fo.user_id = s.user_id
                            AND fo.opis = 'Школарина семестар ' || p_akademski_semestar_id)
    LOOP
        INSERT INTO fin_finansiska_obvrska (user_id, organizaciona_edinica_id,
                                            tip_naplata_organizaciona_edinica_id,
                                            tip_naplati_cenovnik_id,
                                            status_finansiska_obvrska_id,
                                            datum_kreiranje, datum_dospevanje,
                                            originalna_suma, otvorena_suma,
                                            vid_valuta_id, opis)
        VALUES (v_student.user_id, v_oe, v_cenovnik.tip_naplata_organizaciona_edinica_id,
                v_cenovnik.cenovnik_id,
                (SELECT id FROM fin_status_finansiska_obvrska WHERE ime = 'Izdadena'),
                now(), CURRENT_DATE + 30, v_cenovnik.iznos, v_cenovnik.iznos,
                v_cenovnik.vid_valuta_id, v_opis);
        v_brojac := v_brojac + 1;
        IF v_brojac % 1000 = 0 THEN
            COMMIT; -- дозволено само во процедура
        END IF;
    END LOOP;

    RAISE NOTICE 'Генерирани % обврски за семестар %', v_brojac, p_akademski_semestar_id;
END;
$$;

Процедурата pr_generiraj_obvrski_za_semestar сериски генерира обврски за школарина за сите студенти запишани во даден академски семестар, според активниот ценовник на семестарот. Прескокнува студенти кои веќе имаат таква обврска (идемпотентно според opis), а на секои 1000 внесени редови прави COMMIT.

Ова е единствената рутина што мора да биде процедура: периодичниот COMMIT врз batch од десетици илјади редови е дозволен само во процедура — функциите и тригерите во PostgreSQL не смеат да завршуваат трансакции.

Тригери

Тригерите се користат за автоматско извршување логика при внесување или промена на податоци во табелите. Тие се единствениот механизам кој гарантира дека правилото важи без разлика кој и како пишува во табелата — апликација, процедура или рачен INSERT.

1. trg_validna_ocena

CREATE OR REPLACE FUNCTION trgf_validna_ocena()
RETURNS TRIGGER
LANGUAGE plpgsql
AS $$
DECLARE
    v_status VARCHAR;
BEGIN
    SELECT status_slushanje
    INTO v_status
    FROM sm_student_upisan_kurs
    WHERE id = NEW.student_upisan_kurs_id;

    IF v_status IS DISTINCT FROM 'UPISAN' THEN
        RAISE EXCEPTION 'Оценка може да се внесе само за активен упис (статус: %)', v_status;
    END IF;

    IF NEW.osvoeni_poeni IS NOT NULL AND (NEW.osvoeni_poeni < 0 OR NEW.osvoeni_poeni > 100) THEN
        RAISE EXCEPTION 'Освоени поени мора да бидат 0–100 (добиено: %)', NEW.osvoeni_poeni;
    END IF;

    IF NEW.datum_zapis > now() THEN
        RAISE EXCEPTION 'Датумот на запис не смее да биде во иднина';
    END IF;

    RETURN NEW;
END;
$$;

DROP TRIGGER IF EXISTS trg_validna_ocena ON sm_student_ocena_zapis;
CREATE TRIGGER trg_validna_ocena
    BEFORE INSERT ON sm_student_ocena_zapis
    FOR EACH ROW
    EXECUTE FUNCTION trgf_validna_ocena();

Тригерот trg_validna_ocena се активира пред внесување оценка во sm_student_ocena_zapis. Тој одбива оценка ако уписот не е во статус UPISAN, ако освоените поени се надвор од опсегот 0–100, или ако датумот на запис е во иднина.

Реализиран е како тригер бидејќи станува збор за интегритет на податоците — невалидна оценка е невалидна без оглед кој ја внесува (апликација, import или рачен INSERT), што процедура не би го гарантирала. CHECK-ограничување не е доволно бидејќи правилото за статусот бара поглед во друга табела (sm_student_upisan_kurs).

2. trg_azuriraj_otvorena_suma

CREATE OR REPLACE FUNCTION trgf_azuriraj_otvorena_suma()
RETURNS TRIGGER
LANGUAGE plpgsql
AS $$
DECLARE
    v_nova NUMERIC;
BEGIN
    SELECT otvorena_suma - NEW.alocirana_suma
    INTO v_nova
    FROM fin_finansiska_obvrska
    WHERE id = NEW.finansiska_obvrska_id
    FOR UPDATE;

    IF v_nova < 0 THEN
        RAISE EXCEPTION 'Алокацијата (%) ја надминува отворената сума на обврската %',
            NEW.alocirana_suma, NEW.finansiska_obvrska_id;
    END IF;

    UPDATE fin_finansiska_obvrska
    SET otvorena_suma                = v_nova,
        status_finansiska_obvrska_id = CASE
                                           WHEN v_nova = 0 THEN
                                               (SELECT id FROM fin_status_finansiska_obvrska WHERE ime = 'Platena')
                                           ELSE
                                               (SELECT id FROM fin_status_finansiska_obvrska WHERE ime = 'Delumno platena')
            END
    WHERE id = NEW.finansiska_obvrska_id;

    RETURN NEW;
END;
$$;

DROP TRIGGER IF EXISTS trg_azuriraj_otvorena_suma ON fin_naplata_alokacija;
CREATE TRIGGER trg_azuriraj_otvorena_suma
    AFTER INSERT ON fin_naplata_alokacija
    FOR EACH ROW
    EXECUTE FUNCTION trgf_azuriraj_otvorena_suma();

Тригерот trg_azuriraj_otvorena_suma се активира по внесување алокација во fin_naplata_alokacija. Тој ја намалува otvorena_suma на соодветната обврска за алоцираната сума, одбива алокација што би ја однела сумата под нула, и автоматски го поставува статусот на Platena (кога ќе достигне нула) или Delumno platena.

otvorena_suma е изведена вредност што мора да се ажурира секогаш кога ќе настане алокација, па е врзана за настанот преку тригер, а не за повикувачот. Кога би била дел од процедурата pr_registriraj_naplata, секој иден писател на алокации (на пр. модул за поврат или миграција) би морал да не заборави да ја ажурира. Тригерот е AFTER INSERT затоа што редот прво мора да постои како валиден, а ажурирањето на другата табела е последица.

3. trg_kapacitet_kurs

CREATE OR REPLACE FUNCTION trgf_kapacitet_kurs()
RETURNS TRIGGER
LANGUAGE plpgsql
AS $$
DECLARE
    v_kapacitet INTEGER;
    v_upisani   INTEGER;
BEGIN
    IF NEW.status_slushanje <> 'UPISAN' THEN
        RETURN NEW;
    END IF;

    SELECT kapacitet_studenti
    INTO v_kapacitet
    FROM sm_kurs_realizacija
    WHERE id = NEW.kurs_realizacija_id
    FOR UPDATE; -- сериализира конкурентни уписи на иста реализација

    IF v_kapacitet IS NULL THEN
        RETURN NEW; -- неограничен капацитет
    END IF;

    SELECT COUNT(*)
    INTO v_upisani
    FROM sm_student_upisan_kurs
    WHERE kurs_realizacija_id = NEW.kurs_realizacija_id
      AND status_slushanje = 'UPISAN';

    IF v_upisani >= v_kapacitet THEN
        RAISE EXCEPTION 'Курсот % е полн (капацитет %)', NEW.kurs_realizacija_id, v_kapacitet;
    END IF;

    RETURN NEW;
END;
$$;

DROP TRIGGER IF EXISTS trg_kapacitet_kurs ON sm_student_upisan_kurs;
CREATE TRIGGER trg_kapacitet_kurs
    BEFORE INSERT ON sm_student_upisan_kurs
    FOR EACH ROW
    EXECUTE FUNCTION trgf_kapacitet_kurs();

Тригерот trg_kapacitet_kurs се активира пред внесување упис во sm_student_upisan_kurs. За нов упис со статус UPISAN ја заклучува реализацијата (FOR UPDATE), го брои бројот на активни уписи и одбива ако е достигнат kapacitet_studenti (при што NULL значи неограничено).

Капацитетот мора да издржи и конкурентни уписи — двајца студенти што истовремено го земаат последното место. Проверка само во процедура би била race-condition бидејќи двете трансакции би виделе слободно место; тригерот со заклучување на редот на реализацијата ги сериализира таквите инсерти. Процедурата pr_zapisi_student_na_kurs сепак повикува fn_slobodni_mesta однапред за љубезна порака, но тригерот е последната, непробивна одбрана.

Како се тестирано (сите со ROLLBACK, без траен ефект)

Тест	Резултат
fn_prosek_student(1)	8.00
fn_otvoren_dolg(2, 1)	74925.00
fn_slobodni_mesta(1)	100
П1: наплата 5000 за корисник 2	1 алокација, долг 74925→69925, статус → Delumno platena (Т2)
П2: студент со долг	блокирано со јасна порака
П2: студент без долг	упис креиран (UPISAN)
П3: непостоечки семестар	„Нема активен ценовник"
Т1: 150 поени / оценка на POLOZENO	двете одбиени
Т3: упис над капацитет	одбиен