Grundlegende Möglichkeiten von PL/pgSQL

Also, lass uns mal tiefer eintauchen, was PL/pgSQL so mächtig und unersetzlich für Entwickler:innen und Datenbank-Admins macht. In dieser Vorlesung sprechen wir über die Vorteile von PL/pgSQL, seine einzigartigen Features und schauen uns Beispiele an, wie diese Features im echten Leben nützlich sein können.

Um zu verstehen, warum wir PL/pgSQL brauchen, stell dir vor, du bist in einer Welt, in der jede Programmieraufgabe nur mit SQL erledigt werden muss. Zum Beispiel: Um die Anzahl der Studierenden pro Fakultät zu zählen, müsstest du einen komplizierten SQL-Query schreiben und dann die Ergebnisse auf der Client-Seite verarbeiten. Nicht gerade effizient, oder? Genau hier kommt PL/pgSQL ins Spiel – mit Unterstützung für Variablen, Schleifen, Bedingungen und Fehlerbehandlung.

Vorteile von PL/pgSQL:

1. Logik auf der Serverseite: PL/pgSQL reduziert die Menge an Daten, die zwischen Server und Client übertragen werden, weil die ganze Logik auf dem Server läuft. Das senkt die Netzwerklatenz.
2. Performance: Funktionen in PL/pgSQL werden kompiliert und in der Datenbank gespeichert, was sie schneller macht als eine Serie einzelner SQL-Queries.
3. Automatisierung von Aufgaben: Mit PL/pgSQL kannst du Routineaufgaben automatisieren, wie z.B. Datenaktualisierung, Logging oder Integritätsprüfungen.
4. Business-Logik: PL/pgSQL ermöglicht die Umsetzung komplexer Business-Logik, wie Berechnungen, Prüfungen oder das Erstellen von Analyse-Reports.
5. Bequemlichkeit und Lesbarkeit: PL/pgSQL-Code lässt sich easy strukturieren, in Funktionen aufteilen und verbessern – das macht ihn super wartbar.

Einsatzgebiete von PL/pgSQL

Jetzt schauen wir uns mal an, wo genau man PL/pgSQL einsetzen kann und wie es echte Probleme löst.

1. Automatisierung von Routineaufgaben

PL/pgSQL macht es easy, wiederkehrende Aufgaben zu automatisieren. Zum Beispiel musst du täglich bestimmte Daten aktualisieren oder regelmäßig Analysen starten. Mit einer PL/pgSQL-Funktion kannst du das easy mit einem Task-Scheduler (wie pg_cron) zu festen Zeiten erledigen.

Beispiel: Automatisches Status-Update

CREATE FUNCTION update_student_status() RETURNS VOID AS $$
BEGIN
    UPDATE students
    SET status = 'inaktiv'
    WHERE last_login < NOW() - INTERVAL '1 year';
    RAISE NOTICE 'Studentenstatus aktualisiert.';
END;
$$ LANGUAGE plpgsql;

Diese Funktion setzt zum Beispiel automatisch den Status "inaktiv" für Studierende, die seit über einem Jahr nicht mehr eingeloggt waren.

2. Report-Generierung

PL/pgSQL ist super für Analyse-Reports, bei denen Daten aus mehreren Tabellen aggregiert und zusammengeführt werden müssen. Du kannst Prozeduren schreiben, die Reports automatisch generieren und in eigene Tabellen schreiben.

Beispiel: Report zur Anzahl der Studierenden pro Fakultät

CREATE FUNCTION generate_faculty_report() RETURNS TABLE (faculty_id INT, student_count INT) AS $$
BEGIN
    RETURN QUERY
    SELECT faculty_id, COUNT(*)
    FROM students
    GROUP BY faculty_id;
END;
$$ LANGUAGE plpgsql;

Wenn du diese Funktion aufrufst, bekommst du die Statistik für alle Fakultäten.

3. Logging von Änderungen in Tabellen

Logging bedeutet, Änderungen an Daten in Datenbanktabellen zu protokollieren. PL/pgSQL macht das easy, zum Beispiel mit Triggern.

Beispiel für eine Logging-Funktion

CREATE FUNCTION log_changes() RETURNS TRIGGER AS $$
BEGIN
    INSERT INTO change_logs(table_name, operation, old_data, new_data, changed_at)
    VALUES (TG_TABLE_NAME, TG_OP, ROW_TO_JSON(OLD), ROW_TO_JSON(NEW), NOW());
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

Diese Funktion schreibt in die Tabelle change_logs, welche Tabelle geändert wurde, welche Operation (z.B. INSERT, UPDATE, DELETE) ausgeführt wurde und speichert die alten und neuen Daten.

4. Umsetzung komplexer Algorithmen

Mit PL/pgSQL kannst du Algorithmen programmieren, die über das hinausgehen, was Standard-SQL kann. Zum Beispiel Kostenberechnungen, Business-Regel-Prüfungen oder automatische ID-Generierung.

Beispiel: Generierung einer eindeutigen ID

CREATE FUNCTION generate_unique_id() RETURNS TEXT AS $$
BEGIN
    RETURN CONCAT('UID-', EXTRACT(EPOCH FROM NOW()), '-', RANDOM()::TEXT);
END;
$$ LANGUAGE plpgsql;

Diese Funktion erstellt eine eindeutige ID, indem sie einen Zeitstempel und eine Zufallszahl anhängt.

5. Arbeiten mit Triggern

Trigger und PL/pgSQL sind ein Dream-Team. Wenn du Aufgaben automatisieren willst, wie z.B. das Aktualisieren von verknüpften Daten, sind Trigger mit PL/pgSQL-Funktionen das perfekte Tool.

Beispiel: Trigger beim Löschen von Studierenden

CREATE FUNCTION handle_delete_students() RETURNS TRIGGER AS $$
BEGIN
    DELETE FROM enrollments WHERE student_id = OLD.id;
    RAISE NOTICE 'Einschreibungen für Student % gelöscht.', OLD.id;
    RETURN OLD;
END;
$$ LANGUAGE plpgsql;

Mit dieser Funktion kannst du zum Beispiel automatisch die Einschreibungen aus der Tabelle enrollments löschen, wenn ein Student aus der Tabelle students entfernt wird.

6. Fehlerbehandlung

Gerade bei komplexen Aufgaben ist Fehlerbehandlung super wichtig. PL/pgSQL bietet den EXCEPTION-Block, mit dem du Fehler abfangen und behandeln kannst.

Beispiel: Fehlerbehandlung

CREATE FUNCTION insert_student(name TEXT, faculty_id INT) RETURNS VOID AS $$
BEGIN
    INSERT INTO students(name, faculty_id) VALUES (name, faculty_id);
EXCEPTION
    WHEN FOREIGN_KEY_VIOLATION THEN
        RAISE NOTICE 'Fakultäts-ID % existiert nicht!', faculty_id;
END;
$$ LANGUAGE plpgsql;

Wenn hier ein Fehler auftritt, weil die Fakultäts-ID nicht existiert, gibt es eine Warnung statt eines Absturzes.

Beispiele für komplexe Aufgaben, die mit PL/pgSQL gelöst werden

Damit du richtig Bock auf PL/pgSQL bekommst, hier ein paar Beispiele für Aufgaben, die damit easy lösbar sind:

1. Automatisches Update von Rabatten im Online-Shop Eine Funktion, die täglich die Rabatte für Produkte aktualisiert, bei denen die Aktion abläuft.

2. Prüfen und Korrigieren von Daten Eine Funktion, die eine Tabelle auf doppelte Einträge prüft und diese entfernt.

3. Schnelles Umschalten von Konfigurationen Eine Funktion, mit der du Systemparameter ändern kannst, z.B. den Betriebsmodus der App umschalten.

Echte Beispiele aus der IT-Welt

PL/pgSQL wird von Millionen Unternehmen weltweit genutzt. Zum Beispiel:

• Online-Shops nutzen Funktionen für Steuerberechnung, automatisches Rabatt-Update und Verkaufsreport-Generierung.
• Banken setzen PL/pgSQL ein, um tausende Transaktionen pro Tag zu verarbeiten – von Zinsberechnung bis Kreditwürdigkeitsprüfung.
• Soziale Netzwerke implementieren komplexe Datenverarbeitungs-Algorithmen, z.B. für Freundschaftsempfehlungen.

PL/pgSQL ist so etwas wie das Schweizer Taschenmesser für alle, die mit PostgreSQL arbeiten. Es macht nicht nur die Arbeit mit der Datenbank einfacher, sondern ermöglicht auch Aufgaben, die mit normalem SQL entweder super kompliziert oder gar nicht möglich wären. Und das Beste: PL/pgSQL ist easy zu lernen – damit kann sich echt jede:r wie ein echter Datenbank-Profi fühlen.