Database Programming

Во оваа фаза е имплементирана апликациската логика со функции, процедури и тригери. Секоја од нив е документирана во продолжение.

Процедури

Процедура за почеток на смена на возач

Процедурата проверува дали постои id-то на возачот и VIN бројот кај возилото и фрла соодветен исклучот. Доколку возачот и возилото се валидни се додава нов запис за смена во табелата Driver_Vehicle. Оваа процедура ја имплементира бизнис логиката за најава на возачите на системот, со што тие официјално стануваат активни и достапни за преземање на патници.

create or replace procedure start_shift(
    in v_driver_id int4,
    in v_vin_vehicle varchar(17)
)
    language plpgsql as
$$
begin
    if not exists(select user_id from driver where user_id = v_driver_id) then
        raise exception 'Driver with id % does not exist', v_driver_id;
    end if;

    if not exists(select vin from vehicle where vin = v_vin_vehicle) then
        raise exception 'Vehicle with VIN % does not exist', v_vin_vehicle;
    end if;

    insert into driver_vehicle(vin_vehicle, id_driver, time_from, time_to)
    values (v_vin_vehicle, v_driver_id, now(), null);
    commit;
end;
$$;

Процедура за креирање на барање за превоз

Процедурата се повикува кога корисникот иницира ново барање за превоз, при што детало се валидираат координатите, бројот на патници, седишта за деца и багаж. Доколку сите бизнис правила се исполнети, се генерира нов запис во Request со почетен статус pending и се враќа неговиот ID. Ова е почетната точка на главниот тек во апликацијата, процесот на повикување такси возило.

create or replace procedure make_request(
    in v_customer_id int4,
    in v_start_latitude double precision,
    in v_start_longitude double precision,
    in v_end_latitude double precision,
    in v_end_longitude double precision,
    in v_number_of_adult_passengers int4 default 1,
    in v_number_of_children int4 default 0,
    in v_female_driver boolean default false,
    in v_luggage boolean default false,
    in v_luggage_count int4 default 0,
    in v_baby_seat_count int4 default 0
)
    language plpgsql as
$$
begin
    if not exists(select user_id
                  from customer c
                  where c.user_id = v_customer_id)
    then
        raise exception 'Customer with id % does not exist', v_customer_id;
    end if;

    if v_start_latitude < -90.0 or v_start_latitude > 90.0
        or v_start_longitude < -180.0 or v_start_longitude > 180.0
        or v_end_latitude < -90.0 or v_end_latitude > 90.0
        or v_end_longitude < -180.0 or v_end_longitude > 180.0
    then
        raise exception 'Invalid location values';
    end if;

    if v_number_of_adult_passengers < 0 then
        raise exception 'Number of adult passengers cannot be negative';
    end if;

    if v_number_of_children < 0 then
        raise exception 'Number of children cannot be negative';
    end if;

    if v_number_of_adult_passengers + v_number_of_children < 0 then
        raise exception 'There must be at least 1 passenger';
    end if;

    if v_baby_seat_count < 0 then
        raise exception 'Baby seat count cannot be negative';
    end if;

    if v_luggage = false and v_luggage_count > 0 then
        raise exception 'Luggage count cannot be greater than zero if the luggage flag is set to false.';
    end if;

    if v_luggage = true and v_luggage_count <= 0 then
        raise exception 'Invalid luggage count parameters';
    end if;

    if v_start_latitude = v_end_latitude and v_start_longitude = v_end_longitude then
        raise exception 'Start and destination location cannot be the same';
    end if;

    insert into request(customer_user_id,
                        start_latitude,
                        start_longitude,
                        end_latitude,
                        end_longitude,
                        timestamp,
                        number_of_adult_passengers,
                        number_of_children,
                        status,
                        female_driver,
                        luggage,
                        luggage_count,
                        baby_seat_count)
    values (v_customer_id,
            v_start_latitude,
            v_start_longitude,
            v_end_latitude,
            v_end_longitude,
            now(),
            v_number_of_adult_passengers,
            v_number_of_children,
            'pending',
            v_female_driver,
            v_luggage,
            v_luggage_count,
            v_baby_seat_count);
    commit;
end;
$$;

Процедура за додавање на Waypoint на некое барање

Оваа процедура му овозможува на корисникот да додаде попатна дестинација (waypoint) на веќе креирано барање за превоз. Таа ги проверува географските опсези и спречува внес на дупликат локации, притоа автоматски пресметувајќи го редоследот (seqno). Ја имплементира бизнис логиката за сложени рути, овозможувајќи им на возачите однапред да го видат точниот план на движење.

create or replace procedure add_waypoint(
    in v_request_id int4,
    in v_latitude int4,
    in v_longitude int4
)
    language plpgsql as
$$
declare
    v_prev_seqno int4;
begin
    if not exists(select id from request where id = v_request_id) then
        raise exception 'Request with id % does not exist', v_request_id;
    end if;

    if v_latitude < -90.0 or v_latitude > 90.0
        or v_longitude < -180.0 or v_longitude > 180.0 then
        raise exception 'Invalid waypoint coordinates';
    end if;

    if exists(select id
              from waypoints w
              where w.latitude = v_latitude
                and w.longitude = v_longitude
                and w.request_id = v_request_id) then
        raise exception 'Waypoint already added';
    end if;

    select count(*)
    into v_prev_seqno
    from waypoints w
    where w.request_id = v_request_id;

    insert into waypoints(latitude, longitude, seqno, request_id)
    values (v_latitude, v_longitude, v_prev_seqno + 1, v_request_id);
    commit;
end;
$$;

Процедура за зпочнување на возење со такси

Процедурата проверува дали постои id-то на понудата, доколку не постои фрла соодветен исклучок. Потоа се пронаоѓа најблискиот слободен возач кој е на активна смена и на крај се додава нов запис во табелата Ride со статус in_progress, а статусите на понудата и барањето се ажурираат во accepted и in_progress. Ова е критичен дел од бизнис логиката кој го означува физичкиот почеток на патувањето и транзицијата на возилото во зафатена состојба.

create or replace procedure start_ride(
    in v_offer_id int4,
    out v_ride_id int4
)
    language plpgsql as
$$
declare
    v_request_id  int4;
    v_vehicle_vin varchar(17);
    v_driver_id   int4;
begin
    if not exists(select id from offer where id = v_offer_id) then
        raise exception 'Offer with id % does not exist', v_offer_id;
    end if;

    select o.request_id,
           o.driver_user_id
    into
        v_request_id,
        v_driver_id
    from offer o
             join request r on o.request_id = r.id
    where o.id = v_offer_id;

    v_vehicle_vin := (select vin_vehicle from driver_vehicle where id_driver = v_driver_id);

    insert into ride(start_time,
                     end_time,
                     distance_traveled,
                     vehicle_vin,
                     driver_user_id,
                     request_id,
                     status,
                     offer_id)
    values (now(),
            null,
            0,
            v_vehicle_vin,
            v_driver_id,
            v_request_id,
            'in_progress',
            v_offer_id);

    update offer
    set status='accepted'
    where id = v_offer_id;

    update request
    set status='in_progress'
    where id = v_request_id;

    commit;
end;
$$;

Процедура за вработување на возач во компанија

Процедурата проверува дали id-то на возачот е валидно, доколку не постои фрла соодветен исклучок. Оваа процедура е дел од административниот модул на апликацијата и овозможува менаџирање на човечки ресурси, односно формално поврзување на возачот со конкретна такси компанија.

create or replace procedure employ_driver(id_employee int, id_company int)
language plpgsql
as $$
    begin
        if not exists (
                   select 1
                   from driver
                   where user_id=id_employee)
               then
                   raise exception 'Driver does not exist';
        else
               insert into
                   employmenthistory (employee_user_id, start_date, end_date, company_id)
                   values (id_employee,current_date, null,id_company);
        end if;
    end;
$$;

Процедура за вработување на диспечер во компанија

Процедурата проверува дали id-то на диспечерот е валидно, доколку не постои фрла соодветен исклучок. Се користи за управување со кадар во рамките на компанијата. Ова овозможува диспечерот да добие овластување за креирање и испраќање на понуди во таа компанија.

create or replace procedure employ_dispatcher(id_employee int, id_company int)
language plpgsql
as $$
    begin
        if not exists (
                   select 1
                   from dispatcher
                   where user_id=id_employee)
               then
                   raise notice 'Dispatcher does not exist';
        else
               insert into
                   employmenthistory (employee_user_id, start_date, end_date, company_id)
                   values (id_employee,current_date, null,id_company);
        end if;
    end;
$$;

Процедура за отпуштање/завршување на договор на возач во компанија

Процедурата прави две проверки. Првата проверка ја пребарува табелата Driver за id-то на возачот, доколку истото не постои се фрла соодветен исклучок. Потоа се проверува дали постои соодветен запис во табелата EmploymentHistory кој потврдува дека возачот е тековно вработен во компанијата. На крај се променува записот од табелата EmploymentHistory на тој начин што се става краен датум. Оваа процедура служи за прекинување на соработката, оневозможувајќи му на возачот понатаму да вози за таа компанија.

create or replace procedure fire_driver(id_employee int, id_company int)
language plpgsql
as $$
    begin
        if not exists (
               select 1
               from driver
               where user_id=id_employee)
           then
               raise exception 'Driver does not exist';
       else
           if not exists (
               select 1
               from employmenthistory
               where employee_user_id=id_employee and end_date==null and company_id=id_company)
           then
               raise exception 'Driver does not have an employment';
           else
               update employmenthistory
               set end_date=current_date
               where employee_user_id==id_employee and end_date==null and company_id=id_company;
               raise notice 'Driver is fired';
               commit;
           end if;
       end if;
    end;
$$;

Процедура за отпуштање/завршување на договор на диспечер во компанија

Процедурата прави две проверки. Првата проверка ја пребарува табелата Dispatcher за id-то на диспечарот, доколку истото не постои се фрла соодветен исклучок. Потоа се проверува дали постои соодветен запис во табелата EmploymentHistory кој потврдува дека деспечерот е тековно вработен во компанијата. На крај се променува записот од табелата EmploymentHistory на тој начин што се става краен датум. Се користи за администрирање на правата на вработените, по извршувањето диспечерот ги губи сите привилегии за менаџирање со барањата на таа компанија.

create or replace procedure fire_dispatcher(id_employee int, id_company int)
language plpgsql
as $$
    begin
        if not exists (
               select 1
               from dispatcher
               where user_id=id_employee)
           then
               raise notice 'Dispatcher does not exist';
       else
           if not exists(
               select 1
               from employmenthistory
               where employee_user_id=id_employee and end_date==null and company_id=id_company)
           then
               raise exception 'Dispatcher does not have an employment';
           else
               update employmenthistory
               set end_date=current_date
               where employee_user_id=id_employee and end_date==null and company_id=id_company;
               raise notice 'Dispatcher is fired';
               commit;
           end if;
       end if;
    end;
$$;

Процедура за пишување на оцена

Оваа процедурата се повикува кога корисник испраќа свој коментар и рејтинг за возењето откако ќе пристигне на дестинацијата. Се проверува дали постои запис во табелата Ride за соодветните id_ride и id_customer доколку не постои се фрла исклучок Customer can not give a rating on a ride they do not have.. Имплементира логика за корисничка повратна информација, која е клучна за евалуација на квалитетот на такси услугата.

create or replace procedure write_rating(rating numeric, comment text, id_ride int, id_customer int)
language plpgsql
as $$
    begin
        if not exists(
            select 1
            from Ride r
            join Request req on r.request_id=req.id
            where r.id=id_ride and req.customer_user_id=id_customer
        )
        then
            raise exception 'Customer can not give a rating on a ride they do not have.';
        end if;
        insert into review (rating, comment, ride_id, customer_user_id)
        values (write_rating.rating,write_rating.comment,id_ride,id_customer);
    end;
$$;

Процедура за испраќање порака

Пораките можат да бидат испратени од возачот или патникот и се однесуваат на едно возење. Доколку не постои возење за соодветните id_ride и id_user се фрла исклучок User can not write a chat for a ride they do not have.. Оваа процедура е главна во системот за чет помеѓу патникот и возачот за време на реализацијата на услугата.

create or replace procedure write_chat(new_message text, id_ride int, id_user int)
language plpgsql
as $$
    begin
        if not exists(
            select 1
            from Ride r
            join Request req on r.request_id=req.id
            where r.id=id_ride and req.customer_user_id=id_user
        )
        then
            raise exception 'User can not write a chat for a ride they do not have.';
        end if;
        insert into chatmessage(message, user_id_from, ride_id)
        values (new_message,id_user,id_ride);
    end;
$$;

Процедура за поднесување пријава

Оваа процедура се повикува кога корисниците поднесуваат пријава за време на возењето. Тоа може да биде некоја поплака од типот брзо возење или скршнување од патот. Доколку не постои возење за соодветните id_ride и user_id се фрла исклучок Customer can not write a report on a ride they do not have.. Се користи во модулот за безбедност и заштита на корисниците, овозможувајќи брза реакција на поддршката.

create or replace procedure write_report(id_ride int, user_id int, new_message text, new_title text,temp_latitude float, temp_long float, new_reason text)
language plpgsql
as $$
    begin
        if not exists(
            select 1
            from Ride r
            join Request req on r.request_id=req.id
            where r.id=id_ride and req.customer_user_id=user_id
        )
        then
            raise exception 'Customer can not write a report on a ride they do not have.';
        end if;
        insert into report(ride_id, customer_user_id, message, title, latitude, longitude, reason)
        values (id_ride,user_id,new_message,new_title,temp_latitude,temp_long,new_reason);
    end;
$$;

Процедура за креирање понуда за одредено барање

Се повикува кога диспечерот креира понуда со цена и проценето време на пристигнување (ETA) за некое активно барање. Процедурата гарантира дека цената е позитивна, времето е во иднина и дека диспечерот и избраниот возач работат за иста компанија. Ја имплементира бизнис логиката за понуди, каде компаниите поднесуваат предлози кои патникот понатаму треба да ги разгледа.

create or replace procedure create_offer(
    request_id int4,
    dispatcher_user_id int4,
    price numeric(19, 2),
    currency_catalog_id int4,
    eta timestamp,
    customer_user_id int4
)
    language plpgsql
AS
$$
declare
    v_driver_user_id  int4;
    v_start_latitude  double precision;
    v_start_longitude double precision;
begin
    if not exists(select * from request where request.id = request_id and request.status = 'pending') then
        raise exception 'Request with id % and pending status does not exist', request_id;
    end if;
    if price <= 0 then
        raise exception 'Price has to be greater than 0';
    end if;
    if eta <= now() then
        raise exception 'ETA cannot be lower than the time of creation';
    end if;

    select start_latitude, start_longitude
    into v_start_latitude, v_start_longitude
    from request
    where id = request_id;

    select d.user_id, dc.vin_vehicle
    into v_driver_user_id
    from driver d
             join driver_vehicle dc on d.user_id = dc.id_driver
    where dc.time_to is null
      and d.latitude is not null
      and d.longitude is not null
    order by power(v_start_latitude - d.latitude, 2) + power(v_start_longitude - d.longitude, 2)
    limit 1;

    if (select company_id from employmenthistory where employee_user_id = dispatcher_user_id and end_date is null) !=
       (select company_id from employmenthistory where employee_user_id = v_driver_user_id and end_date is null) then
        raise exception 'Dispatcher and driver company mismatch';
    end if;

    insert into offer(status, created_at, request_id, dispatcher_user_id, driver_user_id, price, currency_catalog_id,
                      eta, customer_user_id)
    values ('pending', now(), create_offer.request_id, create_offer.dispatcher_user_id, v_driver_user_id,
            create_offer.price, create_offer.currency_catalog_id, create_offer.eta, create_offer.customer_user_id);
    commit;
end;
$$;

Процедура за прифаќање на понуда

Оваа процедура се повикува кога корисникот прифаќа одредена понуда за превоз. Таа го ажурира статусот на избраната понуда и на самото барање во accepted, додека сите останати конкурентни понуди за истото барање се одбиваат, односно нивниот статус се променува во rejected. Ова ја завршува бизнис логиката на договарање на цената и условите за превоз.

create or replace procedure accept_offer(offer_id int4)
    language plpgsql
as
$$
declare
    request_id_proc int4;
begin
    if not exists(select * from offer where id = offer_id and status = 'pending') then
        raise exception 'Offer with id = % and pending status does not exist', offer_id;
    end if;
    request_id_proc := (select request_id from offer where id = offer_id);
    if not exists(select * from request where id = request_id_proc and status = 'pending') then
        raise exception 'The request for the given offer is not in pending status';
    end if;
    update offer set status = 'accepted' where id = offer_id;
    update offer set status = 'rejected' where id != offer_id and offer.request_id = request_id_proc;
    update request set status = 'accepted' where id = request_id_proc;
    commit;
end;
$$;

Процедура за завршување на смена на возач

Процедурата проверува дали постои id-то на возачот, доколку не постои фрла соодветен исклучок. Потоа се проверува дали возачот има точно една активна смена која не е завршена. На крај се ажурира записот во табелата Driver_Vehicle на тој начин што се поставува тековното време во колоната time_to. Во апликацијата, оваа логика се повикува кога возачот сака да се одјави од системот, означувајќи го крајот на неговото работно време.

create or replace procedure finish_shift(
    in driver_id int4
)
    language plpgsql as
$$
declare
    num_of_shifts int4;
begin
    if not exists(select user_id from driver where user_id = driver_id) then
        raise exception 'Driver with id % does not exist', driver_id;
    end if;

    num_of_shifts := (select count(*) from driver_vehicle where id_driver = driver_id and time_to is null);

    if (num_of_shifts != 1) then
        raise exception 'Driver has not started a shift';
    end if;

    update driver_vehicle
    set time_to=now()
    where id_driver = driver_id
      and time_to is null;
    commit;
end;
$$;

Функции и тригери

Функција и тригер кој спречува два пати доделување на активна смена на ист возач

Функцијата проверува дали постои id-то на возачот и VIN бројот на возилото, при што фрла соодветен исклучок доколку некој од нив не е валиден. Дополнително, се проверува дали возачот е на смена. Тригерот се извршува пред внесување или ажурирање на запис во табелата Driver_Vehicle. Неговата намена е да ја одржи конзистентноста на работното време и да спречи логички грешки при најава во апликацијата.

create or replace function check_active_shift()
    returns trigger as
$check_active_shift$
begin
    if not exists(select user_id from driver where user_id = new.id_driver) then
        raise exception 'Driver with id % does not exist', new.id_driver;
    end if;

    if not exists(select vin from vehicle where vin = new.vin_vehicle) then
        raise exception 'Vehicle with VIN % does not exist', new.vin_vehicle;
    end if;

    if exists(select id
              from driver_vehicle
              where id_driver = new.id_driver
                and time_to is null) then
        raise exception 'Driver is already on shift';
    end if;

    return new;
end;
$check_active_shift$ language plpgsql;
create trigger check_active_shift
    before insert
    on driver_vehicle
    for each row
execute function check_active_shift();

Функција и тригер за спречување на промена на статус на завршена понуда

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

create or replace function prevent_completed_offer_downgrade()
    returns trigger as
$prevent_completed_offer_downgrade$
begin
    if old.status = 'completed'
        and new.status <> 'completed' then
        raise exception 'Completed offer cannot change status';
    end if;

    return new;
end;
$prevent_completed_offer_downgrade$ language plpgsql;
create or replace trigger prevent_completed_offer_downgrade
    before update
    on offer
    for each row
execute function prevent_completed_offer_downgrade();

Функција и тригер за спречување на возење со неважечка возачка дозвола

Функцијата го пребарува датумот на истекување на возачката дозвола за соодветниот возач преку поврзување на табелите Driver и DriverLicense. Доколку дозволата е истечена во однос на тековниот датум, се фрла соодветен исклучок, а тригерот се извршува пред внес на нов запис во табелата Ride. Целта на оваа рестрикција е правна и сигурносна заштита на платформата, осигурувајќи дека само лиценцирани возачи можат да превезуваат патници.

create or replace function prevent_expired_license_ride()
    returns trigger as
$prevent_expired_license_ride$
declare
    exp_date date;
begin
    select dl.expire_date
    into exp_date
    from driver d
             join driverlicense dl on d.driver_license_id = dl.id
    where d.user_id = new.driver_user_id;

    if exp_date is not null and exp_date < current_date then
        raise exception 'Driver license expired';
    end if;

    return new;
end;
$prevent_expired_license_ride$ language plpgsql;
create or replace trigger prevent_expired_license_ride
    before insert
    on ride
    for each row
execute function prevent_expired_license_ride();

Тригер и функција за проверка на единствена оценка

Секој корисник смее да остави максимум една оценка за секое возење. Функцијата враќа тригер кој доколку во табелата Review постои оценка од корисникот за тоа возење фрла соодветен исклучот и тригерот се извршува пред внес на запис во табелата Review. Ова бизнис правило спречува вештачко манипулирање со рејтинзите на возачите и гарантира фер статистика.

create or replace function check_one_review()
returns trigger
language plpgsql
as $$
    begin
        if 0 < (select count(*)
                from review
                where ride_id=new.ride_id and customer_user_id=new.customer_user_id)
        then
            raise exception 'There is already a review from the same customer for this ride';
        end if;
        return new;
    end;
$$;
create or replace trigger check_valid_review
    before insert on review
    for each row
    execute function check_one_review();

Тригер и функција за проверка на бројот на слободни седишта

Секој корисник при испраќање на барање за превоз напоменува колку патници ќе има (деца, воздрасни и бебиња). Функцијата check_available_seats проверува дали во возилото кое се нуди во понудата има доволно седишта и враќа тригер кои се извршува пред внес или промена на запис во табелата Offer. Ја спроведува клучната логика за логистичка компатибилност, со што системот оневозможува испраќање на возило кое физички не може да ги собере патниците.

create or replace function check_available_seats()
returns trigger
language plpgsql
as $$
    begin
        if (select v.passenger_capacity
            from vehicle v
            inner join driver_vehicle dv on v.vin=dv.vin_vehicle
            where dv.id_driver=new.driver_user_id)<(select r.number_of_children+r.number_of_adult_passengers+r.baby_seat_count
                                                    from request r
                                                    inner join offer o on r.id=o.request_id
                                                    where o.id=new.id)
            then
            raise exception 'Not enough seats in the vehicle';
        end if;
        return new;
    end;
$$;
create or replace trigger check_valid_seats
    before insert or update on offer
    for each row
    execute function check_available_seats();

Функција и тригер за проверка на валидноста на email адреса и телефонски број

Функцијата проверува дали постои корисник со ист email или ист телефонски број и фрла исклучоци User with this email already exists и User with this phone number already exists соодветно. Функцијата враќа тригер кои се извршува пред секое додавање на нов запис-корисник во табелата AppUser. Ова спречува дуплирање на идентитети и потенцијални злоупотреби во апликацијата при регистрација.

create or replace function unique_email_and_phone()
returns trigger
as
    $$
    begin
        if exists(
            select 1
            from appuser
            where appuser.email=NEW.email
        ) then
            raise exception 'User with this email already exists';
        end if ;
        if exists(
            select 1
            from appuser
            where appuser.phone_number=NEW.phone_number
        ) then
            raise exception 'User with this phone number already exists';
        end if ;
    end;
    $$;
create trigger check_valid_email_and_phone_number
before insert on Appuser
for each row
execute function unique_email_and_phone()

Функција и тригер за проверка на достапност на возач

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

create or replace function ride_assign() returns trigger as
$ride_assign$
begin
    if exists(select * from ride where ride.status = 'in_progress' and ride.driver_user_id = NEW.driver_user_id) then
        raise exception 'Driver not free';
    end if;
    return NEW;
end;
$ride_assign$ language plpgsql;

create trigger ride_assign before insert on ride
    for each row execute function ride_assign();

Функција и тригер за спречување на манипулација со цената

Функцијата враќа тригер кој спречува промена на цената на понудата откако истата ќе добие статус accepted или completed. Дополнително, се прави проверка дали внесената цена е поголема од нула, при што се фрла соодветен исклучок доколку овие услови не се исполнети. Ова директно ја заштитува финансиската сигурност во системот и спречува неовластени или измамнички промени на трошоците по договарањето.

create or replace function prevent_price_manipulation()
    returns trigger as
$prevent_price_manipulation$
begin
    if OLD.status in ('accepted', 'completed') and NEW.price <> OLD.price then
        raise exception 'Price cannot be modified once the offer is accepted or completed. Current status: %', OLD.status;
    end if;

    if NEW.price <= 0 then
        raise exception 'Price must be a positive value';
    end if;

    return NEW;
end;
$prevent_price_manipulation$ language plpgsql;

create trigger prevent_price_manipulation
    before update on offer
    for each row
    execute function prevent_price_manipulation();

Функција и тригер за контрола на времето на важност на понудата

Оваа функција враќа тригер кој спречува прифаќање на понуди кои се постари од 5 минути од моментот на нивното креирање. Доколку се направи обид за промена на статусот од pending во accepted по истекот на овој рок, статусот на понудата автоматски се менува во expired и истата не може да биде прифатена. Оваа логика овозможува динамичност на пазарот, со цел возачите и диспечерите да не бидат долго блокирани чекајќи одговор од патникот.

create or replace function enforce_offer_validity()
    returns trigger as
$enforce_offer_validity$
begin
    if OLD.status = 'pending' and NEW.status = 'accepted' then
      if (NOW() - OLD.created_at) > interval '5 minutes' then
            NEW.status := 'expired';
            raise notice 'Offer % has expired and cannot be accepted.', OLD.id;
            return NEW;
        end if;
    end if;

    return NEW;
end;
$enforce_offer_validity$ language plpgsql;

create trigger trg_enforce_offer_validity
    before update on offer
    for each row
    execute function enforce_offer_validity();