Функции, процедури и тригери

Функции

Функција за креирање на review од страна на корисник за курс.

create or replace function write_review(
    user_id bigint,
    course_version_id bigint,
    new_rating int,
    new_comment text
)
    returns review
as
$$
    declare
        r review;
        e_id bigint;
    begin
        -- Validate rating first, before any DB work
        if new_rating < 1 or new_rating > 5 then
            raise exception 'Rating must be between 1 and 5, got %', new_rating
                using errcode = 'CHECK_VIOLATION';
        end if;


        select e.id into e_id
        from enrollment e
        where e.user_id = write_review.user_id
          and e.course_version_id = write_review.course_version_id
        limit 1;

        -- Validate enrollment exists
        if e_id is null then
            raise exception 'No enrollment found for user % on course version %',
                user_id, course_version_id
                using errcode = 'NO_DATA_FOUND';
        end if;

        -- Validate there's no review for that enrollment
        if exists(select 1 from review rv where rv.enrollment_id = e_id) then
            raise exception 'Review already submitted for enrollment %', e_id
                using errcode = 'UNIQUE_VIOLATION';
        end if;


        insert into review (rating, comment, date, enrollment_id)
        values (new_rating, new_comment, now(), e_id)
        returning * into r;

        return r;
    end;
$$ language plpgsql;

Процедури

Процедура за испраќање на мејлови за потсетување за онлајн состанок со експерт.

create or replace procedure send_all_emails()
as
$$
    declare
        email_to_send meeting_email_reminder;
    begin
        for email_to_send in
            select * from meeting_email_reminder where sent = false and scheduled_at <= now()
        loop
            perform pg_notify('send_email', email_to_send.id::text);


            update meeting_email_reminder set sent = true where id = email_to_send.id;
        end loop;
    end;
$$
    language plpgsql;

Тригери

Тригер за поставување на предходната верзија од курсот како неактивна при креирање на нова верзија.

create or replace function mark_course_version_as_inactive()
returns trigger
as $$
    begin
        update course_version
        set active = false
        where course_id = NEW.course_id;
        return NEW;
    end;
    $$ language plpgsql;

create trigger mark_course_version_as_inactive_trigger
before insert on course_version
for each row
execute function mark_course_version_as_inactive();