| 1 | create or replace function CalculateDistance()
|
|---|
| 2 | returns trigger as $$
|
|---|
| 3 | begin
|
|---|
| 4 | select greatest(1, round(ST_Distance(dep.location, arr.location) / 1000)::int)
|
|---|
| 5 | into new.distance
|
|---|
| 6 | from slot as ds
|
|---|
| 7 | join gate as dg on ds.gateid = dg.id
|
|---|
| 8 | join terminal as dt on dg.terminalid = dt.id
|
|---|
| 9 | join airport as dep on dep.id = dt.airportid,
|
|---|
| 10 | slot as asl
|
|---|
| 11 | join gate as ag on asl.gateid = ag.id
|
|---|
| 12 | join terminal as at on at.id = ag.terminalid
|
|---|
| 13 | join airport as arr on arr.id = at.airportid
|
|---|
| 14 | where new.departure = ds.id and
|
|---|
| 15 | new.arrival = asl.id and
|
|---|
| 16 | dep.location is not null and
|
|---|
| 17 | arr.location is not null;
|
|---|
| 18 |
|
|---|
| 19 | return new;
|
|---|
| 20 | end;
|
|---|
| 21 | $$
|
|---|
| 22 | language plpgsql;
|
|---|
| 23 |
|
|---|
| 24 | create trigger SetDistance
|
|---|
| 25 | before insert or update of departure, arrival on scheduledflight
|
|---|
| 26 | for each row execute function CalculateDistance(); |
|---|
