PL/pgSQL
PL/pgSQL (Procedural Language/PostgreSQL Structured Query Language) ist eine prozedurale Sprache der objektrelationalen Datenbank PostgreSQL. Die Syntax von PL/pgSQL ist wie bei PL/SQL auf Oracle-Datenbanksystemen stark an Ada angelehnt. PL/pgSQL wurde zur Erweiterung des SQL-Funktionsumfangs eingeführt, dabei kann PL/pgSQL-Code als Stored Procedure in der Datenbank selbst gespeichert sein. Unterstützt werden Variablen, Bedingungen, Schleifen, Funktionen, Datenbankcursor und Ausnahmebehandlungen. PL/pgSQL-Code kann sowohl aus SQL-Kommandos als auch aus Datenbanktriggern heraus aufgerufen werden. Mit Hilfe der prozeduralen Erweiterung lassen sich SQL-Befehle direkt im PostgreSQL-Server dynamisch erzeugen und müssen nicht mehr als Text über eine Datenbankschnittstelle übergeben werden, wie dies z. B. bei ODBC, JDBC und OLE DB der Fall ist, sondern können direkt in der Datenbank erstellt und ausgeführt werden. Verwendung
Grundlegender AufbauPL/pgSQL-Programme bestehen aus Blöcken: [2] DECLARE
-- Deklarationsblock
-- Der DECLARE Abschnitt ist optional
BEGIN
-- Ausführungsteil
EXCEPTION
-- Ausnahmeverarbeitung
-- Der EXCEPTION Abschnitt ist optional
END;
BeispielDas Beispiel schreibt eine "Hallo Welt"-Notiz. -- Eine Funktion namens hallo wird angelegt.
-- "void" bedeutet, dass nichts zurückgegeben wird.
CREATE OR REPLACE FUNCTION hallo() RETURNS void AS
-- Der Funktionskörper wird in $$-Stringliteralen gekapselt.
-- hier steht $body$ zwischen den $ Zeichen.
-- Der Text zwischen den $ Zeichen muss eine Länge von mindestens 0 Zeichen aufweisen.
$body$
BEGIN
RAISE NOTICE 'Hallo Welt'; -- eine Notiz wird aufgerufen
END;
$body$ -- Ende des Funktionskörpers
LANGUAGE plpgsql; -- die Sprache des Funktionskörpers muss angegeben werden
SELECT hallo();
-- Die Funktion wird mit einem SELECT aufgerufen.
-- Die Ausgabe der Notiz erfolgt in der Konsole
DROP FUNCTION hallo();
-- Löschen ("droppen") der Funktion, die wir gerade angelegt haben.
VariablendefinitionenVariablen werden im optionalen Abschnitt CREATE FUNCTION foo() RETURNS void AS
$BODY$
DECLARE
zahl_antwort INTEGER;
zahl_lösung INTEGER := 42;
BEGIN
zahl_antwort := zahl_lösung;
RAISE NOTICE 'Die Antwort lautet %.', zahl_antwort;-- % wird durch die Variable ersetzt
-- return true;
END;
$BODY$ LANGUAGE plpgsql;
Zahlenvariablenvariablenname NUMERIC(precision, scale) DEFAULT wert;
Um eine Zahlenvariable zu definieren, schreibt man den Variablennamen gefolgt vom Variablentyp Hinter diesem schreibt man in runden Klammern die Genauigkeit precision sowie optional ein Komma und die Anzahl an Nachkommastellen scale. Gibt man Genauigkeit entspricht in diesem Fall der Anzahl an Stellen, welche die Variable enthalten kann, und nicht dem Wertebereich. Auswahl weiterer Datentypen für Zahlenvariablen: SMALLINT, INTEGER, BIGINT, DECIMAL, REAL, DOUBLE PRECISION
Textvariablenvariablenname1 VARCHAR(length) := 'Text';
VARCHAR(length) COLLATE collation_name;
Um eine Textvariable zu definieren, schreibt man den Variablennamen gefolgt vom Variablentyp CHAR, TEXT
Booleanvariablenname BOOLEAN := TRUE;
Kann Datum und Uhrzeitvariablenname DATE := TO_DATE( '01.01.2005' , 'DD.MM.YYYY');
Um eine Datumsvariable zu definieren schreibt man den Variablennamen gefolgt vom Variablentyp TIMESTAMP [(p)] [ WITHOUT TIME ZONE ]
TIMESTAMP [(p)] WITH TIME ZONE
DATE
TIME [(p)] [ WITHOUT TIME ZONE ]
TIME [(p)] WITH TIME ZONE
INTERVAL [ FIELDS ] [(p)]
Mit dem optionalen Statement Datentyp über Tabelle oder Spalte festlegenVariablenname tabellenname%ROWTYPE;
Variablenname tabellenname.spaltenname%TYPE;
Beispiel: CREATE FUNCTION foo() RETURNS void AS
$BODY$
DECLARE
t_row tab%ROWTYPE;
BEGIN
SELECT * INTO t_row FROM tab WHERE Z=1;
RAISE NOTICE 'Y*4+Z*2= %.', t_row.y *4+ t_row.z*2;
/*return true;*/
END;
$BODY$ LANGUAGE plpgsql;
Steuerung des ProgrammablaufsRückgabe der FunktionRETURN expression;
RETURN NEXT expression;
RETURN QUERY query;
RETURN QUERY EXECUTE command-string;
RETURN QUERY EXECUTE command-string USING expression;
Mit dem Schlüsselwort In folgendem Beispiel wird BEGIN; -- eine Transaktion starten
CREATE TABLE foo (fooid INT, foosubid INT, fooname TEXT); -- Tabelle foo neu erstellen
INSERT INTO foo VALUES (1, 2, 'drei');
INSERT INTO foo VALUES (4, 5, 'neun');
-- Funktion getAllFoo anlegen. Die Funktion soll alle Datensätze aus foo liefern,
-- deren fooid größer als 0 ist:
CREATE OR REPLACE FUNCTION getAllFoo() RETURNS SETOF foo AS
$BODY$ -- Beginn der PL/pgSQL Prozedur
DECLARE
r foo%rowtype;
BEGIN
FOR r IN
SELECT * FROM foo WHERE fooid > 0
LOOP
-- hier könnten weitere Anweisungen stehen
RETURN NEXT r; -- Rückgabe des aktuellen Datensatzes aus SELECT
END LOOP;
RETURN;
END
$BODY$ -- Ende der PL/pgSQL Prozedur
LANGUAGE plpgsql;
SELECT * FROM getallfoo(); -- Dieses Select zeigt alle Datensätze die die Funktion liefert.
ROLLBACK; -- Das war ein Test, es soll nichts gespeichert werden
Verzweigung des ProgrammablaufsMit Hilfe von Bedingungen kann der Programmablauf gesteuert werden. Je nach Situation werden die Befehle im dafür vorgesehenen Abschnitt abgearbeitet. IF THENIF boolean_expression THEN statements; END IF;
IF boolean_expression THEN statements; ELSE statements; END IF;
IF boolean_expression THEN statements; ELSIF boolean_expression THEN statements; END IF;
IF boolean_expression THEN statements; ELSIF boolean_expression THEN statements; ELSE statements; END IF;
CASE WHENCASE search_expression WHEN expressions THEN statements; END CASE;
CASE search_expression WHEN expressions THEN statements; ELSE statements; END CASE;
CASE WHEN boolean_expression THEN statements; END CASE;
CASE WHEN boolean_expression THEN statements; ELSE statements; END CASE;
-- Optional darf "WHEN … THEN …" beliebig oft vorkommen;
Die Beispiel für Verzweigung des Programmablaufes mit CREATE FUNCTION foo(int) RETURNS void AS
$BODY$
DECLARE
i INTEGER := $1;
BEGIN
if i > 50 then
RAISE NOTICE 'true %', i;
ELSIF i > 25 then
RAISE NOTICE '1. elsif %', i;
ELSIF i > 20 then
RAISE NOTICE '2. elsif %', i;
ELSE
RAISE NOTICE 'if false else %', i;
END IF;
CASE I
WHEN 21,23,25,27 THEN
RAISE NOTICE '1. einfache when %', i;
WHEN 22,24,26,28 THEN
RAISE NOTICE '2. einfache when %', i;
ELSE
RAISE NOTICE 'einfache case else %', i;
END CASE;
CASE
WHEN I BETWEEN 20 and 25 THEN
RAISE NOTICE '1. gesuchte when %', i;
WHEN I BETWEEN 26 and 30 THEN
RAISE NOTICE '2. gesuchte when %', i;
ELSE
RAISE NOTICE 'gesuchte case else %', i;
END CASE;
END;
$BODY$ LANGUAGE plpgsql;
Select foo(27);
Ausgabe mit pgAdmin:
HINWEIS: 1. elsif 27
HINWEIS: 1. einfache when 27
HINWEIS: 2. gesuchte when 27
Total query runtime: 35 ms.
1 row retrieved.
SchleifenMit den Schlüsselwörtern Einfache LOOP SchleifeLOOP
statements;
EXIT;
END LOOP;
<<label>>
LOOP
statements;
EXIT label WHEN boolean_expression ;
END LOOP label ;
Die einfache Der Name eines Blocks wird zwischen doppelten größerkleiner Zeichen <<>>festgelegt wie <<label>> oberhalb. Die Benennung verbessert die Lesbarkeit des Codes. In diesem Beispiel wird der benannte Block "ablock" solange durchlaufen bis j=42 ist. Sobald das j größer als 21 wird, werden Notizen ausgegeben: CREATE OR REPLACE FUNCTION foo(int) RETURNS integer AS
$BODY$
DECLARE
i INTEGER:=$1;
j INTEGER:=0;
BEGIN
<<ablock>> -- Eine Schleife wird mit ablock benannt.
LOOP
j:=j+7;
EXIT ablock WHEN j>=i; -- Die Schleife endet wenn j>=i wird
CONTINUE ablock WHEN j<(i/2) ; -- falls j größer dem halben i ist, wird der folgende Block durchlaufen:
RAISE NOTICE ' %', j;
END LOOP ablock;
RETURN j;
END;
$BODY$ LANGUAGE plpgsql;
Select foo(42);
Ausgabe mit pgAdmin:
HINWEIS: 21
HINWEIS: 28
HINWEIS: 35
Total query runtime: 27 ms.
1 row retrieved.
WHILE LOOP SchleifeWHILE boolean_expression LOOP
statements;
END LOOP
Die einfache FOR SchleifenFOR varname IN expression .. expression LOOP -- FOR i IN REVERSE 10..2 BY 2 LOOP
statements;
END LOOP;
Die Die FOR wertliste IN query LOOP
statements;
END LOOP
Die “wertliste” ist dabei eine Variable die die Felder der Abfrage “query” übernimmt. Die Variable wertliste übernimmt beim Durchlaufen der Abfrage jede Zeile der Abfrage einmal. Mit dieser Zeile wird dann der Schleifenkörper durchlaufen. Verwendet man statt einer erst “vor Ort” bei der FOR-Schleife definierten Abfrage (bzw. einem SQL-String via EXECUTE) einen expliziten Cursor, so wird in ausreichend neuen PostgreSQL-Versionen (9.x) die Schleifenvariable “wertliste” (analog zum Schleifenindex einer numerischen FOR-Schleife) implizit deklariert. CREATE FUNCTION foo() RETURNS void AS
$body$
DECLARE
meinaktuellerview RECORD; -- die Variable meinaktuellerview wird als Type RECORD festgelegt.
BEGIN
RAISE NOTICE 'Refreshing materialized views...';
FOR meinaktuellerview IN SELECT viewname, viewsql FROM fooviews ORDER BY foo.fooid LOOP
-- jetzt beinhaltet "meinaktuellerview" einen Datensatz aus der Tabelle fooviews
RAISE NOTICE 'Ersetzen des materialisierten views %s ...', quote_ident(meinaktuellerview.viewname);
EXECUTE 'TRUNCATE TABLE ' || quote_ident(meinaktuellerview.viewname); -- Inhalt aus einer Tabelle löschen
EXECUTE 'INSERT INTO '
|| quote_ident(meinaktuellerview.viewname) || ' ' || meinaktuellerview.viewsql;
-- eine in der Tabelle gespeicherte Abfrage wird an eine Tabelle angefügt.
END LOOP;
RAISE NOTICE 'Erledigt: materialisierte Views sind aktuell.';
END;
$body$ LANGUAGE plpgsql;
Weblinks
Einzelnachweise
|
||||||||||||||||||||||||
Portal di Ensiklopedia Dunia
