7. Tower
Im Tower unseres Flughafens wird die Einhaltung der Daten aus der Datenbank im Flugverkehr überwacht. Dazu werden die Daten gesondert aufbereitet. Eine Möglichkeit bietet SQL durch Verwendung von Sichten (Views). Eine Sicht ist eine gespeicherte SELECT-Anweisung, die bei der Abfrage erneut ausgeführt wird. Es gibt auch noch materialisierte Sichten, in denen die Ergebnisse einer Sicht wie in einer Tabelle vorgehalten werden - für PostgreSQL derzeit aber nicht. Folgende Sicht gibt die Übersicht über die Abflüge und Ankünfte innerhalb der nächsten Stunde.
CREATE VIEW timetable AS
SELECT 'departure' AS dir, "from" AS airport, airline_id AS airline, "from", departure, "to", arrival FROM flights WHERE departure BETWEEN now() AND now()+ interval '1 hour'
UNION
SELECT 'arrival' AS dir, "to" AS ariport, airline_id AS airline, "from", departure, "to", arrival FROM flights WHERE arrival BETWEEN now() AND now() + interval '1 hour';
Eine Sicht wird mit dem DDL-Befehl CREATE VIEW
erstellt. Dem Befehl folgt der Name, timetable. Nach dem Schlüsselwort AS
ist hier die Vereinigungsmenge zweier SELECT-Anweisungen mittels des
Operators UNION angegeben. Dazu werden zunächst beide SELECT-Anweisungen
einzeln ausgeführt. Es ergeben sich zwei Ergebnismengen, die vereinigt
werden. Die Auswahl der Flüge für die nächste Stunde geschieht durch die
WHERE-Bedingungen. Der BETWEEN-Operator gibt ein Zeitintervall vor. Die
untere Schranke ist der Zeitpunkt der Ausführung der SELECT-Anweisung - zu
diesem wird die Funktion now() aufgerufen. Die obere Schranke ist
ebenfalls der Zeitpunkt der Ausführung der SELECT-Anweisung zzgl. des
Zeitintervalls von einer Stunde. Zur Markierung der ein- und abgehenden
Flüge werden die Flüge im Feld dir mit departure oder arrival
markiert.
Zum Löschen der Sicht wird der Befehl DROP VIEW
verwendet.
DROP VIEW timetable;
Sichten können wie Tabellen abgefragt werden. Um die Abflüge und
Ankünfte eines Flughafens zu betrachten werden nur Abflüge und Ankünfte an
dem Flughafen mit der airport_id = 'TXL'
betrachtet.
SELECT * FROM timetable WHERE airport = 'TXL';
direction | local_airport | flight_number | airline_id | from | departure | to | arrival | seats | price
-----------+---------------+---------------+------------+------+-----------+----+---------+-------+-------
(0 rows)
Damit die Ergebnismenge nicht leer bleibt, definieren wir eine Funktion, die alle Abflüge der Tabelle flights in die nächste Stunde legt und die Ankünfte entsprechend der Flugzeit anpasst.
CREATE FUNCTION update_flights()
RETURNS void
AS
$$
UPDATE flights
SET departure = current_timestamp + (departure - date_trunc('hour', departure)),
arrival = current_timestamp + (departure - date_trunc('hour', departure))
+ (arrival - departure);
$$ LANGUAGE sql;
Funktionen werden in PostgreSQL mittels des DDL-Befehls
CREATE FUNCTION
erstellt und permanent im
Datenbanksystem gespeichert. Darum werden die Funktionen häufig auch als
Stored Procedures bezeichnet (s. Oracle). Bei Bedarf werden die Funktionen
"compiliert". Dem Namen der Funktion, update_flights, folgt die Liste der
Funktionsparameter. Die Funktion erwartet keine Aufrufparameter. Da die
Funktion keine Ergebnismenge zurückgibt, wird nach dem Schlüsselwort
RETURNS void angegeben. Der Rumpf der Funktion kann für PostgreSQL in
verschiedenen Sprachen implementiert werden: SQL, PL/pgSQL, PL/Tcl,
PL/Perl und PL/Python. Der Rumpf der Funktion wird durch die Zeichenfolgen
$$ (dollar quoted string) begrenzt. Er enthält im Falle der Funktion
update_flights eine einzelne SQL-Anweisung. Wir vereinbaren als Sprache
SQL. Dazu notieren wir LANGUAGE sql
am Ende der
Definition.
Die UPDATE-Anweisung im Rumpf der Funktion setzt den Abflug eines jeden Fluges auf die aktuelle Zeitangabe, current_timestamp. Zu diesem Wert wird der Minuten-Anteil aus dem ursprünglichen Abflug hinzuaddiert. Dazu wird die Abflugszeit jeweils auf die begonnene Stunde abgerundet. Dies geschieht durch die Funktion date_trunc mit der Angabe hour im ersten Argument. Der Minutenanteil ergibt sich nun aus der Differenz der Abflugszeit mit der abgerundeten Abflugszeit. Für die neue Ankunftszeit wird zu dieser Angabe noch die Flugzeit hinzuaddiert. Wir führen die Funktion mittels einer SELECT-Anweisung aus.
SELECT update_flights();
update_flights
----------------
(1 row)
Anschließend fragen wir nochmals die Sicht timetable für einen Flughafen ab.
SELECT * FROM timetable WHERE airport = 'TXL';
dir | airport | airline | from | departure | to | arrival
-----------+---------+---------+------+---------------------+-----+---------------------
departure | TXL | AB | TXL | 2011-04-27 18:09:19 | GRZ | 2011-04-27 19:29:19
departure | TXL | AB | TXL | 2011-04-27 18:54:19 | PMI | 2011-04-27 21:29:19
(2 rows)
Zum Löschen der Funktion verwenden wir den DDL-Befehl DROP
FUNCTION
.
DROP FUNCTION update_flights();
Zur Betankung der Flugzeuge müssen die geflogenen Distanzen zwischen Start und Ziel bekannt sein. Unsere Erde haben wir der Einfachheit halber als Kugel mit einem Radius von 6730 km beschrieben. Die kürzeste Distanz zwischen zwei Punkten auf der Kugeloberfläche wird durch einen Teil eines Großkreisbogens beschrieben (Orthodrom). Orthodrome werden in unserem Beispiel als Routen genutzt. Zur Berechnung der Länge einer Route definieren wir folgende Funktion.
CREATE FUNCTION orthodromic_distance(location, location)
RETURNS float
AS
$$
DECLARE
v_from ALIAS FOR $1;
v_to ALIAS FOR $2;
v_result float;
BEGIN
IF ($1=$2) THEN
v_result := 0.0;
ELSE
v_result := acos(
sin(radians(v_from.latitude))*sin(radians(v_to.latitude))
+cos(radians(v_from.latitude))*cos(radians(v_to.latitude))
*cos(radians(v_to.longitude)-radians(v_from.longitude))
)*6370.0;
END IF;
RETURN v_result;
END;
$$ LANGUAGE plpgsql;
Zur Definition der Funktion verwenden wir wiederum den DDL-Befehls
CREATE FUNCTION
. Dem Namen der Funktion,
orthodromic_distance, folgt die Liste der Funktionsparameter. Die
Funktion erwartet zwei Ortsangaben vom Typ location
. Der
Rückgabewert, nach Schlüsselwort RETURNS, ist eine reelle Zahl vom Typ
float
. Der Rumpf der Funktion ist in der PostgreSQL eigenen
prozeduralen Sprache PL/pgSQL (Procedural Language pgSQL)
implementiert.
Sind die beiden Ortsangaben aus dem Funktionsaufruf
orthodromic_distance identisch, so wird als Distanz 0.0 zurückgegeben. Der
Vergleichsoperator = vergleicht in diesem Fall komponentenweise.
Andernfalls wird über die Formel im ELSE-Zweig der IF-Anweisung die Länge
des Orthodroms in Kilometern berechnet und zurückgegeben. Die Funktion
radians rechnet die Grad-Zahl in Bogenmaß um. Die Parameter aus dem
Funktionsaufruf werden im DECLARE-Block durch den Alias-Mechanismus in
lokale Variablen übersetzt. Die Werte aus dem Funktionsaufruf werden über
$1 und $2, entsprechend ihrer Position in der Parameterliste,
dereferenziert. Zum Entfernen der Funktion wird der DDL-Befehl
DROP FUNCTION
genutzt.
DROP FUNCTION orthodromic_distance(location, location);
Da PostgreSQL die Überladung von Funktionen erlaubt, ist die Angabe der Parameterliste erforderlich. Um eine Übersicht über die Abstände zwischen allen Flughäfen zu generieren, erstellen wir folgende Sicht unter Zuhilfenahme eines Kartesischen Produktes.
CREATE VIEW airports_distance AS
SELECT a.airport_id AS "from", b.airport_id AS "to", orthodromic_distance(a.location, b.location) AS distance
FROM airports a, airports b;
Die SELECT-Anweisung verbindet die Tabelle airports mit sich selber, genannt SELF JOIN, ohne eine JOIN-Bedingung zu nutzen. Deshalb wird jedem Datensatz der Tabelle airports genau einmal jeder Datensatz aus der gleichen Tabelle zugeordnet. Ist n die Dimension der Tabelle (Anzahl von Zeilen), so wird die Ergebnismenge n*n Zeilen umfassen.
SELECT * FROM airports_distance LIMIT 10;
from | to | distance
------+-----+------------------
TXL | TXL | 0
TXL | LAX | 9319.42892823799
TXL | PMI | 1655.85872296913
TXL | GRZ | 619.751073066838
TXL | FRA | 434.422849092028
TXL | SYD | 16100.8622060376
LAX | TXL | 9319.42892823799
LAX | LAX | 0
LAX | PMI | 9850.19095355263
LAX | GRZ | 9888.90382700642
(10 rows)
Um die Größe der Abfrage einzuschränken verwendet die
SELECT-Anweisung die Angabe LIMIT 10
. Die Ausgabe wird auf 10
Datensätze beschränkt. Zur Berechnung des Abstandes wird für je zwei
Ortsangaben aus einem Datensatz die Funktion orthodromic_distance
aufgerufen. Wir wollen nun den Tankplan für den nächsten Tag berechnen.
Zunächst definieren wir einen Datentypen, der den Rückgabewert der
Funktion beschreibt.
CREATE TYPE fuel AS (
flight_number varchar,
"from" char(3),
"to" char(3),
fuel float
);
PL/pgSQL bietet bekannte Sprachkonstrukte einer Programmiersprache,
wie Verzweigungen, Schleifen, Variablen, sowie die Verwendung
datenbankspezifischer Konstrukte wie Cursor. PL/pgSQL ermöglicht
Rekursionen sowie die Ausführung dynamischen SQLs mittels des Befehls
EXECUTE
. PL/pgSQL Programmtexte werden in Blöcke
unterteilt. In einem DECLARE-Block können lokale Variablen deklariert
werden, die im Folgenden BEGIN-END-Block sichtbar sind. Zur
Fehlerbehandlung bietet PL/pgSQL die Behandlung von exceptions an.
CREATE FUNCTION add_fuel(v_airport char, v_limit float, v_miles float)
RETURNS SETOF fuel
AS
$$
DECLARE
v_total float := 0.0;
v_rec record;
BEGIN
BEGIN
FOR v_rec IN SELECT flight_number, "from", "to", v_miles*orthodromic_distance("from_airport".location, "to_airport".location) AS fuel
FROM flights JOIN airports "from_airport" ON ("from" = airport_id)
JOIN airports "to_airport" ON ("to" = to_airport.airport_id)
WHERE "from" = v_airport
AND departure BETWEEN current_date AND current_date + interval '1 day'
LOOP
v_total := v_total + v_rec.fuel;
IF v_total > v_limit THEN
RAISE WARNING 'order new fuel for flight %', v_rec.flight_number;
END IF;
RETURN NEXT v_rec;
END LOOP;
EXCEPTION
WHEN others THEN
RAISE WARNING 'error detected: % (%)', SQLERRM, SQLSTATE;
END;
IF v_total > v_limit THEN
RAISE WARNING 'fuel limit reach, order % galons of kerosine', (v_total - v_limit);
END IF;
RETURN;
END;
$$ LANGUAGE plpgsql;
Diese Funktion summiert den erforderlichen Kerosinverbrauch an einem
Flughafen für den nächsten Tag mittels einer FOR-Schleife. Die Funktion
erwartet als Parameter die Kennung des Flughafens, die gelagerte
Kerosinmenge und den Kerosinverbrauch pro geflogenen Flugkilometer. In der
Parameterliste dieser Funktion werden explizit Namen für die Parameter
vereinbart. Die Dereferenzierung mittels der Bezeichner $1, $2, etc.
entfällt deswegen. Als Rückgabewert verwendet die Funktion eine Menge,
SETOF, Werte vom Datentyp fuel
.
Die FOR-Schleife erstreckt sich über die Ergebnismenge der
SQL-Anweisung nach dem Schlüsselwort IN. Der Block innerhalb der
FOR-Schleife wird für jeden Datensatz der Ergebnismenge einmalig
ausgeführt. Dazu werden die Felder eines Datensatzes in Komponenten der
record-Variable v_rec überführt. Der PL/pgSQL Datentyp record
ist flexibel und kann während der Laufzeit mit weiteren Komponenten
versehen werden.
Den Variablennamen ist der Präfix "v_" vorangestellt, um eine
Überschneidung mit den Feldnamen von Tabellen oder Sichten zu vermeiden.
Dies wird offenkundig für die Parametrisierung der SELECT-Anweisung. Dort
wird in der SQL-Anweisung einfach der Wert der Variablen v_airport in dem
Ausdruck WHERE "from" = v_airport
zur Ausführung der
Anweisung ersetzt.
Die Variable v_total summiert den Kerosinverbrauch aller Flüge. Ist die am Flughafen gelagerte Menge an Kerosin, v_limit, überschritten, so wird eine Warnung für die Flüge ausgegeben, für die voraussichtlich kein Kerosin mehr vorhanden ist. Jeder Datensatz wird mit einer RETURN NEXT-Anweisung zunächst in einen internen Stapelspeicher abgelegt. Die Funktion wird erst mittels RETURN, ohne Argument, beendet. Danach wird die Ergebnismenge zurückgegeben.
Jeder Fehler, der im Block der FOR-Schleife auftritt, wird als
Warnung gemeldet. Dies geschieht im exception-Block für die
Fehlerkategorie, others (alle Fehler) durch
Verwendung des Befehles RAISE
. Der Wert der
Fehlermeldung und des Fehlercodes werden aus den im Exception-Block
definierten Variablen SQLEERM und
SQLSTATE ausgelesen und in die Zeichenkette
'error detected: % (%)'
für die Prozentzeichen eingesetzt.
Das Niveau der RAISE-Anweisung wird auf WARNING gesetzt.
SELECT * FROM add_fuel('TXL', 100000.00, 50.00);
HINWEIS: order new fuel for flight AB 9172
WARNUNG: fuel limit reach, order 13780 galons of kerosine
flight_number | from | to | fuel
---------------+------+-----+------------------
AB 8550 | TXL | GRZ | 30987.5536533419
AB 9172 | TXL | PMI | 82792.9361484566
(2 rows)
Zur Berechnung der von den Fluggesellschaften einzuziehenden Steuern verwendet die Betreibergesellschaft eine Funktion, die die Steuern pro Fluggesellschaft berechnet.
CREATE FUNCTION taxes(char, char, float)
RETURNS TABLE(airline varchar, tax float, total bigint)
AS
$$
SELECT al."name",
coalesce(count(*), 0.0)*coalesce($3, 0.0) AS tax,
count(flight_number) AS total
FROM airlines al
LEFT JOIN flights USING (airline_id)
LEFT JOIN passangers USING (flight_number)
WHERE airline_id = $2
AND airport_id = $1
GROUP BY al.name;
$$ LANGUAGE sql;
Die Funktion taxes erwartet als Parameter die airport_id des Flughafens, die airline_id der Fluggesellschaft sowie die Höhe der Steuern pro Fluggast. Als Rückgabewert wird eine Tabelle mit den Feldern Name der Fluggesellschaft, einzuziehende Steuer und Anzahl der Fluggäste vereinbart. Dazu verwendet die RETURNS-Angabe der Funktion folgende Tabellendefinition.
TABLE(airline varchar, tax double precision, total bigint)
Die Tabellendefinition können wir als anonyme Typdefinition begreifen, denn auch Tabellen und Sichten weist PostgreSQL intern einen Datentypen mit einen Namen des jeweiligen Datenbankobjektes zu. So kann ein Tabellenname auch als Datentyp einer Variable in PL/pgSQL genutzt werden. Dieser Zeilentyp (row type) ist aus Komponenten, deren Name und Datentypen, der Tabellendefinition entsprechend zusammengesetzt.
DECLARE
v_flight flights;
BEGIN
v_flight.flight_number := 'AB 0001';
v_flight.airline_id := 'AB';
END
Die Funktion coalesce verhindert die Verwendung des NULL-Wertes in den arithmetischen Ausdrücken. Sie liefert den ersten Parameterwert aus ihrem Aufruf, der vom Wert NULL verschieden ist.
SELECT coalesece(NULL, 0.0) AS "NOT NULL";
"NOT NULL"
----------
0.0
(1 row)
Im folgendem berechnen wir die Steuern für eine Fluggesellschaft am Flughafen Tegel.
SELECT * FROM taxes('TXL', 'AB', 4.99);
airline | tax | total
------------+-------+-------
Air Berlin | 44.91 | 9
(1 row)