DatabaseProgramming: ChangeFlightStatus.sql

create or replace procedure ChangeFlightStatus(
    FlightID_P int,
    StatusID_P int
) as $$
declare
    CurrentStatusID int;
begin
    select statusid
    into CurrentStatusID
    from flight
    where id = FlightID_P;

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

    if not exists(select * from flightstatus where id = StatusID_P) then
        raise exception 'Status does not exist!';
    end if;

    if (select name from flightstatus where id = CurrentStatusID) = 'Arrived' then
        raise exception 'Flight has already arrived!';
    end if;

    if (select name from flightstatus where id = CurrentStatusID) = 'Cancelled' then
        raise exception 'Flight has been canceled!';
    end if;

    update flight
    set statusid = StatusID_P
    where flight.id = FlightID_P;
end;
$$ language plpgsql;