DatabaseProgramming: CancelFlight.sql

create or replace procedure CancelFlight(
    FlightID_P int,
    CancellationReason text
) as $$
declare
    FlightDeparture timestamp;
    CanceledFlightStatus int;
    CanceledTicketStatus int;
begin
    select id into strict CanceledFlightStatus
    from flightstatus where name = 'Cancelled';

    select id into strict CanceledTicketStatus
    from ticketstatus where name = 'Canceled Flight';

    select departure
    into FlightDeparture
    from flight
    where id = FlightID_P
    for update;

    if not found then
        raise exception 'Flight does not exist!';
    end if;

    if exists (select 1 from canceledflight where flightid = FlightID_P) then
        raise exception 'Flight is already canceled!';
    end if;

    if FlightDeparture < now() + interval '30 minutes' then
        raise exception 'Flight has already departed (or is too close to departure)!';
    end if;

    update flight
    set statusid = CanceledFlightStatus
    where id = FlightID_P;

    insert into canceledflight (flightid, reason, cancellationtime)
    values (FlightID_P, CancellationReason, now());

    update ticket
    set ticketstatusid = CanceledTicketStatus
    where flightid = FlightID_P;
end;
$$ language plpgsql;