create or replace function CalculateDistance()
returns trigger as $$
    begin
    select greatest(1, round(ST_Distance(dep.location, arr.location) / 1000)::int)
    into new.distance
    from slot as ds
    join gate as dg on ds.gateid = dg.id
    join terminal as dt on dg.terminalid = dt.id
    join airport as dep on dep.id = dt.airportid,
         slot as asl
    join gate as ag on asl.gateid = ag.id
    join terminal as at on at.id = ag.terminalid
    join airport as arr on arr.id = at.airportid
    where new.departure = ds.id and
          new.arrival = asl.id and
          dep.location is not null and
          arr.location is not null;

    return new;
    end;
$$
language plpgsql;

create trigger SetDistance
before insert or update of departure, arrival on scheduledflight
for each row execute function CalculateDistance();