DatabaseProgramming: ConnectingFlights.sql

create or replace function ConnectingFlight(
    DepartureCode char(3),
    ArrivalCode char(3),
    Date date
) returns table (
    flight1_Number varchar(6),
    flight1_Departure timestamp,
    flight1_Arrival timestamp,
    connection char(3),
    layoverMinutes int,
    flight2_Number varchar(6),
    flight2_Departure timestamp,
    flight2_Arrival timestamp
) as $$
begin
    return query

    with route as (
        select f.id, f.flightnumber, f.departure, f.arrival, dep_a.code as dep_code,
                ar_a.code as arr_code
        from flight as f
        join gate as dep_g on f.actualgatedepartureid = dep_g.id
        join terminal as dep_t on dep_g.terminalid = dep_t.id
        join airport as dep_a on dep_t.airportid = dep_a.id
        join gate as ar_g on f.actualgatearrivalid = ar_g.id
        join terminal as ar_t on ar_g.terminalid = ar_t.id
        join airport as ar_a on ar_t.airportid = ar_a.id
    )
    select
        f1.flightnumber,
        f1.departure,
        f1.arrival,
        f1.arr_code,
        (extract(epoch from (f2.departure - f1.arrival)) / 60)::int,
        f2.flightnumber,
        f2.departure,
        f2.arrival
    from route as f1
    join route as f2 on f1.arr_code = f2.dep_code
    where f1.dep_code = DepartureCode and
          f2.arr_code = ArrivalCode and
          f1.departure::date = Date and
          f2.departure >= f1.arrival + INTERVAL '1 hour' and
          f2.departure <= f1.arrival + INTERVAL '16 hours';
end;
$$ language plpgsql;