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;
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)), ... );
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';
Ü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';
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';
Funktion aufrufen
SELECT default_parameter();
SELECT default_parameter('keine Panik');
Funktion ist IMMUTABLE
CREATE FUNCTION programm_version() RETURNS BIGINT AS $$ SELECT 20090123::BIGINT; $$ LANGUAGE 'sql' IMMUTABLE;
SELECT programm_version();
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';
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';
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';
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;
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;
Funktion für Trigger erstellen
CREATE FUNCTION ...() RETURNS TRIGGER AS $$ BEGIN ... END $$ LANGUAGE 'plpgsql';
PL/pgSQL installieren
CREATE LANGUAGE plpgsql;
PL/pgSQL installieren
createlang plpgsql test
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';
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';
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';
Vergleiche durchführen
SELECT kleiner_gleich_groesser (23, 42);
SELECT kleiner_gleich_groesser (5, 5);
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';
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';
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';
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';
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';
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';
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';
Trigger-Funktion
CREATE FUNCTION ... () RETURNS TRIGGER AS $$ BEGIN RETURN ...; END; $$ LANGUAGE 'plpgsql';
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;
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
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';
Array als Rückgabewert
CREATE FUNCTION perl_array () RETURNS TEXT[] AS $$ return ['23', '42']; $$ LANGUAGE 'plperl';
Beispielfunktion aufrufen
SELECT perl_array();
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';
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';
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';
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';
Daten innerhalb einer Trigger-Funktion ändern
CREATE FUNCTION ... () RETURNS TRIGGER AS $$ BEGIN $_TD->{new}{zahl} = 23; return "MODIFY"; END; $$ LANGUAGE 'plpgsql';
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); }
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"
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` ...
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;
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;
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');