DatabaseProgramming: FindAlternativeFlights.sql

create or replace function FindAlternativeFlights(
    CancelledFlightID int,
    MaxHours int
) returns table (
    FlightID int,
    FlightNumber varchar(6),
    Departure timestamp,
    Arrival timestamp,
    HoursFromOriginal numeric,
    SeatClass text,
    AvailableSeats bigint
) as $$
declare
    Origin_V char(3);
    Destination_V char(3);
    Departure_V timestamp;
begin
    select dap.code, aap.code, f.departure
    into Origin_V, Destination_V, Departure_V
    from flight f
    join gate dg on dg.id  = f.actualgatedepartureid
    join terminal dt on dt.id  = dg.terminalid
    join airport dap on dap.id = dt.airportid
    join gate ag on ag.id  = f.actualgatearrivalid
    join terminal at_ on at_.id = ag.terminalid
    join airport aap on aap.id = at_.airportid
    where f.id = CancelledFlightID;

    if not FOUND then
        raise exception 'Flight not found!';
    end if;

    return query
    select flight.id, flight.flightnumber, flight.departure, flight.arrival,
           round(extract(epoch from (flight.departure - Departure_V)) / 3600.0, 2),
           seatclass.name::text, count(*) filter (where seat.reserved = false)
    from flight
    join gate dg  on dg.id  = flight.actualgatedepartureid
    join terminal dt  on dt.id  = dg.terminalid
    join airport dap on dap.id = dt.airportid
    join gate ag  on ag.id = flight.actualgatearrivalid
    join terminal at_ on at_.id = ag.terminalid
    join airport  aap on aap.id = at_.airportid
    join seat on seat.flightid = flight.id
    join seatconfiguration on seatconfiguration.id = seat.seatconfigurationid
    join seatclass on seatclass.id = seatconfiguration.seatclassid
    where flight.id <> CancelledFlightID
      and dap.code = Origin_V
      and aap.code = Destination_V
      and flight.departure between Departure_V - make_interval(hours => MaxHours)
                           and Departure_V + make_interval(hours => MaxHours)
      and not exists (select 1 from canceledflight where public.canceledflight.flightid = flight.id)
    group by flight.id, flight.flightnumber, flight.departure, flight.arrival, seatclass.name
    having count(*) filter (where seat.reserved = false) > 0
    order by flight.departure, seatclass.name;
end;
$$ language plpgsql;