Ačkoliv je PL/pgSQL pouze jedním z jazyků, které můžeme použít při návrhu uložených procedur, je patrně nejpoužívanější. Jedná se o jednoduchý programovací jazyk navržený pouze pro psaní uložených procedur RDBMS PostgreSQL. Je to jazyk ne nepodobný Module, s úzkou vazbou na vlastní RDBMS.
PL/pgSQL nezavádí nové typy a vlastní funkce. Obojí sdílí s RDBMS. Funkce v PL/pgSQL mohou obsahovat většinu parametrizovaných SQL příkazů: pro správu tabulek, databází i jednotlivých záznamů. PL/pgSQL má konstrukci pro iteraci napříč množinou záznamů specifikovanou příkazem SELECT. V PL/pgSQL můžeme konstruovat SQL příkazy a pak je nechat provádět. Autoři PL/pgSQL se zjevně inspirovali jazykem PL/SQL, který je nativním prog. jazykem pro RDBMS Oracle, a tak není příliš obtížné konvertovat uložené procedury z Oracle do PostgreSQL a naopak.
V PostgreSQL můžeme PL/pgSQL použít k implementaci vlastních agregačních i normálních funkcí, operátorů, k implementaci procedur, triggerů. Možnosti vytváření uložených procedur se s každou novou verzí zvětšují, proto se i vyvíjí příkaz CREATE FUNCTION, kterým definujeme novou funkci. Popis se vztahuje na verzi 7.3.
CREATE [OR REPLACE] FUNCTION název (typ_argumenty [,...]) RETURNS návr_typ AS ' tělo fce 'LANGUAGE 'plpgsql' [EXTERNAL SECURITY INVOKER | EXTERNAL SECURITY DEFINER]
Počínaje verzí 7.2.1 můžeme použít frázi CREATE OR REPLACE. V předchozích verzích jsme museli před opakovaným vytvořením funkce danou funkci zrušit příkazem DROP FUNCTION. Ve verzi 7.3 můžeme určit, s jakými právy se bude funkce vykonávat. Funkce získá buď práva uživatele, který funkci spouští (EXTERNAL SECURITY INVOKER), nebo práva vlastníka funkce (EXTERNAL SECURITY DEFINER).
Doporučuji psát kód funkcí v libovolném textovém editoru produkujícím čistý text. Kód pak uložit do souboru a psql příkazem \i
název_souboru provést příkaz (příkazy) uložené v souboru a vedoucí k vytvoření funkce. Rozhodně nedoporučuji psát funkce přímo v psql.
K tomu, abychom mohli používat PL/pgSQL, musí být tento jazyk tzv. povolen pro danou databázi. Příkaz createlang -l vaše_db by měl vypsat tabulku obsahující řádek plpgsql | t. Pokud vrácená tabulka tento řádek neobsahuje, musí se používání PL/pgSQL pro danou databázi umožnit příkazem (příkaz může provést pouze uživatel s právy Postgres super uživatele).
createlang plpgsql vaše_db. Jestliže PL/pgSQL používat můžete, zkuste si napsat jednoduchou funkci Součet:
CREATE OR REPLACE FUNCTION Soucet(int,int) RETURNS int AS ' BEGIN RETURN $1 + $2; END; ' LANGUAGE 'plpgsql';
Pokud funkci přepíšete bezchybně, můžete ji otestovat příkazem SELECT soucet(10,10). V sloupci soucet (jmenuje se stejně, jako funkce) se objeví hodnota 20.
Funkce má dvě části – deklaraci proměnných (ta zatím nebyla použita) a vlastní tělo, tj. seznam příkazů (blok) oddělených středníkem vložený mezi dvojici klíčových slov BEGIN a END. Příkaz RETURN ukončí vykonávání funkce a jako výsledek vrátí hodnotu výrazu. Symboly $1 a $2 se používají ve významu hodnoty prvého a druhého argumentu funkce. Ve stávajích verzích PostgreSQL včetně verze 7.3 nelze vrátit typ tabulka. Blok může obsahovat jiné – vnořené bloky. Bloky se, na rozdíl od Pascalu, nepoužívají k vymezení seznamu příkazů v konstrukcích IF, WHILE, FOR, LOOP, ale pouze k vymezení existence některých lokálních proměnných.
V PostgreSQL lze funkce přetěžovat, tj. můžeme mít definováno několik funkcí lišících se od sebe počtem a typy argumentů.
U identifikátorů, stejně tak i u klíčových slov, lze bez omezení používat velká i malá písmena. Při kompilaci se veškeré řetězce vyjma řetězců mezi zdvojenými apostrofy převádí na malá písmena.
Operátor přiřazení má v PL/pgSQL podobu symbolu :=. Kromě toho lze proměnné přiřadit výsledek SQL dotazu konstrukcí SELECT INTO
SELECT INTO cíl výraz FROM ...;
Cílem může být proměnná typu record nebo row, nebo seznam proměnných. Při provádění se kontroluje, zda cíl odpovídá výrazu (počtem a typy). Pokud je výsledkem prázdná množina, nabudou všechny cílové proměnné hodnoty NULL. Pokud je výsledkem více řádků, použijí se hodnoty z prvého řádku.
Kromě testu na NULL, který nemusí být vždy jednoznačný, můžeme testovat hodnotu vestavěné proměnné FOUND, která obsahuje TRUE, pokud dotaz vrátil alespoň jeden řádek. Také lze po každém SQL příkazu uložit počet zpracovaných řádek do proměnné příkazem GET.
GET DIAGNOSTICS promenna = ROW_COUNT;
Test proměnné FOUND lze provést pouze po příkazu SELECT INTO. Následující dva příklady vrací počet řádek v tabulce jména. V prvním případě se pro provedení SQL příkazu použila konstrukce PERFORM. Ta slouží k vykonání SQL příkazu nebo funkce v těch případech, kdy nedochází k dalšímu zpracování vrácené hodnoty.
CREATE OR REPLACE FUNCTION radku1() RETURNS int AS ' DECLARE r int; BEGIN PERFORM * FROM jmena; GET DIAGNOSTICS r = ROW_COUNT; RETURN r; END; ' LANGUAGE 'plpgsql'; CREATE OR REPLACE FUNCTION radku2() RETURNS int AS ' DECLARE r int; BEGIN SELECT INTO r count(*) FROM jmena; RETURN r; END; ' LANGUAGE 'plpgsql';
Za příkazem DECLARE můžeme deklarovat seznam dvojic proměnná typ oddělených středníkem. Kromě toho lze proměnné přiřadit hodnotu (počítá se při každém vstupu do bloku), lze ji zákazat přiřazení hodnoty NULL a také ji můžeme označit jako konstantní. Při porušení těchto pravidel přeruší prostředí PL/pgSQL běh procedury. Všechny proměnné označené jako NOT NULL musí mít určenou DEFAULT hodnotu.
DECLARE jméno [CONSTANT] typ [NOT NULL][DEFAULT|:=hodnota]
Kromě běžných typů lze použít typ RECORD, který může obsahovat řádek libovolné tabulky, nebo tzv. odvozené typy. Ty vycházejí buď z řádku konkrétní tabulky (název_tab%ROWTYPE), sloupce (název_tab.jm_sloupce%TYPE), nebo dříve deklarované proměnné (proměnná%TYPE). K jednotlivým položkám typu RECORD nebo řádek se přistupuje skrze tečkovou notaci, tj. proměnná.položka.
Kód PL/pgSQL může obsahovat libovolný SQL příkaz. Příkladem může být funkce vracející název měsíce na základě jeho indexu.
CREATE OR REPLACE FUNCTION MonthName1 (INTEGER) RETURNS VARCHAR(10) AS ' DECLARE vysledek RECORD; BEGIN IF $1 < 1 OR $1 > 12 THEN RAISE EXCEPTION ''Parametr je mimo přípustné meze\n''; END IF; SELECT INTO vysledek CASE $1 WHEN 1 THEN ''Leden'' WHEN 2 THEN ''Únor'' WHEN 3 THEN ''Březen'' WHEN 4 THEN ''Duben'' WHEN 5 THEN ''Květen'' WHEN 6 THEN ''Červen'' WHEN 7 THEN ''Červenec'' WHEN 8 THEN ''Srpen'' WHEN 9 THEN ''Září'' WHEN 10 THEN ''Říjen'' WHEN 11 THEN ''Listopad'' WHEN 12 THEN ''Prosinec'' END::VARCHAR(10) AS retval; RETURN vysledek.retval; END; ' LANGUAGE 'plpgsql';
Tím libovolným SQL příkazem byl v tomto příkladě příkaz CASE. Ačkoliv se vrací pouze řetězec, je nutno použít typ RECORD, jelikož příkaz CASE vrací vždy RECORD (byť o jednom prvku). Kromě příkazu CASE se v příkladu použije konstrukce IF THEN END IF a příkaz RAISE EXCEPTION. Konstrukce IF THEN END IF je jasná. Počínaje verzí 7.2 můžeme používat podmínky ve tvaru IF THEN ELSIF THEN ELSE END IF. Prvním argumentem příkazu RAISE je úroveň výjimky. K dispozici jsou tři možnosti: DEBUG – zapíše se do logu, NOTICE – oznámí se uživateli, EXCEPTION – přeruší se vykonávání funkce. Druhým parametrem je text chybového hlášení (text zapisujeme mezi zdovojené apostrofy). Pokud se v textu objeví symbol %, pak se tento symbol nahradí odpovídající proměnnou, která se předá jako třetí, čtvrtý, atd. argument. V PL/pgSQL není možné zachytit výjimku, tj. každá výjimka na úrovni EXCEPTION vede k přerušení provádění funkce.
Jako argumenty lze použít pouze proměnné, nikoliv výrazy, tj. pokud chceme zobrazit výraz, musíme jej napřed přiřadit do proměnné.
Všimněte si, že příkaz CASE je parametrizován, tj. obsahuje proměnnou. Parametry lze použít u všech SQL příkazů, všude tam, kde se vyskytuje nějaká hodnota. Nelze parametrizovat názvy sloupců a tabulek. Toto omezení lze obejít (za cenu sníženého výkonu a zvýšené pracnosti) tzv. dynamickými dotazy (viz. níže příklad EXECUTE).
Tento příklad vytvoří tabulku s hodnotami fce sin v zadaném rozsahu. Používá konstrukci WHILE a parametrizovaný SQL příkaz INSERT. Dále ukazuje, jak lze ve funkcích vytvářet tabulky. Tabulka tabsin se vytvoří pouze v případě, že dosud neexistovala.
Vytvoření tabulky pro interval od –1 do 1 s krokem 0.0001 provede příkaz select tabsin(-1.0, 1.0, 0.0001). Provádění cyklu je rychlé. INSERT dvaceti tisíců řádek trvá zhruba dvacet vteřin na mém stařičkém 2×P160. Požadovanou hodnotu funkce zjistíme příkazem SELECT
SELECT * FROM tabsin WHERE x=0.1234::NUMERIC(5,4);
Použití PG/plSQL pro správu databáze
Vhodně navržené uložené procedury znatelně zjednoduší správu databáze. Následující funkci používám pro vyčištění RDBMS od databází, které vytvoří studenti během semestru. Pokud se funkce spustí bez parametrů, zobrazí nápovědu, stejně tak, pokud nesouhlasí počet argumentů.
Všimněte si konstrukce FOR IN LOOP END LOOP, která iteruje napříč výsledkem SQL dotazu (v tomto případě seznamem databází, jejichž vlastnící vyhovují masce LIKE). Jelikož tabulky mohou obsahovat i mezery, je třeba název tabulky vložit do uvozovek. PL/pgSQL má problémy s vykonáváním příkazu DROP DATABASE, pokud název databáze obsahuje mezeru. Problém můžeme obejít tak, že celý SQL příkaz uložíme do řetězce a necháme provést příkazem EXECUTE. EXECUTE můžeme použít ve všech případech, kdy PL/pgSQL odmítne SQL příkaz provést.
Počínaje verzí 7.3 již nebude nutné uvnitř funkce zjišťovat, zdali ji spouští oprávněný uživatel. Počínaje touto verzí můžeme příkazem GRANT určit, kdo má oprávnění spouštět funkci.
Pokračování v pátek.
