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)