Entwicklung von Stored Functions in PostgreSQL

Einführung

In diesem Lab lernen Sie, wie Sie PostgreSQL-Stored-Functions entwickeln. Sie durchlaufen den Prozess der Definition einer einfachen Stored-Function, des Hinzufügens von Eingabeparametern mit Standardwerten, der Ausführung der Function innerhalb einer Abfrage und schließlich des Entfernens einer ungenutzten Function, um Ihre Datenbank organisiert zu halten.

Eine grundlegende Stored Function definieren

In diesem Schritt lernen Sie, wie Sie eine einfache Stored-Function in PostgreSQL definieren. Stored-Functions sind wiederverwendbare Codeblöcke, die eine bestimmte Aufgabe ausführen und innerhalb der Datenbank ausgeführt werden können.

Öffnen Sie zunächst ein Terminal und verbinden Sie sich mit der PostgreSQL-Datenbank über das psql-Kommandozeilen-Tool. Alle Datenbankoperationen führen Sie innerhalb dieser psql-Shell durch.

sudo -u postgres psql

Sie sollten nun die PostgreSQL-Eingabeaufforderung sehen, die wie postgres=# aussieht.

Erstellen Sie nun eine Function namens get_total_products, die einen festen Integer-Wert zurückgibt. Diese einfache Function hilft Ihnen, die grundlegende Syntax zu verstehen. Führen Sie den folgenden SQL-Befehl in der psql-Shell aus:

CREATE FUNCTION get_total_products()
RETURNS INTEGER AS $$
BEGIN
  RETURN 100;
END;
$$ LANGUAGE plpgsql;

Lassen Sie uns diesen Befehl aufschlüsseln:

• CREATE FUNCTION get_total_products(): Dies definiert eine neue Function namens get_total_products ohne Eingabeparameter.
• RETURNS INTEGER: Dies gibt an, dass die Function einen einzelnen Wert vom Datentyp INTEGER zurückgibt.
• AS $$ ... $$: Die Dollar-Quoted-String $$ wird verwendet, um den Body der Function einzuschließen. Dies ist eine gängige Praxis in PostgreSQL, um Probleme mit einfachen Anführungszeichen innerhalb des Codes der Function zu vermeiden.
• BEGIN ... END;: Dieser Block enthält den ausführbaren Teil der Function.
• RETURN 100;: Dies ist die Logik unserer Function, die einfach den Integer 100 zurückgibt.
• LANGUAGE plpgsql: Dies gibt an, dass die Function in plpgsql geschrieben ist, der prozeduralen Sprache von PostgreSQL.

Nachdem Sie den Befehl ausgeführt haben, bestätigt PostgreSQL die Erstellung der Function:

CREATE FUNCTION

Um zu überprüfen, ob die Function erstellt wurde, können Sie sie in einer SELECT-Anweisung aufrufen:

SELECT get_total_products();

Die Ausgabe zeigt den von der Function zurückgegebenen Wert:

 get_total_products
--------------------
                100
(1 row)

Dies bestätigt, dass Ihre erste Stored-Function korrekt funktioniert.

Eine Function mit Parametern erstellen

Stored-Functions werden leistungsfähiger, wenn sie Eingabeparameter akzeptieren können. In diesem Schritt erstellen Sie eine neue Function, die zwei Zahlen als Eingabe annimmt und deren Summe zurückgibt.

Stellen Sie sicher, dass Sie sich noch in der psql-Shell aus dem vorherigen Schritt befinden. Erstellen Sie nun eine Function namens add_numbers, die zwei Integer-Parameter akzeptiert.

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

Die Syntax ist ähnlich wie im vorherigen Schritt, mit einem wesentlichen Unterschied in der Function-Signatur:

• add_numbers(a INTEGER, b INTEGER): Dies definiert zwei Parameter, a und b, beide vom Typ INTEGER, die bei Aufruf der Function bereitgestellt werden müssen.

Nach Ausführung des Befehls sehen Sie die Bestätigung CREATE FUNCTION.

Testen Sie die Function nun, indem Sie zwei Zahlen als Argumente übergeben:

SELECT add_numbers(15, 25);

Die Function addiert die beiden Zahlen und gibt das Ergebnis zurück:

 add_numbers
-------------
          40
(1 row)

Sie können Functions auch mit Standard-Parameterwerten verwenden. Erstellen wir eine neue Function greet_user, bei der die Begrüßungsnachricht einen Standardwert hat.

CREATE OR REPLACE FUNCTION greet_user(username VARCHAR, greeting VARCHAR DEFAULT 'Hello')
RETURNS TEXT AS $$
BEGIN
  RETURN greeting || ', ' || username || '!';
END;
$$ LANGUAGE plpgsql;

Hier aktualisiert CREATE OR REPLACE die Function, falls sie bereits existiert. Dem Parameter greeting wird der Standardwert 'Hello' zugewiesen. Der Operator || wird für die String-Verkettung verwendet.

Testen Sie die Function, indem Sie nur den erforderlichen Parameter username angeben:

SELECT greet_user('Alex');

Die Function verwendet die Standardbegrüßung:

       greet_user
------------------------
 Hello, Alex!
(1 row)

Rufen Sie sie nun erneut auf, aber übergeben Sie diesmal eine benutzerdefinierte Begrüßung:

SELECT greet_user('Alex', 'Welcome');

Die Ausgabe zeigt nun Ihre benutzerdefinierte Nachricht:

      greet_user
-----------------------
 Welcome, Alex!
(1 row)

Eine Function in einer Query ausführen

Ein häufiger Anwendungsfall für Stored-Functions ist die Durchführung von Berechnungen auf Daten aus Tabellen. In diesem Schritt erstellen Sie eine Tabelle, füllen sie mit Daten und verwenden dann eine Function in einer Abfrage auf dieser Tabelle.

Erstellen Sie zunächst eine einfache Tabelle namens products, um Produktnamen und Preise zu speichern.

CREATE TABLE products (
  id SERIAL PRIMARY KEY,
  name VARCHAR(100),
  price NUMERIC(10, 2)
);

Sie sehen die Bestätigungsnachricht CREATE TABLE.

Fügen Sie als Nächstes einige Beispieldaten in die Tabelle products ein:

INSERT INTO products (name, price) VALUES
('Laptop', 1200.00),
('Mouse', 25.50),
('Keyboard', 75.00);

Sie sehen INSERT 0 3, was darauf hinweist, dass drei Zeilen eingefügt wurden.

Erstellen wir nun eine Function zur Berechnung des Preises mit Mehrwertsteuer. Diese Function nimmt einen Preis als Eingabe und gibt den Preis inklusive einer 7%igen Steuer zurück.

CREATE FUNCTION calculate_taxed_price(price NUMERIC)
RETURNS NUMERIC AS $$
BEGIN
  RETURN price * 1.07;
END;
$$ LANGUAGE plpgsql;

Nachdem Sie die Function erstellt haben, können Sie sie direkt in einer SELECT-Abfrage auf der Tabelle products verwenden. Diese Abfrage zeigt für jedes Produkt den ursprünglichen Preis und den Preis inklusive Steuer an.

SELECT name, price, calculate_taxed_price(price) AS taxed_price FROM products;

Die Ausgabe zeigt die Ergebnisse des Function-Aufrufs für jede Zeile:

   name   |  price  | taxed_price
----------+---------+-------------
 Laptop   | 1200.00 |    1284.0000
 Mouse    |   25.50 |      27.2850
 Keyboard |   75.00 |      80.2500
(3 rows)

Dies zeigt, wie Sie Geschäftslogik in einer Function kapseln und auf Ihre Daten anwenden können.

Eine gespeicherte Function entfernen

Es ist eine gute Praxis, Datenbankobjekte zu entfernen, die nicht mehr benötigt werden. In diesem Schritt lernen Sie, wie Sie eine gespeicherte Function aus Ihrer Datenbank entfernen oder "droppen". Wir werden die in der ersten Schritt erstellte Function get_total_products entfernen.

Zuerst können Sie die Functions in Ihrer Datenbank auflisten, um zu bestätigen, dass get_total_products existiert.

\df

Sie sehen eine Liste von Functions, einschließlich get_total_products.

Um die Function zu entfernen, verwenden Sie den Befehl DROP FUNCTION. Sie müssen den Namen der Function angeben. Wenn die Function Parameter hat, müssten Sie deren Typen angeben, aber da get_total_products keine hat, können Sie einfach den Namen verwenden.

DROP FUNCTION get_total_products();

PostgreSQL bestätigt die Aktion:

DROP FUNCTION

Wenn Sie nun die Functions erneut mit \df auflisten, sehen Sie, dass get_total_products nicht mehr in der Liste ist.

Es ist auch wichtig, die Argumenttypen anzugeben, wenn die Function überladen ist (d. h. mehrere Functions mit demselben Namen, aber unterschiedlichen Parametern). Um beispielsweise die Function add_numbers zu droppen, müssen Sie ihre Integer-Parameter angeben:

DROP FUNCTION add_numbers(INTEGER, INTEGER);

Zum Schluss, um Ihre Umgebung aufzuräumen, droppen Sie die in der vorherigen Schritt erstellte Tabelle products.

DROP TABLE products;

Damit ist das Lab abgeschlossen. Um die psql-Shell zu beenden, geben Sie \q ein und drücken Sie Enter.

Zusammenfassung

In diesem Lab haben Sie die Grundlagen der Entwicklung von Stored Functions in PostgreSQL gelernt. Sie haben eine einfache Function erstellt, diese mit Parametern und Standardwerten erweitert, eine Function in einer Abfrage gegen Tabellendaten angewendet und schließlich die Datenbank bereinigt, indem Sie die Functions und Tabellen entfernt haben. Diese Fähigkeiten sind unerlässlich für die Erstellung modularerer und effizienterer Datenbanklösungen.