Einfache Funktionen erstellen: CREATE FUNCTION

In PostgreSQL sind Funktionen ein mächtiges Tool, mit dem du Aufgaben automatisieren, Business-Logik bauen und dem Server mehr Intelligenz geben kannst. Stell dir Funktionen wie Mini-Programme vor, die direkt in der Datenbank laufen. Sie sind praktisch für:

• Wiederverwendung von Code. Wenn du immer wieder die gleichen Abfragen schreibst, pack sie in eine Funktion und ruf sie einfach auf, wann immer du willst.
• Automatisierung von Aufgaben. Zum Beispiel willst du das Gehalt von Mitarbeitern basierend auf ihren Arbeitsstunden berechnen. Eine Funktion macht das easy.
• Kapselung von Logik. So bleibt die komplizierte Logik auf dem Server und die Clients müssen sich nicht mit komplexen SQL-Queries rumschlagen.

Allgemeiner Syntax von CREATE FUNCTION

So sieht die Grundstruktur für das Erstellen einer Funktion aus:

CREATE FUNCTION function_name(parameters) RETURNS return_type AS $$
BEGIN
    -- Funktionskörper (Logik)
    RETURN ergebnis;
END;
$$ LANGUAGE plpgsql;

Schauen wir uns die wichtigsten Teile an:

CREATE FUNCTION function_name(parameters):

Hier gibst du den Namen der Funktion function_name an und die Parameter (falls nötig).

Parameter können Name und Datentyp enthalten: my_param INTEGER, another_param TEXT.

RETURNS return_type:

Hier sagst du, was deine Funktion zurückgibt: einen Wert (INTEGER, TEXT usw.) oder einen Datensatz (TABLE, RECORD).

BEGIN ... END:

Zwischen diesen Schlüsselwörtern steht der "Körper" der Funktion, wo die Magie passiert.

RETURN ergebnis:

Gibt das Ergebnis der Funktion zurück. Achtung: Der Rückgabetyp muss zu dem passen, was du bei RETURNS angegeben hast.

LANGUAGE plpgsql:

Hier gibst du an, dass du PL/pgSQL verwendest. PostgreSQL kann auch andere Sprachen, aber wir brauchen jetzt genau diese.

Einfaches Beispiel: Zwei Zahlen addieren

Lass uns eine Funktion schreiben, die die Summe von zwei Ganzzahlen zurückgibt.

CREATE FUNCTION add_numbers(a INT, b INT) RETURNS INT AS $$
BEGIN
    RETURN a + b;
END;
$$ LANGUAGE plpgsql;

Jetzt rufen wir sie auf:

SELECT add_numbers(5, 7); -- Ergebnis: 12

Was passiert hier?

• Die Funktion nimmt zwei Parameter a und b vom Typ INT.
• Im Funktionskörper addieren wir sie einfach (a + b) und geben das Ergebnis zurück.
• Easy wie ein Taschenrechner!

Beispiel mit Variablen

Stell dir vor, wir haben eine Uni-Datenbank und wollen wissen, wie viele Studenten registriert sind.

Wir schreiben eine Funktion:

CREATE FUNCTION count_students() RETURNS INT AS $$
DECLARE
    total INT; -- Variable für das Ergebnis deklarieren
BEGIN
    SELECT COUNT(*) INTO total FROM studenten; -- Zeilen in der Tabelle zählen
    RETURN total; -- Ergebnis zurückgeben
END;
$$ LANGUAGE plpgsql;

Funktionsaufruf:

SELECT count_students(); -- Angenommen, Ergebnis: 120

Hier sehen wir:

• Die Variable total wird genutzt, um das Ergebnis der SQL-Abfrage zu speichern.
• Mit SELECT ... INTO landet das Abfrageergebnis in der Variable.

Das ist besonders praktisch, wenn du Daten erst bearbeiten und dann zurückgeben willst.

Mehrere Werte zurückgeben: RETURNS TABLE

Im letzten Beispiel haben wir nur einen Wert zurückgegeben. Aber was, wenn unsere Funktion eine ganze Datenmenge liefern soll, z.B. eine Liste von Studenten? Genau dafür ist RETURNS TABLE da.

Beispiel:

CREATE FUNCTION get_students() RETURNS TABLE(id INT, name TEXT) AS $$
BEGIN
    RETURN QUERY SELECT id, name FROM studenten;
END;
$$ LANGUAGE plpgsql;

Funktionsaufruf:

SELECT * FROM get_students();

Mögliches Ergebnis:

Warum RETURN QUERY praktisch ist

RETURN QUERY erlaubt es dir, das Ergebnis einer SQL-Abfrage direkt aus der Funktion zurückzugeben. Das spart Zwischenschritte und macht die Funktion schlanker.

Wir schreiben eine Funktion, die nur aktive Studenten zurückgibt:

CREATE FUNCTION get_active_students() RETURNS TABLE(id INT, name TEXT) AS $$
BEGIN
    RETURN QUERY SELECT id, name FROM studenten WHERE aktiv = TRUE;
END;
$$ LANGUAGE plpgsql;

Bevor du get_active_students() aufrufst, musst du die Tabelle studenten anlegen und mit Testdaten füllen. So geht's:

-- Tabelle studenten anlegen
CREATE TABLE studenten (
    id SERIAL PRIMARY KEY,
    name TEXT NOT NULL,
    aktiv BOOLEAN DEFAULT TRUE
);

-- Ein paar Einträge hinzufügen
INSERT INTO studenten (name, aktiv) VALUES
('Alice', FALSE),
('Bob', TRUE),
('Charlie', TRUE),
('Dana', FALSE);

Tabelle:

Jetzt der Aufruf:

SELECT * FROM get_active_students();

Ergebnis:

Daten vor der Ausführung prüfen

Funktionen können IF-Prüfungen enthalten, um sicherzustellen, dass die Daten passen. Zum Beispiel können wir eine Funktion schreiben, die einen Studenten nur dann ins nächste Semester versetzt, wenn er alle Prüfungen bestanden hat.

Beispiel:

CREATE FUNCTION promote_student(student_id INT) RETURNS TEXT AS $$
DECLARE
    bestandene_pruefungen INT;
BEGIN
    -- Anzahl bestandener Prüfungen zählen
    SELECT COUNT(*) INTO bestandene_pruefungen
    FROM pruefungen
    WHERE student_id = promote_student.student_id AND status = 'bestanden';

    -- Bedingung prüfen
    IF bestandene_pruefungen < 5 THEN
        RETURN 'Student hat nicht genug Prüfungen bestanden';
    END IF;

    -- Semester des Studenten erhöhen
    UPDATE studenten
    SET semester = semester + 1
    WHERE id = promote_student.student_id;

    RETURN 'Student wurde befördert!';
END;
$$ LANGUAGE plpgsql;

Typische Fehler beim Erstellen von Funktionen

Kein Rückgabetyp angegeben. PostgreSQL verlangt immer, dass du angibst, was die Funktion zurückgibt. Zum Beispiel:

CREATE FUNCTION fail() AS $$ -- Fehler: kein RETURNS
BEGIN
    RETURN 1;
END;
$$ LANGUAGE plpgsql;

Korrektur:

CREATE FUNCTION succeed() RETURNS INT AS $$
BEGIN
    RETURN 1;
END;
$$ LANGUAGE plpgsql;

Falscher Rückgabetyp. Wenn du RETURNS INT angibst, musst du auch eine Zahl zurückgeben. Einen String zurückzugeben ist dann keine gute Idee.

Fehler in SQL-Abfragen innerhalb der Funktion. Teste deine Abfragen immer vorher, bevor du sie in Funktionen packst. Am besten direkt per psql oder pgAdmin.