Severity: Notice
Message: Undefined offset: 1
Filename: infosekolah/leftmenudasboard.php
Line Number: 33
Line Number: 34
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.
PL/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;
Das 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.
Variablen werden im optionalen Abschnitt DECLARE definiert und optional initialisiert.
DECLARE
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;
:= ist der Zuweisungsoperator, mit dem man einer Variable einen Wert zuweist. Im DECLARE Abschnitt kann für Zuweisungen alternativ auch DEFAULT verwendet werden.
:=
DEFAULT
variablenname NUMERIC(precision, scale) DEFAULT wert;
Um eine Zahlenvariable zu definieren, schreibt man den Variablennamen gefolgt vom Variablentyp NUMERIC.
NUMERIC
Hinter diesem schreibt man in runden Klammern die Genauigkeit precision sowie optional ein Komma und die Anzahl an Nachkommastellen scale. Gibt man NUMERIC ohne Klammern an gelten für die Genauigkeit bis zu 131072 Stellen vor dem Dezimalpunkt und bis zu 16383 Stellen nach dem Dezimalpunkt.
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
variablenname1 VARCHAR(length) := 'Text'; VARCHAR(length) COLLATE collation_name;
Um eine Textvariable zu definieren, schreibt man den Variablennamen gefolgt vom Variablentyp VARCHAR. Hinter diesem schreibt man die Anzahl der Zeichen, die in der Variable gespeichert werden können, in Klammern. Soll die Textvariable nach sprach- oder benutzerdefinierten Kriterien sortiert werden, kann man das Schlüsselwort COLLATE gefolgt vom Collationsname z. B. "en_US" verwenden. Weiterer Datentypen für Textvariablen sind:
VARCHAR
COLLATE
CHAR, TEXT
variablenname BOOLEAN := TRUE;
Kann TRUE, FALSE oder NULL sein.
TRUE
FALSE
NULL
variablenname DATE := TO_DATE( '01.01.2005' , 'DD.MM.YYYY');
Um eine Datumsvariable zu definieren schreibt man den Variablennamen gefolgt vom Variablentyp DATE. Zum Konvertieren von Datum und Uhrzeit stellt PostgreSQL eine Reihe von Funktionen zur Verfügung. Hier wurde TO_DATE() verwendet. Diese Funktion wandelt den Text zwischen den ersten Hochkommas in ein Datum mit dem angegebenen Format zwischen den zweiten Hochkommas um. Weitere Datum- und Zeit-Datentypen sind:
DATE
TO_DATE()
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 (p) kann die Anzahl der Stellen der Sekundenbruchteile präzisiert werden.
(p)
Variablenname tabellenname%ROWTYPE; Variablenname tabellenname.spaltenname%TYPE;
%TYPE definiert eine Variable des Typs der angegebenen Spalte. %ROWTYPE definiert eine Variable des Typs der angegebenen Tabelle. Weil jede Tabelle in PostgreSQL implizit einen gleichnamigen Zeilentyp generiert, darf %ROWTYPE auch weggelassen werden.
%TYPE
%ROWTYPE
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;
RETURN expression; RETURN NEXT expression; RETURN QUERY query; RETURN QUERY EXECUTE command-string; RETURN QUERY EXECUTE command-string USING expression;
Mit dem Schlüsselwort RETURN wird die Rückgabe an die Funktion definiert. Soll die Funktion Datensätze mit Hilfe des Schlüsselwortes SETOF zurückgeben, kann das mit RETURN NEXT oder RETURN QUERY realisiert werden. Mit Hilfe von USING können Parameter in den SQL-Befehl eingefügt werden.
RETURN
SETOF
RETURN NEXT
RETURN QUERY
USING
In folgendem Beispiel wird RETURN NEXT verwendet:
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
Mit Hilfe von Bedingungen kann der Programmablauf gesteuert werden. Je nach Situation werden die Befehle im dafür vorgesehenen Abschnitt abgearbeitet.
IF 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;
IF prüft, ob eine Bedingung erfüllt ist. Trifft die Bedingung zu, wird der Code nach dem THEN und ausgeführt und ansonsten übersprungen. Mit ELSIF können weitere Bedingungen hinzugefügt werden. Ist eine ELSE vorhanden wird der darauf folgende Code ausgeführt, falls keine der Bedingungen zutrifft. Trifft mehr als eine der Bedingungen zu, wird nur die erste wahre Bedingung ausgeführt, und alle anderen wahren Bedingungen übersprungen. Die Schlüsselwörter ELSEIF und ELSIF sind Synonyme.
IF
THEN
ELSIF
ELSE
ELSEIF
CASE 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 CASE-Anweisung bietet zwei unterschiedliche Wege, im ersten Fall wird nach dem CASE ein Suchausdruck angegeben. Dieser Ausdruck wird im Ausdruck der WHEN folgt gesucht und anschließend wird der Code nach dem THEN ausgeführt. Nach dem WHEN können auch mehrere durch Kommas getrennte Ausdrücke ausgewertet werden. Im zweiten Fall folgt dem CASE das WHEN direkt, und danach wird eine Bedingung angegeben. Trifft diese zu wird der Code nach dem THEN ausgeführt. In beiden Fällen wird der dem ELSE folgende Code ausgeführt, wenn nichts gefunden wurde. mit END CASE wird die CASE-Anweisung abgeschlossen. Wie zuvor bei IF THEN wird nur der der ersten gültigen Bedingung folgende Codeabschnitt ausgeführt.
CASE
WHEN
END CASE
IF THEN
Beispiel für Verzweigung des Programmablaufes mit IF THEN und CASE WHEN:
CASE WHEN
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.
Mit den Schlüsselwörtern LOOP, EXIT, CONTINUE, WHILE, FOR und FOREACH können Anweisungsblöcke wiederholt durchlaufen werden.
LOOP, EXIT, CONTINUE, WHILE, FOR
FOREACH
LOOP statements; EXIT; END LOOP; <<label>> LOOP statements; EXIT label WHEN boolean_expression ; END LOOP label ;
Die einfache LOOP Schleife endet sobald ein EXIT die Schleife oder ein RETURN die Funktion beendet. Folgt hinter dem EXIT ein WHEN mit einer Bedingung, wird die Schleife nur bei erfüllter Bedingung verlassen. PL/pgSQL erlaubt es Schleifen zu benennen. Der LOOP Block wird auf jeden Fall bis zum EXIT durchlaufen, egal ob die Bedingung auch schon zuvor zutraf. Mit der CONTINUE WHEN Anweisung kann ein Teil der Schleife bedingt ausgeführt werden.
LOOP
EXIT
CONTINUE WHEN
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 boolean_expression LOOP statements; END LOOP
Die einfache WHILE LOOP Schleife wird nur durchlaufen wenn die Eingangsbedingung erfüllt ist, und endet sobald die Bedingung nicht mehr zutrifft. Auch diese Variante kann benannt werden.
WHILE LOOP
FOR varname IN expression .. expression LOOP -- FOR i IN REVERSE 10..2 BY 2 LOOP statements; END LOOP;
Die FOR-Schleife zählt eine Indexvariable vom Type INTEGER die auch automatisch erstellt wird von einem festgelegten Startwert bis zu einem festgelegten Endwert. Start- und Zielwert sind durch zwei Punkte getrennt. Gibt man das Schlüsselwort REVERSE nach dem IN an, so wird vom größeren zum kleineren Wert heruntergezählt. Die Schrittweite ist aber in jedem Falle positiv anzugeben, auch beim Rückwärtszählen.
FOR
INTEGER
Die FOR-Schleife kann auch zum Durchlaufen einer Abfrage verwendet werden:
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;
