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

Функции

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

create or replace function complete_quiz_attempt(
    p_enrollment_id int,
    p_quiz_id int,
    p_answers jsonb -- [{question_id, answer_option_id}]
)
    returns bigint
    language plpgsql as
$$
declare
    v_attempt_id    bigint;
    v_attempt_num   int;
    v_total_points  int;
    v_earned_points int;
    v_score         int;
    v_passed        boolean;
    v_passing_score numeric;
begin
    -- check answer count matches question count
    if (select count(*) from jsonb_array_elements(p_answers)) !=
       (select count(*) from quiz_question where quiz_id = p_quiz_id)
    then
        raise exception 'Answer count does not match question count for quiz %', p_quiz_id;
    end if;

    -- check all questions belong to the quiz
    if exists (select 1
               from jsonb_array_elements(p_answers) a
               where not exists (select 1
                                 from quiz_question
                                 where id = (a ->> 'question_id')::bigint
                                   and quiz_id = p_quiz_id)) then
        raise exception 'One or more questions do not belong to quiz %', p_quiz_id;
    end if;

    -- check all answer options belong to their respective questions
    if exists (select 1
               from jsonb_array_elements(p_answers) a
               where not exists (select 1
                                 from quiz_answer_option
                                 where id = (a ->> 'answer_option_id')::bigint
                                   and quiz_question_id = (a ->> 'question_id')::bigint)) then
        raise exception 'One or more answer options do not belong to their question';
    end if;

    select coalesce(max(attempt_number), 0) + 1
    into v_attempt_num
    from quiz_attempt
    where enrollment_id = p_enrollment_id
      and quiz_id = p_quiz_id;

    select coalesce(sum(points), 0)
    into v_total_points
    from quiz_question
    where quiz_id = p_quiz_id;

    select coalesce(sum(qq.points), 0)
    into v_earned_points
    from jsonb_array_elements(p_answers) a
             join quiz_question qq on qq.id = (a ->> 'question_id')::bigint
             join quiz_answer_option qao on qao.id = (a ->> 'answer_option_id')::bigint
    where qao.correct = true;

    select passing_score
    into v_passing_score
    from quiz
    where id = p_quiz_id;

    v_score := case
                   when v_total_points > 0
                       then ((v_earned_points::numeric / v_total_points) * 100)::int
                   else 0 end;
    
    v_passed := v_score >= v_passing_score;

    insert into quiz_attempt (attempt_number, completed_at, status, score, total_points, earned_points, passed, quiz_id,
                              enrollment_id)
    values (v_attempt_num, now(),
            case when v_passed then 'PASSED' else 'FAILED' end,
            v_score, v_total_points, v_earned_points, v_passed,
            p_quiz_id, p_enrollment_id)
    returning id into v_attempt_id;

    insert into quiz_attempt_answer (correct, quiz_question_id, quiz_attempt_id)
    select qao.correct,
           (a ->> 'question_id')::bigint,
           v_attempt_id
    from jsonb_array_elements(p_answers) a
             join quiz_answer_option qao on qao.id = (a ->> 'answer_option_id')::bigint;

    insert into quiz_attempt_answer_selected_options (quiz_attempt_answer_id, quiz_answer_option_id)
    select qaa.id, (a ->> 'answer_option_id')::bigint
    from jsonb_array_elements(p_answers) a
             join quiz_attempt_answer qaa
                  on qaa.quiz_attempt_id = v_attempt_id
                      and qaa.quiz_question_id = (a ->> 'question_id')::bigint;

    return v_attempt_id;
end;
$$;

Процедури

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

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();