DatabaseProgramming: Board.sql

create or replace procedure Board(TicketID_P int)
as $$
declare
    TicketStatusID_V int;
    TicketFlightID int;
    FlightDeparture timestamp;
    CheckedInStatus int;
    BoardedStatus int;
begin
    select id into strict CheckedInStatus
    from ticketstatus where name = 'Checked In';

    select id into strict BoardedStatus
    from ticketstatus where name = 'Boarded';

    select t.ticketstatusid, t.flightid, f.departure
    into TicketStatusID_V, TicketFlightID, FlightDeparture
    from ticket t
    join flight f on f.id = t.flightid
    where t.id = TicketID_P
    for update of t;

    if not found then
        raise exception 'Ticket not found!';
    end if;

    if TicketStatusID_V = BoardedStatus then
        raise exception 'Ticket already boarded!';
    end if;

    if TicketStatusID_V <> CheckedInStatus then
        raise exception 'Ticket not checked in!';
    end if;

    if exists (select 1 from canceledflight where flightid = TicketFlightID) then
        raise exception 'Flight has been canceled!';
    end if;

    if FlightDeparture > now() + interval '1 hour' then
        raise exception 'Boarding not allowed yet!';
    end if;

    if FlightDeparture < now() + interval '15 minutes' then
        raise exception 'Boarding closed!';
    end if;

    update ticket
    set ticketstatusid = BoardedStatus
    where id = TicketID_P;
end;
$$ language plpgsql;