PostgreSQL Stored Procedures: Tutorial mit Beispielen

Einführung

Stored Procedures spielen in PostgreSQL eine wichtige Rolle und bieten einige Vorteile für die Datenbankverwaltung. In diesem Tutorial schauen wir uns an, wie du Stored Procedures in PostgreSQL nutzt, um SQL-Operationen effizienter und sicherer abzuwickeln. Der Artikel richtet sich an Softwareentwickler und Datenbankadministratoren, die ihre Datenbank-Skills mit fortgeschrittenen PostgreSQL-Features ausbauen möchten.

Was ist eine Stored Procedure?

Bevor wir mit den Beispielen loslegen, solltest du das Konzept einer Stored Procedure verstehen. In PostgreSQL ist eine Stored Procedure ein vorkompiliertes ausführbares Objekt, das eine Reihe von SQL-Befehlen enthält. Ihre Hauptaufgabe ist es, Operationen auf der Datenbank auszuführen, etwa Einfügen, Aktualisieren, Löschen und Erstellen. Besonders nützlich sind sie bei Operationen mit mehreren Schritten oder komplexer Logik – sie ermöglichen ein besseres Transaktionsmanagement und verringern die Angreifbarkeit der Datenbank.

Hinweis: Die Beispiele und Befehle in diesem Tutorial funktionieren mit PostgreSQL 11 und neueren Versionen.

Voraussetzungen

Für dieses Tutorial solltest du Folgendes installiert und eingerichtet haben:

1. Eine PostgreSQL-Installation auf deinem lokalen Rechner oder Server.
2. Ein Kommandozeilen-Tool oder ein GUI-Tool wie Beekeeper Studio, um die SQL-Befehle auszuführen.

Eine Stored Procedure in PostgreSQL erstellen

Traditionell nutzt PostgreSQL das Schlüsselwort FUNCTION, um Routinen zu definieren, die einen Wert zurückgeben können. Funktionen in PostgreSQL können Operationen ausführen und müssen einen Wert oder eine Menge zurückgeben – das kann ein Skalarwert oder eine Tabelle sein. Außerdem lassen sie sich in SQL-Abfragen verwenden.

PROCEDURE wurde mit PostgreSQL 11 eingeführt und dient dazu, Routinen ohne Rückgabewert zu definieren. Mit Prozeduren kannst du Aktionen ausführen, etwa den Zustand der Datenbank ändern, ohne direkt Daten zurückzugeben. Sie werden mit der CALL-Anweisung aufgerufen – anders als Funktionen, die oft als Teil einer SELECT-Anweisung aufgerufen werden.

Mit PROCEDURE eine Tabelle aktualisieren

Fangen wir mit einer einfachen Stored Procedure in PostgreSQL an. Wir erstellen eine Prozedur, die das Gehalt eines Mitarbeiters aktualisiert.

CREATE OR REPLACE PROCEDURE update_employee_salary(p_id INT, p_salary NUMERIC)
LANGUAGE plpgsql
AS $$
BEGIN
   UPDATE employees SET salary = p_salary WHERE employee_id = p_id;
END;
$$;

Im obigen Code ist update_employee_salary der Name der Prozedur, die zwei Parameter entgegennimmt – die Mitarbeiter-ID (p_id) und das neue Gehalt (p_salary).

Eine Stored Procedure in PostgreSQL aufrufen

Um unsere neu erstellte Prozedur aufzurufen, verwenden wir den Befehl CALL wie unten gezeigt:

CALL update_employee_salary(1, 50000);

Der obige Befehl setzt das Gehalt des Mitarbeiters mit der ID 1 auf 50000.

Fortgeschritten: Prozeduren mit bedingter Logik

Prozeduren können auch komplexere Logik nutzen, etwa bedingte Anweisungen und Fehlerbehandlung, um anspruchsvollere Operationen auszuführen.

Diese Prozedur aktualisiert die Abteilung eines Mitarbeiters anhand vorgegebener Bedingungen und zeigt, wie du Kontrollstrukturen in Prozeduren einsetzt.

CREATE OR REPLACE PROCEDURE update_employee_department(employee_id INT, new_department_id INT)
LANGUAGE plpgsql
AS $$
BEGIN
    IF new_department_id IS NOT NULL THEN
        UPDATE employees
        SET department_id = new_department_id
        WHERE id = employee_id;
    ELSE
        RAISE EXCEPTION 'Department ID cannot be null.';
    END IF;
END;
$$;

Erwartete Ausgabe:

CREATE PROCEDURE

Mit FUNCTION Daten abrufen

Im Gegensatz zu PROCEDURE eignet sich eine FUNCTION in PostgreSQL besser für Operationen, bei denen ein Rückgabewert erwartet wird. Das ist besonders praktisch für Datenabrufe und Berechnungen, die Teil von SQL-Abfragen sein sollen. Hier erstellen wir als Beispiel eine Funktion, die Informationen anhand bestimmter Bedingungen abruft.

Diese Funktion holt die Details eines Mitarbeiters anhand seiner ID. Sie zeigt, wie Funktionen Daten zurückgeben können, die du dann direkt in SQL-Abfragen verwenden kannst.

CREATE OR REPLACE FUNCTION get_employee_details(employee_id INT)
RETURNS TABLE(first_name VARCHAR, last_name VARCHAR, department_id INT) AS $$
BEGIN
    RETURN QUERY 
    SELECT first_name, last_name, department_id
    FROM employees
    WHERE id = employee_id;
END;
$$ LANGUAGE plpgsql;

Erwartete Ausgabe:

CREATE FUNCTION

Die Funktion aufrufen

Um mit dieser FUNCTION Daten abzurufen, verwendest du die SELECT-Anweisung – genau wie bei der Abfrage einer Tabelle.

SELECT * FROM get_employee_details(1);

Erwartete Ausgabe:

Die Ausgabe enthält hier die Details des Mitarbeiters mit der ID 1. Existiert der Mitarbeiter, werden Vorname, Nachname und Abteilungs-ID angezeigt. Gibt es keinen solchen Mitarbeiter, bleibt das Ergebnis leer.

 first_name | last_name | department_id 
------------+-----------+---------------
 John       | Doe       | 1
(1 row)

Fazit

In diesem Tutorial hast du gelernt, wie du Stored Procedures in PostgreSQL mit PROCEDURE und FUNCTION schreibst und aufrufst. Wer Stored Procedures beherrscht, profitiert von besserer Code-Organisation, mehr Sicherheit und höherer Ausführungsgeschwindigkeit. Die Beispiele sind bewusst einfach gehalten, aber die Konzepte bleiben bei komplexeren Operationen dieselben.

Stored Procedures in PostgreSQL bieten eine robuste Möglichkeit, Logik zu kapseln und Operationen effizient und sicher auszuführen. Wenn du Stored Procedures in dein Datenbankdesign integrierst, erhältst du wartbarere und zuverlässigere Datenbankanwendungen. Nutze diesen Leitfaden als Ausgangspunkt, um die vielfältigen prozeduralen Möglichkeiten von PostgreSQL zu erkunden.