AdvancedTopics: FlightAtTime.sql

create or replace function FlightAtTime (
    FlightID_P int,
    Time_P timestamp
)  returns table (
    CurrentLocation geography,
    Route geography
                 )
as
$$
    declare
        DepartureTime timestamp;
        ArrivalTime timestamp;
        DepartureLocation geography;
        ArrivalLocation geography;
        Fraction numeric;
        CurrentPosition_V geography;
        Route_V geography;
    begin
        select flight.departure, flight.arrival, ad.location, aa.location
        into DepartureTime, ArrivalTime, DepartureLocation, ArrivalLocation
        from flight
        join gate as ga on flight.actualgatearrivalid = ga.id
        join gate as gd on flight.actualgatedepartureid = gd.id
        join terminal as ta on ta.id = ga.terminalid
        join terminal as td on td.id = gd.terminalid
        join airport as aa on aa.id = ta.airportid
        join airport as ad on ad.id = td.airportid
        where flight.id = FlightID_P;

        if Time_P <= DepartureTime then
            CurrentPosition_V := DepartureLocation;
        elsif Time_P >= ArrivalTime then
            CurrentPosition_V := ArrivalLocation;
        else
            Fraction := EXTRACT(epoch FROM (Time_P - DepartureTime))
              / EXTRACT(epoch FROM (ArrivalTime - DepartureTime));

            CurrentPosition_V := ST_LineInterpolatePoint(
            ST_MakeLine(DepartureLocation::geometry, ArrivalLocation::geometry),
            fraction
            )::geography;
        end if;

        Route_V := ST_MakeLine(
            DepartureLocation::geometry,
            ArrivalLocation::geometry
        )::geography;

        return query select CurrentPosition_V, Route_V;

    end;
$$ language plpgsql;

select * from flight;

select * from FlightAtTime(847585, '2025-12-07 10:00:00.000000'::timestamp)