PostgreSQL Datenbankpraxis für Anwender, Administratoren und Entwickler

Inhaltsverzeichnis

Kapitel 10 - Serverseitige Programmierung

Seite 332

Interne Funktionen aufrufen 

SELECT LENGTH('Dies ist ein Text');


Interne Funktionen aufrufen 

SELECT ABS(-5);


Ein Array bilden 

SELECT array[23, 42];


Interne Aggregatfunktionen aufrufen 

SELECT MAX(id) FROM test;

Seite 333

Funktion definieren 

CREATE FUNCTION email_pruefen(VARCHAR)
       RETURNS BOOLEAN
AS $$
  SELECT CASE
    WHEN (LENGTH($1) = 0) THEN (FALSE)
    WHEN (LENGTH(TRIM($1)) > 5 AND STRPOS($1, '@') > 0) THEN (TRUE)
    ELSE (FALSE)
  END;
$$ LANGUAGE 'sql';

Funktion in CHECK nutzen 

CREATE TABLE benutzer (
  ...
  email      VARCHAR      NOT NULL
                          CHECK (email_pruefen(email)),
  ...
);

Seite 334

Funktion erweitern 

CREATE OR REPLACE FUNCTION email_pruefen(VARCHAR)
       RETURNS BOOLEAN
AS $$
  SELECT CASE
    WHEN ($1 IS NULL) THEN (FALSE)
    WHEN (LENGTH($1) = 0) THEN (FALSE)
    WHEN (LENGTH(TRIM($1)) > 5 AND STRPOS($1, '@') > 0) THEN (TRUE)
    ELSE (FALSE)
  END;
$$
LANGUAGE 'sql';


Basisfunktion mit zwei Parametern 

CREATE FUNCTION hochzaehlen(INTEGER, INTEGER)
       RETURNS INTEGER
AS $$

  SELECT $1 + $2;

$$ LANGUAGE 'sql';

Seite 335

Überladene Funktion mit einem Parameter 

CREATE FUNCTION hochzaehlen(INTEGER)
       RETURNS INTEGER
AS $$

  SELECT hochzaehlen($1, 1);

$$ LANGUAGE 'sql';


Überladene Funktionen testen 

SELECT hochzaehlen(23, 19);
SELECT hochzaehlen(23);


Funktion für Texte 

CREATE FUNCTION speicherverbrauch(TEXT)
       RETURNS INTEGER
AS $$

  SELECT pg_column_size($1::TEXT);

$$ LANGUAGE 'sql';

Seite 336

Funktion für Integer 

CREATE FUNCTION speicherverbrauch(INTEGER)
    RETURNS INTEGER
AS $$

  SELECT pg_column_size($1::INTEGER);

$$ LANGUAGE 'sql';


Überladene Funktionen testen 

SELECT speicherverbrauch(23);
SELECT speicherverbrauch('ein Text');


Funktion mit Default-Parametern 

CREATE FUNCTION default_parameter(TEXT DEFAULT 'keine Angabe')
    RETURNS VOID
AS $$ BEGIN

  RAISE NOTICE 'Parameter 1 ist: %', $1;

END $$ LANGUAGE 'plpgsql';

Seite 337

Funktion aufrufen 

SELECT default_parameter();
SELECT default_parameter('keine Panik');

Seite 338

Funktion ist IMMUTABLE 

CREATE FUNCTION programm_version()
       RETURNS BIGINT
AS $$

  SELECT 20090123::BIGINT;

$$ LANGUAGE 'sql' IMMUTABLE;
SELECT programm_version();

Seite 339

Funktion ist STRICT 

CREATE FUNCTION addieren(INTEGER, INTEGER)
       RETURNS BIGINT
AS $$

  SELECT ($1 + $2)::BIGINT;

$$ LANGUAGE 'sql' STRICT;


STRICT-Funktion ausprobieren 

SELECT addieren(23, 42);
SELECT addieren(23, NULL);


Variablennamen verwenden 

CREATE FUNCTION multiplizieren(p1 INTEGER, p2 INTEGER)
       RETURNS BIGINT
AS $$ BEGIN

  RETURN (p1 * p2)::BIGINT;

END $$ LANGUAGE 'plpgsql';

Seite 340

INOUT-Funktion erstellen 

CREATE FUNCTION f1(INOUT p1 INTEGER)
       RETURNS INTEGER
AS $$ BEGIN

  p1 := p1 + 1;

END $$ LANGUAGE 'plpgsql';


INOUT-Funktion verwenden 

SELECT f1(23);


Funktion mit mehreren Ergebniszeilen 

CREATE FUNCTION mehrere_zeilen()
       RETURNS SETOF INTEGER
AS $$ BEGIN

  RETURN NEXT 23;
  RETURN NEXT 42;

END $$ LANGUAGE 'plpgsql';

Seite 341

Mehrere Ergebnisse zurückgeben 

SELECT * FROM mehrere_zeilen();


Funktion mit mehreren Ergebniszeilen 

CREATE FUNCTION mehrere_zeilen2()
       RETURNS SETOF INTEGER
AS $$ BEGIN

  RETURN QUERY SELECT 23;
  RETURN QUERY SELECT 42;

END $$ LANGUAGE 'plpgsql';

Seite 342

Funktion ist SECURITY DEFINER 

CREATE TABLE benutzer (
  benutzername   TEXT    PRIMARY KEY,
  passwort       TEXT    NOT NULL
);
INSERT INTO benutzer
              (benutzername, passwort)
       VALUES (session_user, 'altes Passwort');
CREATE FUNCTION neues_passwort(TEXT)
      RETURNS VOID
AS $$
  UPDATE benutzer
     SET passwort = $1
   WHERE benutzername = session_user;

$$ LANGUAGE 'sql' EXTERNAL SECURITY DEFINER;
SELECT neues_passwort('ganz geheim');
SELECT benutzername, passwort FROM benutzer;

Seite 344

Erwartete Ausführungskosten angeben 

CREATE FUNCTION ...()
      RETURNS ...
AS $$ BEGIN

...

END $$ LANGUAGE 'plpgsql' COST 2500;


Erwartete Anzahl Ergebnisse angeben 

CREATE FUNCTION ...()
      RETURNS SETOF ...
AS $$ BEGIN

...

END $$ LANGUAGE 'plpgsql' ROWS 200;

Seite 345

Funktion für Trigger erstellen 

CREATE FUNCTION ...()
      RETURNS TRIGGER
AS $$ BEGIN

...

END $$ LANGUAGE 'plpgsql';

Seite 347

PL/pgSQL installieren 

CREATE LANGUAGE plpgsql;


PL/pgSQL installieren 

createlang plpgsql test

Seite 348

PL/pgSQL-Funktion erstellen 

CREATE FUNCTION plpgsql_test ()
      RETURNS VOID
AS $$ BEGIN

  RAISE NOTICE 'Ich bin eine Testfunktion';

END; $$ LANGUAGE 'plpgsql';


PL/pgSQL-Funktion mit Hochkommas als Quoting erstellen 

CREATE FUNCTION plpgsql_test ()
      RETURNS VOID
AS '
BEGIN

  RAISE NOTICE ''Ich bin eine Testfunktion'';

END;
'
LANGUAGE 'plpgsql';


Variablen deklarieren 

CREATE FUNCTION gross_geschrieben (TEXT)
      RETURNS TEXT
AS $$
DECLARE
  param_1 ALIAS FOR $1;
  var_up TEXT;
BEGIN

  var_up := UPPER(param_1);

  RETURN var_up;

END;
$$
LANGUAGE 'plpgsql';

Seite 349

Variablen deklarieren 

SELECT gross_geschrieben ('alles klein geschrieben');


Fehler in einer Funktion abfangen 

CREATE FUNCTION division (FLOAT, FLOAT)
      RETURNS FLOAT
AS $$
DECLARE
  param_1 ALIAS FOR $1;
  param_2 ALIAS FOR $2;
BEGIN
  RETURN param_1 / param_2;

  EXCEPTION WHEN division_by_zero THEN
      RAISE NOTICE 'Division durch 0 ist nicht erlaubt!';
     RETURN NULL;

END;
$$
LANGUAGE 'plpgsql';

Seite 350

Division durchführen 

SELECT division (10.5, 2);
SELECT division (20, 0);


Vergleiche durchführen 

CREATE FUNCTION kleiner_gleich_groesser (INTEGER, INTEGER)
      RETURNS INTEGER
AS $$
DECLARE
  param_1 ALIAS FOR $1;
  param_2 ALIAS FOR $2;
BEGIN
  IF param_1 < param_2 THEN
      RAISE NOTICE 'Parameter 1 ist kleiner Parameter 2';
      RETURN -1;
  ELSIF param_1 = param_2 THEN
      RAISE NOTICE 'Parameter 1 ist gleich Parameter 2';
      RETURN 0;
  ELSE
      RAISE NOTICE 'Parameter 1 ist groesser Parameter 2';
      RETURN 1;
  END IF;

END;
$$
LANGUAGE 'plpgsql';

Seite 351

Vergleiche durchführen 

SELECT kleiner_gleich_groesser (23, 42);
SELECT kleiner_gleich_groesser (5, 5);

Seite 352

Eine einfache Schleife 

CREATE FUNCTION ... ()
      RETURNS ...
AS $$ BEGIN

  LOOP
      ... Funktionen innerhalb der Schleife
  END LOOP;

  RETURN ...;
END; $$ LANGUAGE 'plpgsql';


Schleife abbrechen 

  LOOP
      IF bedingung = 'wahr' THEN
          EXIT;
      END IF;
  END LOOP;


FOR-Schleife 

CREATE FUNCTION ... ()
      RETURNS ...
AS $$
DECLARE
  zaehler  INTEGER;
BEGIN

  FOR zaehler IN 23..42 LOOP
      RAISE NOTICE 'Zaehler: %', zaehler;
  END LOOP;

  RETURN ...;
END; $$ LANGUAGE 'plpgsql';

Seite 353

FOR-Schleife rückwärts zählend 

  FOR zaehler IN REVERSE 42..23 LOOP
      RAISE NOTICE 'Zaehler: %', zaehler;
  END LOOP;


Große Schritte wagen 

  FOR zaehler IN 1..99 BY 10 LOOP
      RAISE NOTICE 'Zaehler: %', zaehler;
  END LOOP;


Daten in einer FOR-Schleife auslesen 

CREATE FUNCTION ... (...)
      RETURNS ...
AS $$
DECLARE
  var_record  RECORD;
BEGIN

     FOR var_record IN
  SELECT spalte1, spalte2
    FROM tabelle
    LOOP

        RAISE NOTICE 'Spalte 1: %', var_record.spalte1;

     END LOOP;

  RETURN ...;
END; $$ LANGUAGE 'plpgsql';

Seite 354

Mit einer WHILE-Schleife auf ein Ereignis warten 

CREATE FUNCTION ... (...)
      RETURNS ...
AS $$
DECLARE
  ergebnis  BOOLEAN;
BEGIN

  ergebnis := FALSE;
  WHILE ergebnis = FALSE LOOP
      ergebnis := meine_funktion();
  END LOOP;

  RETURN ...;
END; $$ LANGUAGE 'plpgsql';

Seite 355

Einfache Anweisung innerhalb einer Funktion 

CREATE FUNCTION ... ()
      RETURNS ...
AS $$ BEGIN

  UPDATE tabelle SET spalte = spalte + 1;

  RETURN ...;
END; $$ LANGUAGE 'plpgsql';


Anweisung mit Variablen 

CREATE FUNCTION ... (schrittweite INTEGER)
      RETURNS ...
AS $$ BEGIN

  UPDATE tabelle SET spalte = spalte + schrittweite;

  RETURN ...;
END; $$ LANGUAGE 'plpgsql';

Seite 356

Dynamische Anweisung mit Variablen 

CREATE FUNCTION ... (schrittweite INTEGER)
      RETURNS ...
AS $$
DECLARE
  query_string   TEXT;
BEGIN

  query_string := 'UPDATE tabelle SET spalte = spalte + ';
  query_string := query_string || quote_literal(schrittweite);
  RAISE NOTICE '%', query_string;
  EXECUTE query_string;

  RETURN ...;
END; $$ LANGUAGE 'plpgsql';


Andere Funktion aufrufen 

CREATE FUNCTION ... (...)
      RETURNS ...
AS $$ BEGIN

  PERFORM andere_funktion();

  RETURN ...;
END; $$ LANGUAGE 'plpgsql';

Seite 357

Einzelnen Datensatz auslesen 

CREATE FUNCTION ... (...)
      RETURNS ...
AS $$
DECLARE
  var_record  RECORD;
BEGIN

  SELECT spalte1, spalte2
    INTO var_record
    FROM tabelle
   WHERE id = ...;

  RAISE NOTICE 'Spalte 1: %', var_record.spalte1;

  RETURN ...;
END; $$ LANGUAGE 'plpgsql';

Seite 358

RETURNING in PL/pgSQL-Funktionen nutzen 

CREATE FUNCTION ... (...)
      RETURNS ...
AS $$
DECLARE
  var_record  RECORD;
BEGIN

  INSERT INTO serial_test1 (inhalt)
       VALUES ('ein anderer Eintrag')
    RETURNING id INTO var_record;

  RAISE NOTICE 'eingefuegte ID: %', var_record.id;

  RETURN ...;
END; $$ LANGUAGE 'plpgsql';


Daten in einer Schleife auslesen 

CREATE FUNCTION ... (...)
      RETURNS ...
AS $$
DECLARE
  var_record  RECORD;
BEGIN

     FOR var_record IN
  SELECT spalte1, spalte2
    FROM tabelle
    LOOP

        RAISE NOTICE 'Spalte 1: %', var_record.spalte1;

     END LOOP;

  RETURN ...;
END; $$ LANGUAGE 'plpgsql';

Seite 359

Trigger-Funktion 

CREATE FUNCTION ... ()
      RETURNS TRIGGER
AS $$ BEGIN

  RETURN ...;
END; $$ LANGUAGE 'plpgsql';

Seite 360

Aufruftyp des Triggers 

IF TG_LEVEL = 'STATEMENT' THEN
    RAISE NOTICE 'Statement-Trigger aufgerufen';
END IF;


Aufruftyp des Triggers 

IF TG_WHEN = 'AFTER' THEN
    RAISE NOTICE 'Keine Datenaenderungen mehr moeglich';
END IF;


Aufruftyp des Triggers 

IF TG_OP = 'INSERT' THEN
    RAISE NOTICE 'Neuer Datensatz wird eingefuegt';
END IF;

Seite 361

Schema- und Tabellenname 

RAISE NOTICE 'Trigger aufgerufen von Tabelle: %.%',
    TG_TABLE_NAME, TG_TABLE_SCHEMA;


PL/Perl installieren 

CREATE LANGUAGE plperl;

PL/Perl installieren 

createlang plperl test

Seite 362

PL/Perl-Funktion erstellen 

CREATE FUNCTION plperl_test ()
      RETURNS TEXT
AS $$

  return 'Hallo Welt';

$$ LANGUAGE 'plperl';


PL/Perl-Funktion aufrufen 

SELECT plperl_test();


Parameter übergeben 

CREATE FUNCTION text_ausschnitt (TEXT, INTEGER, INTEGER)
      RETURNS TEXT
AS $$
  my $text = shift;
  my $start = shift;
  my $laenge = shift;

  return substr($text, $start, $laenge);

$$ LANGUAGE 'plperl';

Seite 363

Array als Rückgabewert 

CREATE FUNCTION perl_array ()
      RETURNS TEXT[]
AS $$

  return ['23', '42'];

$$ LANGUAGE 'plperl';


Beispielfunktion aufrufen 

SELECT perl_array();

Seite 364

Eigenen Datentyp erzeugen 

CREATE TYPE berechnung AS (min INTEGER, max INTEGER, avg INTEGER);


Hash als Rückgabewert 

CREATE FUNCTION perl_hash ()
      RETURNS berechnung
AS $$

  return {min => 23, max => 42, avg => int((23 + 42) / 2)};

$$ LANGUAGE 'plperl';


Beispielfunktion aufrufen 

SELECT min, max, avg FROM perl_hash();


Mehrere Ergebnisse 

CREATE FUNCTION perl_setof ()
      RETURNS SETOF INTEGER
AS $$

  return_next 23;
  return_next 42;

$$ LANGUAGE 'plperl';

Seite 365

Beispielfunktion aufrufen 

SELECT * FROM perl_setof();

use strict; nutzen 

CREATE FUNCTION ... ()
      RETURNS ...
AS $$

  BEGIN { strict->import(); }

$$ LANGUAGE 'plperl';

Daten für später zwischenspeichern 

  $_SHARED{'parameter'} = 'Wert';

Seite 366

Anfragen stellen: klein und schnell 

CREATE FUNCTION ... ()
      RETURNS ...
AS $$

  my $res = spi_exec_query('SELECT id, daten FROM tabelle');

  for (my $i = 0; $i <= $res->{processed}; $i++) {
    elog(NOTICE, $res->{rows}[$i]->{daten} . "\n");
  }

$$ LANGUAGE 'plperl';

Seite 367

Anfragen stellen: groß und etwas langsamer, dafür mit umfangreichen Datenmengen 

CREATE FUNCTION ... ()
      RETURNS ...
AS $$

  my $st = spi_query("SELECT id, daten FROM tabelle");
  while (defined (my $row = spi_fetchrow($st))) {
    elog(NOTICE, $row->{daten} . "\n");
  }

$$ LANGUAGE 'plperl';


Trigger-Funktion 

CREATE FUNCTION ... ()
      RETURNS TRIGGER
AS $$ BEGIN

  return;
END; $$ LANGUAGE 'plpgsql';

Seite 368

Daten innerhalb einer Trigger-Funktion ändern 

CREATE FUNCTION ... ()
      RETURNS TRIGGER
AS $$ BEGIN

  $_TD->{new}{zahl} = 23;
  return "MODIFY";

END; $$ LANGUAGE 'plpgsql';

Seite 370

Version 0-Funktion 

#include "postgres.h"
#include <string.h>

int addieren(int param1, int param2) {
  return param1 + param2;
}


Version 1-Funktion 

#include "postgres.h"
#include <string.h>
#include "fmgr.h"

PG_FUNCTION_INFO_V1(addieren);

Datum addieren(PG_FUNCTION_ARGS) {
  int32 param1 = PG_GETARG_INT32(0);
  int32 param2 = PG_GETARG_INT32(1);

  PG_RETURN_INT32(param1 + param2);
}

Seite 371

Finden einer Definition 

ctags -Rx /usr/include | grep PG_GETARG_INT32
PG_GETARG_INT32 macro  205 /usr/include/postgresql/8.3/server/fmgr.h


Include-Datei einbinden 

#include "fmgr.h"

Seite 372

Kompilieren des Sourcecode 

cc -Wall -fpic -c eigenes_modul.c


Linken des Moduls 

cc -shared -o eigenes_modul.so eigenes_modul.o


Include-Dateien des Servers einbinden 

pg_config --includedir-server
cc -I`pg_config --includedir-server` ...

Seite 373

Funktion mit NULL-Werten 

#include "postgres.h"
#include <string.h>
#include "fmgr.h"

Datum addieren(PG_FUNCTION_ARGS) {
  int32 param1, param2;

  if (PG_ARGISNULL(0)) {
    elog(ERROR, "Erster Wert darf nicht NULL sein");
  }

  param1 = PG_GETARG_INT32(0);

  if (PG_ARGISNULL(1)) {
    param2 = 1;
  } else {
    param2 = PG_GETARG_INT32(1);
  }

  PG_RETURN_INT32(param1 + param2);
}


Funktion registrieren 

CREATE FUNCTION addieren(INTEGER)
      RETURNS BIGINT
           AS '$libdir/addieren', 'addieren'
     LANGUAGE C STRICT;
CREATE FUNCTION addieren(INTEGER, INTEGER)
      RETURNS BIGINT
           AS '$libdir/addieren', 'addieren'
     LANGUAGE C STRICT;

Seite 375

Konfigurieren und Kompilieren der Sourcen 

./configure --prefix=`pg_config --pkglibdir`
make


Installation (als root): 

make install


pkglibdir ermitteln 

pg_config --pkglibdir
find `pg_config --pkglibdir` -name createlang_pgplsh.sql


Erweiterung installieren 

\i /usr/lib/postgresql/8.3/lib/share/pgplsh/createlang_pgplsh.sql


Erweiterung wieder löschen 

DROP FUNCTION plsh_handler();
DROP LANGUAGE plsh;

Seite 376

Funktion in PL/sh erstellen 

CREATE FUNCTION mail_senden(TEXT, TEXT, TEXT)
      RETURNS VOID
AS $$
#!/bin/sh

echo "$3" | mail -s "$2" "$1"

$$ LANGUAGE plsh;


PL/sh Funktion anwenden 

SELECT mail_senden('test@example.com',
              'Test Mail',
              'Dies ist eine Test Mail aus der Datenbank');
 
© Copyright 2009 - 2023 Andreas Scherbaum
Zeige Quelltext