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.

$ sudo apt-get install postgresql
...
$ sudo service postgresql status
Running clusters: 9.1/main

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.

$ sudo -u postgres createuser -P dba
Enter password for new role: 
Enter it again: 
Shall the new role be a superuser? (y/n) y

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.

$ sudo -u postgres createdb -O dba TEST_SPRING_JDBC "Test Datenbank für test-spring-jdbc"

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

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

$ sudo -u postgres createlang plpgsql TEST_SPRING_JDBC

Das Erzeugung einer Stored Function per Konsolenanwendung

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

$ psql TEST_SPRING_JDBC
psql (9.1.7)
Type "help" for help.
 
TEST_SPRING_JDBC=#

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

TEST_SPRING_JDBC=# CREATE OR REPLACE FUNCTION fn_info() RETURNS text AS $
TEST_SPRING_JDBC$# BEGIN
TEST_SPRING_JDBC$# RETURN 'Eine einfache Funktion';
TEST_SPRING_JDBC$# END;
TEST_SPRING_JDBC$# $ LANGUAGE plpgsql;
CREATE FUNCTION
TEST_SPRING_JDBC=#

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

TEST_SPRING_JDBC=# SELECT fn_info();
        fn_info         
------------------------
 Eine einfache Funktion
(1 row)
 
TEST_SPRING_JDBC=#

Die Anzeigen der erzeugten Funktionen.

TEST_SPRING_JDBC=# df
                                              List of functions
 Schema |     Name      | Result data type |                  Argument data types                   |  Type  
--------+---------------+------------------+--------------------------------------------------------+--------
 public | fn_info       | text             |                                                        | normal
(1 rows)
 
TEST_SPRING_JDBC=#

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.

DROP FUNCTION IF EXISTS searchPersons(
    INTEGER, s_User 
) CASCADE;
DROP DOMAIN IF EXISTS a_Person CASCADE;
DROP TYPE IF EXISTS s_User CASCADE;
DROP TYPE IF EXISTS s_Person CASCADE;

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

CREATE TYPE s_User AS ( -- Der aktuelle Benutzer
    id      CHAR(10),
    name    VARCHAR(30),
    dept    VARCHAR
);

CREATE TYPE s_Person AS ( -- Eine Person
    id          BIGINT,
    name        VARCHAR(30),
    salary      DECIMAL,
    dateOfBirth DATE
);

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.

CREATE DOMAIN a_Person AS s_Person[]; -- Collection der Person

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.

CREATE FUNCTION searchPersons( -- Suche Personen
    p_num       IN  INTEGER, 
    p_user      IN  s_User, 
    p_persons   OUT a_Person
) AS
$BODY$  -- Beginn der PL/pgSQL Funktion
    DECLARE -- Deklarationsblock
        i       INTEGER;
        p       s_Person;
        dateOfBirth DATE := current_date;
    BEGIN -- Ausführungsteil
        RAISE NOTICE 'Die Stored Procedure searchPersons wurde aufgerufen. User=%',
            p_user;
        FOR i IN 1..p_num
        LOOP -- Fake der Suche
            p := p_persons[i];
            p.id := i;
            p.name := p_user.name;
            p.dateOfBirth := dateOfBirth;
            p.salary := pi() * i * 100;
            p_persons[i] := p;
        END LOOP;
    END;
$BODY$ -- Ende der Funktion
LANGUAGE plpgsql; -- Die Sprache des Funktionskörpers

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

$ psql --file=src/sql/postgresql/init.sql TEST_SPRING_JDBC
psql:src/sql/postgresql/init.sql:3: NOTICE:  function searchpersons() does not exist, skipping
DROP FUNCTION
psql:src/sql/postgresql/init.sql:4: NOTICE:  type "a_person" does not exist, skipping
DROP DOMAIN
psql:src/sql/postgresql/init.sql:5: NOTICE:  type "s_user" does not exist, skipping
DROP TYPE
psql:src/sql/postgresql/init.sql:6: NOTICE:  type "s_person" does not exist, skipping
DROP TYPE
CREATE TYPE
CREATE TYPE
CREATE DOMAIN
CREATE FUNCTION

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.

$ psql TEST_SPRING_JDBC
psql (9.1.6)
Type "help" for help.
 
TEST_SPRING_JDBC=# df
                                              List of functions
 Schema |     Name      | Result data type |                  Argument data types                 |  Type
--------+---------------+------------------+------------------------------------------------------+--------
 public | fn_info       | text             |                                                      | normal
 public | searchpersons | a_person         | p_num integer, p_user s_user, OUT p_persons a_person | normal
(2 row)
 
TEST_SPRING_JDBC=# dT+
                         List of data types
 Schema |   Name   | Internal name | Size  | Elements | Description 
--------+----------+---------------+-------+----------+-------------
 public | a_person | a_person      | var   |          | 
 public | s_person | s_person      | tuple |          | 
 public | s_user   | s_user        | tuple |          | 
(3 rows)
 
TEST_SPRING_JDBC=# q
$

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

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

CREATE OR REPLACE FUNCTION get_user() RETURNS s_User AS
$$
    DECLARE
        u s_User;
    BEGIN
        u.id := 4711;
        u.name := 'Frank Rahn';
        u.dept := 'Entwicklung';
        RETURN u;
    END;
$$ LANGUAGE plpgsql;

SELECT * FROM searchPersons(5, get_user());

DROP FUNCTION IF EXISTS get_user();

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

$ psql --file=src/sql/postgresql/test.sql TEST_SPRING_JDBC
CREATE FUNCTION
psql:src/sql/postgresql/test.sql:13: NOTICE:  Die Stored Procedure searchPersons wurde aufgerufen. User=("4711      ","Frank Rahn",Entwicklung)
DROP FUNCTION
$

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

p_persons
----------------------------------------------------------------------------------------------
 {"(1,"Frank Rahn",314.159265358979,2012-10-27)","(2,"Frank Rahn",628.318530717959,2012...

• Über
• Letzte Artikel

Frank Rahn

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

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.

Letzte Artikel von Frank Rahn (Alle anzeigen)

• Wer ist der optimale Java Bean Mapper? - Freitag, 22. September 2023
• Spring Boot Webanwendung: Die ersten Schritte (Tutorial) - Montag, 28. März 2016
• Mainframe-Zugriff via Java - Sonntag, 04. Mai 2014