Stored Procedure mit User-defined Types unter PostgreSQL

Die Stored Procedure "searchPersons" mit User-defined Types (UDT)

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...

Frank Rahn
Letzte Artikel von Frank Rahn (Alle anzeigen)
1 Kommentar

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 […]

Hinterlasse einen Kommentar

An der Diskussion beteiligen?
Hinterlasse uns deinen Kommentar!

Schreibe einen Kommentar

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

Ihre E-Mail-Adresse wird nicht veröffentlicht. Ihr Kommentar wird verschlüsselt an meinen Server gesendet. Erforderliche Felder sind mit * markiert.

Weitere Informationen und Widerrufshinweise finden Sie in meiner Datenschutzerklärung.