Funktionsaufrufe aus SQL-Queries

Stell dir vor, du hast eine Funktion für komplexe Berechnungen oder Datenverarbeitung. Ohne die Möglichkeit, die Funktion in SQL-Queries einzubauen, würdest du so arbeiten:

1. Die Funktion aus einer Programmiersprache (zum Beispiel Python oder JavaScript) aufrufen.
2. Das Ergebnis in die SQL-Query übergeben.

Das ist ein unnötiger Schritt! In PostgreSQL kannst du Funktionen direkt in SQL-Queries einbauen, was den Code kürzer macht, die Operationen beschleunigt und die Anzahl der Server-Calls reduziert. Das ist besonders praktisch für:

• Automatisierung von Berechnungen.
• Datenvalidierung vor dem Einfügen.
• Modifikation von bestehenden Daten.

Funktionsaufruf in SELECT

Lass uns mit den Basics starten und anschauen, wie man Funktionen in einer normalen SELECT-Query nutzt. Angenommen, wir haben eine Tabelle students mit Infos über Studierende. Wir wollen eine Funktion schreiben, die das aktuelle Alter eines Studenten basierend auf seinem Geburtsdatum zurückgibt.

Schritt 1: Funktion schreiben

Wir erstellen die Funktion calculate_age, die ein Geburtsdatum nimmt und das Alter zurückgibt:

CREATE OR REPLACE FUNCTION calculate_age(birth_date DATE) RETURNS INT AS $$
BEGIN
    RETURN DATE_PART('year', AGE(NOW(), birth_date))::INT;
END;
$$ LANGUAGE plpgsql;

Schritt 2: Funktion in der SELECT-Query nutzen

Jetzt können wir die Funktion für jeden Datensatz in der Tabelle aufrufen:

SELECT id, name, calculate_age(birth_date) AS age FROM students;

Was passiert hier?

• Für jede Zeile aus der Tabelle students berechnet die Funktion calculate_age das Alter.
• Der zurückgegebene Wert wird in der Spalte age angezeigt.

Beispielergebnis:

Wie du siehst, ist das gar nicht schwer und das Ergebnis sieht schick und professionell aus.

Funktionsaufruf in INSERT

Funktionen sind auch beim Einfügen von Daten praktisch. Stell dir vor, wir haben eine Tabelle logs, in der User-Aktionen gespeichert werden. Wir wollen eine Log-Nachricht einfügen und dabei eine Funktion nutzen, die den Text automatisch generiert.

Schritt 1: Funktion erstellen

Wir schreiben die Funktion generate_log_message, die einen Usernamen und eine Aktion nimmt und den Nachrichtentext zurückgibt:

CREATE OR REPLACE FUNCTION generate_log_message(username TEXT, action TEXT) RETURNS TEXT AS $$
BEGIN
    RETURN username || ' hat Aktion ausgeführt: ' || action || ' um ' || NOW();
END;
$$ LANGUAGE plpgsql;

Schritt 2: Funktion im INSERT nutzen

Jetzt fügen wir eine Nachricht in die Tabelle logs ein und rufen dabei die Funktion beim Hinzufügen der Zeile auf:

INSERT INTO logs (message)
VALUES (generate_log_message('Otto', 'login auf der Seite'));

Ergebnis:

Die Funktion übernimmt alles: Sie kümmert sich um das Textformat und fügt den Zeitstempel hinzu. Das ist ein super Beispiel, wie Funktionen Routineaufgaben automatisieren können.

Funktionsaufruf in UPDATE

Funktionen können auch zur Modifikation von Daten in einer Tabelle genutzt werden. Angenommen, wir haben die Tabelle students und wollen den Gruppennamen updaten, indem wir eine Funktion nutzen, die sie auf das nächste Semester "befördert".

Schritt 1: Funktion erstellen

Wir schreiben die Funktion promote_student, die eine alte Gruppe (zum Beispiel 101) nimmt und eine neue zurückgibt (zum Beispiel 201):

CREATE OR REPLACE FUNCTION promote_student(old_group TEXT) RETURNS TEXT AS $$
BEGIN
    RETURN '2' || RIGHT(old_group, LENGTH(old_group) - 1);
END;
$$ LANGUAGE plpgsql;

Schritt 2: Funktion im UPDATE nutzen

Wir aktualisieren für alle Studierenden die Gruppe:

UPDATE students
SET group_name = promote_student(group_name);

Ergebnis:

Schau mal, wie die aufgerufene Funktion das Update zaubert: Die alten Gruppen werden in neue Strings umgewandelt.

Funktionsaufruf in WHERE-Bedingungen

Funktionen können auch in Filterbedingungen genutzt werden. Lass uns unser Beispiel mit dem Alter der Studierenden erweitern.

Schritt 1: Nach Alter filtern

Wir nutzen die bereits erstellte Funktion calculate_age, um Studierende auszuwählen, die älter als 20 sind:

SELECT id, name, birth_date
FROM students
WHERE calculate_age(birth_date) > 20;

Ergebnis:

Hier macht die Funktion die Hauptarbeit und berechnet für jeden Studenten das Alter on the fly.

Kombination mit Aggregatfunktionen

Jetzt machen wir es etwas spannender. Wir wollen die Gesamtzahl der Studierenden unter 22 Jahren zählen. Funktionen lassen sich super mit Aggregatfunktionen wie COUNT() kombinieren.

SELECT COUNT(*)
FROM students
WHERE calculate_age(birth_date) < 22;

Was passiert hier?

• Die Funktion calculate_age wird zum Filtern genutzt.
• COUNT(*) zählt die Zeilen, die die Bedingung erfüllen.

Echte Anwendungsbeispiele

Automatisierte Datenvalidierung. Angenommen, du willst checken, dass das Alter aller Studierenden in einem typischen Bereich liegt (zum Beispiel von 18 bis 30 Jahren). Schreib eine Funktion zum Prüfen und nutze sie in der WHERE-Bedingung.

SELECT id, name
FROM students
WHERE NOT (calculate_age(birth_date) BETWEEN 18 AND 30);

Optimierung beim Einfügen von Daten. Stell dir vor, du arbeitest in einem Online-Shop. Anstatt den Gesamtpreis der Bestellung auf der Client-Seite zu berechnen, schreib eine Funktion, die ihn direkt beim Hinzufügen der Daten in die Tabelle orders berechnet.

INSERT INTO orders (user_id, total_price)
VALUES (1, calculate_total_price(ARRAY[5, 10, 15]));

Typische Fehler beim Funktionsaufruf

Wenn du anfängst, Funktionen in Queries aktiv zu nutzen, können Fehler auftreten. Hier ein paar typische Situationen und wie du sie löst:

Fehlende Berechtigungen. Wenn du nicht der Owner der Funktion oder Tabelle bist, kann PostgreSQL den Aufruf verbieten. Check, ob du die Rechte zum Ausführen der Funktion hast.

Falsche Datentypen. Achte beim Übergeben von Argumenten an die Funktion auf die Datentypen. Wenn die Funktion zum Beispiel DATE erwartet, du aber einen String übergibst, gibt's einen Fehler. Nutze explizite Typumwandlung:

SELECT calculate_age('2000-01-01'::DATE);

Syntaxfehler in der Funktion. Wenn die Funktion einen Fehler zurückgibt, kann die ganze Query crashen. Teste deine Funktionen gründlich, bevor du sie einsetzt.