Stored Procedure mit User-defined Types unter PostgreSQL

In diesem Beitrag möchte ich zeigen, wie eine Stored Procedure mit User-defined Types (UDT) in einer PostgreSQL Datenbank programmiert wird. Im übergeordneten Beitrag Spring und Stored Procedure mit User-defined Types wird das Aufrufen dieser Stored Procedure aus Java heraus gezeigt. Im Beitrag Stored Procedure mit User-defined Types unter Oracle wird dieses Beispiel anhand einer Oracle Database beschrieben.

Dieser Beitrag beschreibt …

  • das Installieren eines PostgreSQL Datenbankservers
  • das Anlegen einer Datenbank unter PostgreSQL
  • das Erzeugen einer Stored Procedure mit User-defined Types (UDT)

Die Installation des PostgreSQL Datenbankservers

Der PostgreSQL Datenbankservers wird unter dem Betriebssystem Ubuntu mit folgenden Befehlen installiert.

Der Datenbankserver wurde installiert, direkt konfiguriert und gestartet.

Nun wird noch der Datenbankbenutzer dba eingerichtet. Dazu muss der sudo Befehl mit der Option -u postgres ausgeführt werden, da die Instanz der Datenbankservers dem Betriebssystembenutzer postgres gehört. Die Option -P sorgt dafür, dass das Passwort abgefragt wird.

Das Anlegen einer Datenbank

Dieser Befehl erzeugt die Datenbank TEST_SPRING_JDBC. Die Option -O dba legt den Datenbankbenutzer dba als Eigentümer der Datenbank fest.

Dieser Befehl macht die prozedurale Programmiersprache PL/pgSQL in dieser Datenbank verfügbar.

Mögliche Alternativen: PL/Tcl, PL/Perl, PL/Python, PL/Java, …

Das Erzeugung einer Stored Function per Konsolenanwendung

Als erstes wird über die Konsolenanwendung psql eine Funktion erzeugt.

Die Eingabe des SQL Befehls zur Erzeugung der Funktion fn_info().

Mit dem folgenden SQL Befehl wird die gerade erzeugte Funktion getestet.

Die Anzeigen der erzeugten Funktionen.

Das Erzeugung der Stored Procedure mit User-defined Types

Mit dem folgendem Skript wird die Stored Procedure und die User-defined Types angelegt.

Zunächst werden aber die existierenden Prozeduren bzw. Typen kaskadierend gelöscht.

In den folgenden Zeilen werden die benutzerdefinierten Typen des aktuellen Benutzers und einer Person angelegt.

PostgreSQL vergibt für jeden komplexen Typ implizit einen speziellen Namen für Arrays (z. B. _s_Person für s_Person[] und _s_User für s_User[]).

Da andere Datenbanken diese Konzept nicht kennen, werden hier benutzerdefinierte Wertebereiche ( DOMAIN) verwendet.

In folgenden Zeilen wird die eigentliche Stored Procedure erzeugt.

  • In den Zeilen 24 und 25 sind die Eingabeparameter der Prozedur definiert.
  • In der Zeile 26 ist der Rückgabeparameter als ein Liste von Personen definiert.
  • In den Zeilen 29 bis 32 sind die Variablen dieser Prozedur definiert.
  • In der Zeile 32 wird das Geburtsdatum mit dem aktuellen Datum vorbelegt.
  • In der Zeile 34 wird eine Meldung auf die Kommandozeile geschrieben.
  • In der Zeile 38 wird eine neue Person angelegt.
  • In der Zeile 42 wird die konstant Kreiszahl Pi (π) verwendet, um ein Gehalt auszurechnen.
  • In der Zeile 43 wird die veränderte Person zurück in das Array gestellt.

Die Ausführung dieses Skriptes mit dem Kommando psql liefert folgende Konsolenausgaben.

Mit den Kommandos \df und \dT+ in der Konsolenanwendung psql können die Details über die Stored Procedures bzw. UDTs abgefragt werden. Mit dem Kommando \q wird die Konsolenanwendung verlassen.

Das folgende Skript dient der Überprüfung der angelegten Prozedur.

  • In den Zeilen 1 bis 11 wird eine Funktion definiert, die ein Benutzer erzeugt.

Die Konsolenausgabe dieses Skripts zeigt, dass die Stored Procedure aufgerufen wurde.

Die folgenden Ausgaben zeigen die Rückgabe der Stored Procedure.

Frank Rahn

Frank Rahn ist Softwarearchitekt. Er unterstützt bei der Konzeption von Softwarearchitekturen mit Java-Technologie. Folge Sie ihm auf Facebook, Twitter oder Google+.

Benötigen Sie Unterstützung? Kontaktieren Sie ihn.

Hat Ihnen dieser Beitrag gefallen? Wir würden uns über Ihren Kommentar freuen! Bitte verwenden Sie Ihren bürgerlichen Namen und eine E-Mail-Adresse mit Gravatar.

Letzte Artikel von Frank Rahn (Alle anzeigen)

1 Antwort

Trackbacks & Pingbacks

  1. […] User-defined Types wird das Aufrufen dieser Stored Procedure aus Java heraus gezeigt. Im Beitrag Stored Procedure mit User-defined Types unter PostgreSQL wird dieses Beispiel anhand einer PostgreSQL Datenbank […]

Dein Kommentar

Want to join the discussion?
Feel free to contribute!

Schreibe einen Kommentar

Deine E-Mail-Adresse wird nicht veröffentlicht. Erforderliche Felder sind mit * markiert.