02 PL/pgSQL

PL/pgSQL

V PostgreSQL môžeme PL/pgSQL použiť k implementácií vlastných agregačných aj normálnych funkcií, operátorov, k implementácií procedúr, triggerov. Autori PL/pgSQL sa zjavne inšpirovali staršími verziami programovacieho jazyka PL/SQL, čo je jazyk pre vývoj uložených procedúr firmy Oracle. Nie je tak príliš náročné konvertovať vložené procedúry z ORACLE do PostgreSQL a naopak. PL/SQL je veľmi osekaná ADA rozšírená o SQL. Syntax PL/pgSQL a PL/SQL je veľmi podobná – zásadne sa líši implementácia. PL/pgSQL je veľmi jednoduchý interpret abstraktného syntaktického stromu, ktorý beží v rovnakom procese, v ktorom prebieha spracovanie SQL príkazu. Procedúry PL/SQL bežia vo svojom vlastnom procese, pričom PL/SQL je prekladané do strojového kódu. Každý prístup má svoje výhody a nevýhody – a má samozrejme aj inú motiváciu a iné historické pozadie. PL/pgSQL je úzko integrovaný s PostgreSQL – má zanedbateľnú réžiu, pre prístup k dátam nie je nutné používať interprocess(medzi procesmi) komunikáciu, jednoducho sa udržuje, jednoducho sa rozširuje, jednoducho sa učí. Silou PL/SQL je jeho bohatosť a fakt, že je prekladaný do strojového kódu. Predsa len PL/SQL je menej osekaná ADA než PL/pgSQL a aj vďaka tomu je univerzálnejší než PL/pgSQL a taktiež náročnejší na naučenie. PL/pgSQL je založený na jednoduchom prekladači (interpreter) – tak jednoduchom, že neimplementuje ani základné aritmetické a logické operácie – každý výraz sa prevádza na SELECT, ktorý spracováva executor. PL/pgSQL obsahuje iba implementáciu premenných a riadiace konštrukcie (IF, LOOP, RETURN, :=, ..). Našťastie jednoduchšie dotazy, ktoré odpovedajú výrazom – t. j. neobsahujú odkaz na tabuľky, dokáže interpret PL/pgSQL spúšťať rádovo efektívnejšie než typické dotazy – t. j. dotazy do tabuliek. Z tejto implementácie vychádza aj efektívne použitie PL/pgSQL. Je to úžasné lepidlo pre SQL príkazy. Na druhú stranu, PL/pgSQL sa vôbec nehodí pre numerické úlohy, ktoré vyžadujú veľký počet aritmetických operácií. PL/pgSQL sa nehodí pre náročnejšie operácie, kde se intenzívne modifikujú reťazce alebo polia. Každá úprava reťazca alebo poľa znamená vytvorenie upravenej kópie pôvodných dát, ktorou sú pôvodné dáta nahradené. Tento prístup je v prípade väčšieho objemu alebo väčšieho počtu operácií neefektívny.
V PL/pgSQL sa spojili dva programovacie jazyky – dva interpretované programovacie jazyky – PL/pgSQL a SQL. Pri prvom použití funkcie (v rámci session) sa kód PL/pgSQL prevedie do syntaktického stromu (Abstract Syntax Tree), pri prvom použití SQL príkazu sa generuje prevádzací plán SQL príkazu. Parser PL/pgSQL (rovnako ako PostgreSQL) je postavený nad GNU Bisonom. Získaný syntaktický strom a vytvorené spúšťajúce plány sa používajú opakovane pokiaľ nedôjde k zmene kódu funkcie alebo k ukončení session.

Doporučenia pre návrh vložených procedúr v jazyku PL/pgSQL

Na internete je možné nájsť mnoho doporučení pre písanie vložených procedúr v PL/SQL firmy Oracle. Implementácia PL/pgSQL je iná a totiž nie všetky doporučenia sú relevantné pre PostgreSQL. V PL, rovnako ako v iných programovacích jazykoch, je možné napísať nečitateľný a zle udržiavateľný, poprípade neefektívny kód. Riziko, že Váš kód bude nevyhovujúci, znížime, pokiaľ sa budeme držať následujúcich doporučení:

• Kód píšeme v klasickom (programátorskom) editore a ukladáme do súboru. V súboroch môžeme lepšie udržovať komentáre, môžeme združovať funkčne blízke alebo závislé funkcie, môžeme verziovať kód. Odporúča sa nepoužívať nástroje ako je pgAdmin alebo phpPgAdmin.
• Existujú aj špeciálne IDE pre prácu s databázami napr. DataGrip od firmy JetBrains, či PgExplorer pre platformu Windows.
• Namiesto nástroja psql je tiež možné použiť PGCLI. Oproti psql má tento nástroj pomocné funkcie ako Auto Completion alebo zvýrazňovač syntaxe naviac, podporuje viacriadkové príkazy a inteligentnú históriu. Nástroj je naprogramovaný v jazyku Python.
• Je potrebné sa brániť pred kolíziou lokálnych premenných a databázových objektov:
  1. použitím prefixov premenných (napr. pre lokálne premenné symbol “_”)
  2. použitím kvalifikovaných atribútov (tabulka.stlpec) vo všetkých SQL príkazoch v procedúrach
• Premenné sa deklarujú pomocou odvodených typov - %TYPE a %ROWTYPE

• V PL je potrebné používať natívne SQL všade, kde je to možné a rozumné. Pokiaľ by sa naskytla potreba príliš komplikovaného dotazu, je možné, že PL kód bude rýchlejší. Je lepšie nepoužívať dynamické SQL. Častokrát je lepšie cyklus vo funkcií nahradiť klasickým SELECTom obsahujúcim konštrukciu CASE.

  1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39

  -- neefektívny kód IF _cena <= _cena_max THEN INSERT INTO tab(id, cena) VALUES(_id, _cena); ELSE INSERT INTO tab(id, cena) VALUES(_id, _cena_max); END IF; -- efektívny kód INSERT INTO tab(id, cena) VALUES(_id, CASE WHEN _cena <= _cena_max THEN _cena ELSE _cena_max END); -- neefektívny kód FOR _c1, _c2 IN SELECT c1,c2 FROM tab1 LOOP IF _c1 > 20 THEN INSERT INTO tab2 VALUES(20,_c2); ELSE INSERT INTO tab3 VALUES(_c1,_c2); END IF; END LOOP; -- efektívny kód INSERT INTO tab2 SELECT 20,c2 FROM tab1 WHERE c1 > 20; INSERT INTO tab3 SELECT c1,c2 FROM tab1 WHERE c1 <= 20; -- neefektívny kód FOR i IN array_lower(delitems,1) .. array_upper(delitems,1) LOOP DELETE FROM tab WHERE tab.id = delitems[i]; END LOOP; -- efektívny kód DELETE FROM tab WHERE tab.id = ANY(delitems);

• Funkcia má obsahovať iba jeden príkaz RETURN - jedna cesta dovnútra, jedna cesta von.

• Vyhýbať sa redundantnému kódu - v aplikácií by sa nemal objaviť dvakrát rovnaký kód.

• Je potrebné využívať funkciu Assert, prípadne jej modifikácie:

  1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18

  CREATE OR REPLACE FUNCTION Assert(bool, varchar) RETURNS void AS $$ BEGIN IF NOT $1 OR $1 IS NULL THEN IF $2 IS NOT NULL THEN RAISE EXCEPTION 'Assert failure: %', $2; END IF; RAISE NOTICE 'Assert. Message is null'; END IF; END; $$ LANGUAGE plpgsql; CREATE OR REPLACE FUNCTION Assert_IsNotNull(anyelement, varchar) RETURNS void AS $$ BEGIN PERFORM Assert($1 IS NOT NULL, $2); END; $$ LANGUAGE plpgsql;

• Treba dodržiavať predom dohodnuté notácie pre texty výnimiek, dopredu sa dohodnúť na zozname užívateľom definovaných chyb a chybových hlásení.

• Netestovať na NULL premennú, ktorá je deklarovaná ako NOT NULL. Je na PL, aby zaistil, že tato premenná v žiadnom prípade nebude obsahovať NULL. Pre zaistenie úspešnosti dotazu netestujeme obsah premenných modifikovaných dotazom, ale vždy obsah logickej systémovej premennej FOUND.
• Nepoužívať návestie <> pre cykly a bloky, treba využiť typ boolean, tak aby kód bol, čo možno najčitateľnejší.
• Nespoliehať sa na automatické konverzie typu date a timestamp, ktoré závisia od konfigurácie. Je lepšie použiť funkcie to_char a to_date.
• Nepoužiť IF pre naplnenie logickej premennej:

  is_ok := _age > 18;
  

• Každú premennú treba použiť iba k jednému jasnému účelu. Z kódu treba odstrániť nepoužívané premenné.
• Predvolená kategória funkcie je VOLATILE. Pokiaľ v tele funkcie nepristupujete k tabuľkám a nepoužívame funkcie typu random(), currval() atď, je lepšie použiť kategórie IMMUTABLE alebo STABLE. Doba spracovania funkcie môže byť aj o polovicu kratšia.
• Nezapuzdrovať SQL príkazy do jednoduchých funkcií.
• Obmedzovať použitie kurzorov a dočasných tabuliek.
• Je odporúčané preferovať štandardizované funkcie pred vlastnými.
• V triggeroch “tajne” neopravujeme dáta, treba posúdiť, či nebude lepšie použiť CHECK.
• Každá procedúra by mala obsahovať maximálne 50 až 60 riadkov.
• K testovaniu použiť unit testy (k overeniu identifikácie chyby, k overeniu korektnosti opravy)
• Zapuzdriť vyvolanie výnimky do vlastnej procedúry.
• Vždy vo svojom kóde použiť iba jeden z dvoch možných spôsobov (výnimky a návratový kód) signalizácie chyby.

• Opakované priradenie do premennej (u typov varchar a array) zlúčiť do jedného výrazu.

  1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34

  --zle DECLARE v varchar; BEGIN v := 'a'; v := v || 'b'; v := v || 'c'; RETURN v; END; --dobre BEGIN RETURN 'a' || 'b' || 'c'; END; -- zle DECLARE s varchar := ''; BEGIN IF x1 IS NULL THEN s := s || 'NULL,' ELSE s := s || x1; END IF; IF x2 IS NULL THEN s := s || 'NULL, ' ELSE s := s || x2; END IF; ... -- správne DECLARE s varchar; BEGIN s := COALESCE(x1 || ',', 'NULL,') || COALESCE(x2 || ',', 'NULL,')

• Funkcie obsahujúce iba jeden príkaz nepísať v PL/pgSQL, ale v SQL:

  1 2 3 4 5 6 7 8 9 10 11 12 13

  --zle CREATE OR REPLACE FUNCTION foo() RETURNS varchar AS $$ BEGIN RETURN 'a' || 'b' || 'c'; END; $$ LANGUAGE plpgsql IMMUTABLE; --dobre CREATE OR REPLACE FUNCTION foo() RETURNS varchar AS $$ SELECT 'a' || 'b' || 'c'; $$ LANGUAGE sql;

Inštalácia PL/pgSQL

re používanie PL jazykov sa musíme uistiť, či ich máme nainštalované. PostgreSQL podporuje veľa PL jazykov a sľubuje bezproblémový proces ich inštalácie.

Pre inštaláciu PL/pgSQL, stačí spustiť nástroj príkazového riadka a spustiť príkaz:

1

$ createlang plpgsql meno-databazy

PL/pgSQL je však už predvolene nainštalovaný v každej aj novo vytvorenej databáze. Spustenie príkazu vyššie, by malo iba vypísať že PL jazyk už je nainštalovaný.

Predpokladajme, že chceme nainštalovať tento jazyk do testovacej databázy z názvom – test_db. Spustíme teda príkaz (v príklade použitý Linux shell):

1

$ createlang plpgsql test_db

Tento príkaz však predpokladá, že užívateľ operačného systému je rovnaký ako super užívateľ (SuperUser) danej databázy. Pokiaľ nie je, musíme zadať meno super užívateľa databázy za prepínač -U. Príklad:

1

$ createlang plpgsql test_db -U postgres

Štruktúra funkcie v PL/pgSQL

PL/pgSQL má blokovú štruktúru a ide o jazyk citlivý na veľké a malé písmena (ang. case sensitive). Blok obsahuje príkazy vo vnútri kľúčových slov DECLARE /BEGIN a END
Blok môže byť definovaný, následovne:

1
2
3
4
5

DECLARE
	deklarácia premenných
BEGIN
	príkazy
END;

Autori popisujú PL ako jazyky blokovo orientované (všetky príkazy sú v nejakom bloku, bloky je možné do seba ľubovoľne vnárať). Blok má zmysel hlavne pri deklarovaní lokálnych premenných. Bloky sa na rozdiel od Pascalu, nepoužívajú k vymedzeniu zoznamu príkazov v konštrukciách IF, WHILE, FOR, LOOP, ale iba k vymedzeniu existencie niektorých lokálnych premenných. Nakoľko bloky hrajú veľkú rolu pri funkciách, máme dostatočné poznatky na vytvorenie funkcie. Začneme teda rozkladať štruktúru funkcie a popisovať element za elementom.
Príkaz CREATE FUNCTION používame práve pre vytvorenie funkcií. Ako prvé treba definovať meno funkcie. Ďalej definujeme jej argumenty, návratovú hodnotu a v poslednom rade sekciu deklarácií. Syntax funkcie:

1
2

CREATE OR REPLACE FUNCTION meno_funkcie (argumenty)
RETURNS type AS

Od verzie 7.2.1 môžeme použiť frázu CREATE OR REPLACE. V predchádzajúcich verziách sa musela funkcia pred jej opakovaným vytvorením daná funkcia zrušiť príkazom DROP FUNCTION. Pretože je možné funkcie preťažovať, musí sa príkazu DROP FUNCTION predať nie len názov funkcie, ale aj typy argumentov, aby podľa nich mohol PostgreSQL jednoznačne identifikovať správnu verziu funkcie k zmazaniu. Samozrejme funkciu je možné odstrániť iba pokiaľ na to máme práva. Môžeme zmazať iba užívateľské funkcie (user defined functions), teda funkcie vytvorené pomocou CREATE FUNCTION, nemažeme interné funkcie PostgreSQL. Od verzie 7.3 môžeme určiť, s akými oprávneniami sa bude funkcia vykonávať. Funkcia získa buď práva užívateľa, ktorý funkciu spúšťa (EXTERNAL SECURITY INVOKER), alebo práva vlastníka funkcie (EXTERNAL SECURITY DEFINER). Čiže volajúci – SECURITY INVOKER vlastník – SECURITY DEFINER. Pričom predvolená hodnota každej novo vytvorenej funkcie je SECURITY DEFINER. Kľúčové slovo EXTERNAL je nepovinné.

Vo vnútri bloku, deklarujeme premenné a je možné nastaviť aj predvolenú hodnotu.
Blokovú sekciu identifikujeme pomocou kľúčového slova DECLARE. Sekcia sa skladá z mena premennej, údajového typu a končí bodkočiarkou. Každá premenná, riadok alebo záznam použitý vo vnútri bloku, by mala byť deklarovaná v tejto sekcií, výnimkou je cyklus FOR.
Čo možno znázorniť nasledujúcim spôsobom:

1
2

DECLARE
	deklaracia;

Po definovaní mena funkcie, jej návratového typu a deklarácií premenných, sa zameriame na telo funkcie s kľúčovým slovom BEGIN.
Jeho syntax je:

1
2

BEGIN
	prikazy;

Sekcia príkazov, môže obsahovať nekonečno pod blokov. Vnorený kód bloku, prečíta interpreter ako obyčajný blok a metóda jeho zápisu je rovnaká ako pri bežnom PL/pgSQL bloku. Inak povedané, sekcia začína slovom DECLARE, nasleduje slovo BEGIN a príkazy vo vnútri tela ukončuje kľúčové slovo END. Je potrebné si zapamätať že BEGIN / END, nespúšťa a ani neukončuje akúkoľvek transakciu, ale slúži na zoskupenie príkazov.
Kľúčové slovo END ukončuje blok kódu:

1
2

END;
LANGUAGE 'plpgsql';

Hlavné telo PL/pgSQL funkcie, by malo vracať hodnotu predom definovaného typu a všetky pod bloky musia byť riadne uzavreté ešte pred ukončením hlavného bloku.

Príklad funkcie v PL/pgSQL

Vytvoríme si novú databázu:

1

createdb teoria_tst

Jednoduchú skúšobnú tabuľku:

1
2
3
4
5
6
7
8
9
10
11

teoria_tst=# CREATE TABLE tst_tabulka
(
	tst_id INTEGER NOT NULL,
	tst_meno TEXT NOT NULL,
	rok_narodenia INTEGER,
	adresa TEXT,
	mesto CHARACTER VARYING(100),
	stat CHARACTER VARYING(2),
	psc CHARACTER VARYING(10),
	CONSTRAINT "PRIM_KEY" PRIMARY KEY (tst_id)
);

Vložíme dáta do tabuľky:

1
2

INSERT INTO tst_ tabulka
VALUES (1, 'Rob', 1990, 'Vavretánia 240', 'Námestovo', 	'SK', '02901');

Túto databázu budeme používať na väčšinu príkladov v teoretickej časti tejto práce.

Vytvoríme funkciu pocetZaznamov(), ktorá vráti počet zapísaných záznamov do tabuľky tst_tabulka v databáze teoria_tst.

1
2
3
4
5
6
7
8
9

CREATE OR REPLACE FUNCTION pocetZaznamov()
RETURNS INTEGER AS $$
DECLARE
	pocet INTEGER;
BEGIN
SELECT COUNT(*) INTO pocet FROM tst_ tabulka;
RETURN pocet;
END;
$$ LANGUAGE plpgsql;

Po úspešnom vytvorení, funkciu zavoláme, načo sa vypíše jej návratová hodnota.

1
2
3
4
5

teoria_tst=# SELECT pocetZaznamov();
pocetZaznamov
-------------------
1
(1 row)

Po pridaní ďalšieho testovacieho riadku do tabuľky, funkcia samozrejme vráti číslo dva a podobne. Takýmto spôsobom vytvárame funkcie v PL/pgSQL.

Komentáre v PL/pgSQL

Pre prehľadnosť kódu je odporúčané používať komentáre, tie je možné používať v PL, tak ako aj v ostatných programovacích jazykoch. Na výber je jednoriadkový komentár a komentár obsahujúci viacero riadkov.

1
2
3
4
5
6
7

- - jednoriadkový komentár
/* komentár */  Viac riadkový komentár.
/*
Viacriadkový
komentár
*/

Nasledujúci príklad funkcie v PL/pgSQL, ukazuje použitie komentárov vo vnútri funkcie. Pričom je vidieť, že komentáre zvyšujú prehľadnosť a hlavne čitateľnosť kódu.

1
2
3
4
5
6
7
8
9
10
11
12

teoria_tst=# CREATE OR REPLACE FUNCTION spojRetazce (text, text)
--
--Tato funkcia spoji dva reťazce dokopy
--
/* spojovací znak „ || “ (pipe)
zlúči
oba retazce*/
RETURNS text AS $$
BEGIN
	RETURN $1 || ' ' || $2;
END;
$$ LANGUAGE plpgsql;

Použitie funkcie:

1
2
3
4
5

teoria_tst=# SELECT spojRetazce('foo', 'bar');
spojRetazce
------------
foo bar
(1 row)

Deklarácia premenných v PL/pgSQL

Premenné slúžia na ukladanie dát, aj PL/pgSQL umožňuje použitie premenných. Každá premenná je použiteľná počas doby života bloku, a musí byt deklarovaná vo vnútri bloku DECLARE. Od verzie PostgreSQL 9.0, nemôže názov premennej byť zároveň kľúčovým slovom v SQL pravdepodobne dôjde k zobrazeniu syntaktickej chyby. V predchádzajúcich verziách sa ako identifikátory premenných nemohli použiť iba kľúčové slová v PL/pgSQL. PostgreSQL čoby databáza nepozná koncept premennej, prípadne lokálnej premennej. Všetká práca s premennými je implementovaná v runtime PL/pgSQL. Každá funkcia získa pri štarte stavovú premennú, ktorá okrem iného obsahuje aj vektor všetkých premenných. Premenné sa očíslujú indexom v tomto vektore - a neskôr pri ich použití sa kopírujú do vektoru parametrov SQL príkazu.
Použitie premenných si ukážeme na kóde z funkcie pocetZaznamov():

1
2
3
4

CREATE OR REPLACE FUNCTION pocetZaznamov()
RETURNS INTEGER AS $$
DECLARE
	pocet INTEGER;

Pričom vidíme, že pri deklarácií premennej pocet je zvolený údajový typ INTEGER. Čo znamená, že táto premenná dokáže uložiť iba dáta celočíselného typu.
Syntax deklarácie premenných:

1
2

meno [ CONSTANT ] typ [ COLLATE znakova_sada ] [ NOT
NULL ] [ { DEFAULT | := } výraz

Pridanie premennej s kľúčovým slovom CONSTANT, zabezpečí, že hodnota tejto premennej bude konštantná behom spustenia bloku. Slovo COLLATE reprezentuje pravidlá pre usporiadanie reťazcov. Dáta v konkrétnom stĺpci je možné radiť raz podľa slovenských pravidiel COLLATE “sk_sk.utf8”, a druhý raz podľa iných pravidiel (COLLATE “en_US.utf8”). Premenné je možne deklarovať s prednastavenou hodnotou. Pokiaľ táto hodnota nie je nastavená, premennej sa priradí hodnota NULL. Hodnotu je možné nastaviť už v časti DECLARE, pomocou operátora priradenia „ := “. NOT NULL používame v prípade, keď nechceme nastaviť predvolenú hodnotu premennej na NULL. V praxi sa osvedčilo takýmto premenným vždy priradiť hodnotu už pri deklarácií.
Do budúcna je dobré si zapamätať, že zakaždým spustením bloku sú vyhodnotené a priradené prednastavené hodnoty premennej. Funkcia func_deklaracia() v nasledujúcej ukážke poukazuje ako zadefinovať konštantu s predvolenou hodnotou „10“. V ukážke tiež vidíme ako deklarovať premennú s NOT NULL a textovú premennú ,do ktorej priradíme hodnotu. 1

1
2
3
4
5
6
7
8
9
10
11
12
13
14

teoria_tst=# CREATE OR REPLACE FUNCTION func_deklaracia()
RETURNS text AS $$
DECLARE
	-- Premenná NOT NULL a priradenie hodnoty
	 VARCHAR NOT NULL := 'not null text';
	-- Deklarácia konštanty
	digit CONSTANT INTEGER := 10;
	/* Premenná s prednastavenou
	textovou hodnotou */
	helloworld VARCHAR DEFAULT 'PostgreSQL rocks !';
BEGIN
	RETURN helloworld;
END;
$$ LANGUAGE 'plpgsql';

Výpis funkcie:

1
2
3
4
5

teoria_tst=# SELECT func_deklaracia();
func_deklaracia
--------------------
PostgreSQL rocks !
(1 row)

Deklarácia parametrov funkcie

Funkcie dokážu prijímať a vracať hodnoty, tieto hodnoty v programovaní nazývame parametre funkcie, či argumenty. Argumenty musia byť deklarované pred ich použitím. Parametre funkcie sú označené numerickým identifikátorom $1 a $2. Tieto symboly $1 a $2 sa používajú vo význame hodnoty prvého a druhého argumentu funkcie. Pre prístup k referenčným hodnotám parametrov, použijeme numerický identifikátor alebo použijeme ALIAS (ang. Prezývka). 1
Prezývku – ALIAS priradíme parametru, následujúcim spôsobom:

1

meno ALIAS FOR $n;

Použitie v príklade:

1
2
3
4
5
6
7
8

CREATE OR REPLACE FUNCTION ukazka_alias(int)
RETURNS integer AS $$
DECLARE
	total ALIAS FOR $1;
BEGIN
RETURN total*10;
END;
$$ LANGUAGE plpgsql;

Bežne však definujeme meno parametra už v príkaze CREATE FUNCTION:

1
2
3
4
5
6

CREATE OR REPLACE FUNCTION ukazka_alias(total int)
RETURNS integer AS $$
BEGIN
	RETURN total*10;
END;
$$ LANGUAGE plpgsql;

Funkcia po jej použití, vráti:

1
2
3
4
5

teoria_tst=# SELECT ukazka_alias(10);
ukazka_alias
---------------
100
(1 row)

Parametru funkcie je možné nastaviť aj typ. Poznáme tri typy parametrov: IN, OUT, INOUT. Funkcia deklarovaná s parametrom typu IN obsahuje hodnotu, ktorá bude odovzdaná funkcií. Pokiaľ nedeklarujeme typ parametra, bude ním predvolene IN. Typ OUT sa vráti ako výsledok funkcie. Obe typy sú efektívne, pokiaľ má funkcia vrátiť viacero výstupov bez deklarácie výstupu typu PostgreSQL. Parameter typu INOUT slúži obom spomínaným účelom. Ide teda parameter pre vstup a aj výstup funkcie. 2
Následujúca funkcia používa IN a OUT parametre:

1
2
3
4
5
6
7

teoria_tst=# CREATE OR REPLACE FUNCTION func_parametre(a int, IN b
int, OUT plus int, OUT minus int) AS $$
BEGIN
	plus := a + b;
	minus := a - b;
END;
$$ LANGUAGE plpgsql;

Použitie funkcie:

1
2
3
4
5

teoria_tst=# SELECT func_parametre(10, 5);
func_parametre
------------
(15,5)
(1 row)

Deklarácia atribútu %TYPE

Atribút %TYPE používame pri ukladaní hodnôt z databázového objektu, zvyčajne stĺpca tabuľky. Premenná s týmto atribútom ukladá hodnotu rovnakého údajového typu ako tá, na ktorú odkazuje. Čo je nápomocné hlavne pri budúcej zmene údajového typu stĺpca.
Deklarácia:

1

meno_premennej meno_tabulky.meno_stlpca%TYPE

Naša databáza teoria_tst obsahuje stĺpec rok_narodenia v tabuľke tst_tabulka. Premennú odkazujúcu na tento stĺpec deklarujeme, takto:

1

rok tst_tabulka.rok_narodenia%TYPE

Údajový typ záznam a riadok tabuľky

Premenná typu riadok sa deklaruje na používateľom definovanej tabuľke alebo pohľade použitím príkazu meno_tabulky%ROWTYPE. K jednotlivým riadkom je možné pristupovať bodkovou notáciou, napríklad – riadok.pole . Premenná deklarovaná ako typ ROWTYPE dokáže uložiť riadok z výsledku dotazu SELECT alebo FOR. Typ záznam (RECORD) je podobný ROWTYPE s výnimkou, že nemá žiadnu preddefinovanú štruktúru a dokáže akceptovať riadky z ktorejkoľvek tabuľky. Čo znamená, že je možné zmeniť štruktúru za každým pripojením na riadok. Ide o konštrukciu pre iteráciu naprieč množinou záznamov špecifikovanou príkazom SELECT či FOR.

Výrazy

Výrazy používame v procedurálnom programovaní kedykoľvek chceme priradiť hodnotu k premennej, zavolať funkciu alebo použiť podmienený výraz ako je IF a ELSE. Poradie spustenia výrazov, kontrolujeme ich organizáciou. Každý výraz je zakončený bodkočiarkou.

Príkaz priradenia

Ide o najčastejšie používaný príkaz. Priraďujeme nim premenenej svoju hodnotu.
Syntax príkazu:

1

ciel := hodnota;

Kde , cieľ môže byt čokoľvek, premenná, stĺpec, parameter funkcie, riadok, ale nie konštanta. Počas spustenia, sa zo všetkých operácii prednostne priradia hodnoty do premenných. Pokiaľ sa typ premennej a hodnota nezhodujú, PostgreSQL sa hodnotu pokúsi konvertovať alebo vypíše chybu.

Zavolanie funkcie a jej návratová hodnota

Všetky PostgreSQL funkcie vracajú hodnotu. Vytvorenú funkciu spustíme jednoducho príkazom SELECT.
Niekoľko názorných príkladov:

• The SELECT identifikator_funkcie() fukcia:

  1	SELECT identifikator(argumenty);

• Priradenie prednastavenej hodnoty funkcií identifikator_funkcie():

  1	identifikator_premennej:=identifikator(argumenty);

• Príkaz SELECT a AVG(riadok) funkcia:

  1	SELECT AVG(tst_id) FROM tst_tabulka;

• Príkaz SELECT a funkcia pocetZaznamov():

  1	SELECT pocetZaznamov();

Výraz RETURN

Pokiaľ funkcia úspešne skončí, vyhodnotí sa hodnota výrazu, ktorá bude mať návratovú hodnotu a to takého typu ako je špecifikované v príkaze CREATE FUNCTION.
Syntax príkazu RETURN je nasledujúca:

1
2
3
4
5
6
7
8
9

CREATE OR REPLACE FUNCTION 		identifikator_funkcie(argumenty)
RETURNS TYPE AS
DECLARE
	deklaracna_cast;
BEGIN
výrazy;
RETURN { meno_premennej | hodnota}
END;
LANGUAGE 'plpgsql;

Chyby a výnimky

Pokiaľ dôjde k chybe v syntaxe alebo výnimke, beh programu skončí chybovým hlásením. Vyvolané výnimky je však možné zachytiť. Pre odchyt takejto výnimky implementujeme do funkcie programu príkaz RAISE.

1

RAISE NOTICE ''Pokus o delenie nulou'';

Prvým argumentom príkazu RAISE je úroveň výnimky. K dispozícii sú tri možnosti: DEBUG – zapíše sa do logu, NOTICE – oznámi sa užívateľovi, EXCEPTION – preruší sa vykonávanie funkcie. Druhým parametrom je text chybového hlásenia (text zapisujeme medzi zdvojené apostrofy). Pokiaľ sa v texte objaví symbol %, potom sa tento symbol nahradí odpovedajúcou premennou, ktorá se predá ako tretí, štvrtý, atď. argument. V PL/pgSQL nie je možné zachytiť výnimku, t. j. každá výnimka na úrovni EXCEPTION vedie k prerušeniu chodu funkcie. RAISE NOTICE sa bežne používa pre zobrazenie ladiacich hlásení, zobrazeniu obsahu premenných. V podstate je to jediný ladiaci prostriedok, ktorý je k dispozícii. RAISE DEBUG prepošle špecifikovaný text ako odlaďovaciu správu do PostgreSQL logu a klientského programu, pokiaľ je klient pripojený do databázového clustra so spustením módu debug. DEBUG je v produkčnom móde ignorovaný. RAISE EXCEPTION pošle špecifický text ako správu o chybe klientskému programu a PostreSQL logu. Tiež prerušuje prebiehajúci chod funkcie. 1

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21

CREATE FUNCTION raise_test () RETURNS integer AS '
  DECLARE
   		cislo INTEGER = 1;
 	BEGIN
 	  	 -- Raise debug správa.
 	  	RAISE DEBUG ''Raise_test() funkcia začala.'';
   		cislo = cislo + 1;
   		 -- Raise notice poukazuje
	 -- na zmenu premennej - cislo
   		RAISE NOTICE
		''Hodnota premennej cislo bola pozmenená.'';
	 -- Vypise sa nova hodnota premennej
   		RAISE NOTICE
		''Nova hodnota premennej je: %.'',cislo;
   		 -- Raise exception.
   		RAISE EXCEPTION
		''Hodnota % sa zmenila.
			Funkcia prerušená.'',cislo;
   		RETURN 1;
 	END;
$$ LANGUAGE plpgsql;

Výsledok po spustení funkcie:

1
2
3
4

teroria_tst=# SELECT raise_test();
NOTICE:   Hodnota premennej cislo bola pozmenená
NOTICE:   Nova hodnota premennej je: 2
ERROR:    Hodnota 2 sa zmenila.  Funkcia prerušená.

Výstup DEBUG sa nezobrazil, pretože databáza neprebieha v mode debuging.

Lepším príkladom pre RAISE EXCEPTION bude následujúci príklad, ktorý kontroluje správnosť poštového smerovacie čísla.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36

CREATE OR REPLACE FUNCTION validacia_sk_PSC(psc TEXT)
	RETURNS boolean AS $$
	DECLARE
		cisla text;
	BEGIN
		-- REGEX zmaze vsetko co nie je cislo
		cisla := (SELECT regexp_replace
		(psc,'[^[:digit:]]','','g'));
		IF cisla = '' THEN
			RAISE EXCEPTION
		'PSC neobsahuje ziadne cisla	--> %', cisla
			USING HINT = 'Jedná sa o Slovenské PSČ?',
			ERRCODE = 'P9999';
		ELSIF length(cisla) < 5 THEN
			RAISE EXCEPTION
			'PSČ neobsahuje dostatočný počet číslic
			--> %', cisla
			USING HINT = 'PSČ má menej než 5 číslic',
			ERRCODE = 'P9998';
		ELSIF length(cisla) > 9 THEN
			RAISE EXCEPTION
			'Zbytočne veľa číslic --> %', cisla
			USING HINT =
			'PSČ obsahuje viac než 9 číslic.',
			ERRCODE = 'P9997';
		ELSIF length(cisla) > 5 AND length(cisla) < 9 				THEN
			RAISE EXCEPTION
				'PSČ nie je validné --> %', cisla
			USING HINT =
			'PSČ ma abnormálny počet číslic	.',
			ERRCODE = 'P9996';
		ELSE
			RETURN true;
		END IF;
	END;
	$$ LANGUAGE plpgsql;

Každej výnimke môžeme priradiť špeciálnu ERRCODE hodnotu. Jej horná hranica je P9999, hodnota symbolizuje typ danej chyby. Ide o veľmi zjednodušený postup a je odporúčané neprekrývať chybové kódy definované v PL/pgSQL(napr. P0001).
Následujúci príklad funkcie zachytáva chyby, vytvorené v predchádzajúcej funkcii:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20

CREATE OR REPLACE FUNCTION ziskaj_SK_stav_validacie_PSC(psc TEXT)
RETURNS TEXT AS $$
BEGIN
 	 -- Perform je ako SELECT
	 -- ibaze vrati iba jeden riadok napr. True
 	PERFORM validacia_sk_PSC(psc);
 	RETURN 'PSČ je validne.';
  	EXCEPTION
  	WHEN SQLSTATE 'P9999'
    		THEN RETURN 'Nejedna sa o SK PSC.';
  	WHEN SQLSTATE 'P9998'
  		THEN RETURN 'Malo cislic.';
  	WHEN SQLSTATE 'P9997'
    		THEN RETURN 'Privela cislic.';
  	WHEN SQLSTATE 'P9996'
    		THEN RETURN 'Dlzka cisla medzi 6 - 8 cislic.';
      	RAISE; -- Iná SQL chyba...
END;
$$
LANGUAGE 'plpgsql';

Výsledok funkcie:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33

teoria_tst=# SELECT ziskaj_SK_stav_validacie_PSC('34955');
ziskaj_SK_stav_validacie_PSC
------------------------------
PSČ je validne.
(1 row)
teoria_tst=# SELECT ziskaj_SK_stav_validacie_PSC('349587');
ziskaj_SK_stav_validacie_PSC
------------------------------
Dlzka cisla medzi 6 - 8 cislic.
(1 row)
teoria_tst=# SELECT ziskaj_SK_stav_validacie_PSC('3495878977');
ziskaj_SK_stav_validacie_PSC
------------------------------
Privela cislic.
(1 row)
teoria_tst=# SELECT ziskaj_SK_stav_validacie_PSC('BNHCGR');
ziskaj_SK_stav_validacie_PSC
------------------------------
Nejedna sa o SK PSC.
(1 row)
teoria_tst=# SELECT ziskaj_SK_stav_validacie_PSC('3467');
ziskaj_SK_stav_validacie_PSC
------------------------------
Malo cislic.
(1 row)

Vetvenie programu a cykly

Štruktúra podmienených výrazov a cyklov obsahuje príkazy, ktoré sa vykonajú po splnení podmienky.
Syntax pre podmienené výrazy:

1
2
3
4
5

IF podmienka THEN
	príkazy
ELSE
	príkazy
END IF;

Syntax pre príkaz cyklu:

1
2
3

LOOP
	príkazy
END LOOP;

IF-THEN

Najjednoduchšia forma podmieneného výrazu. Syntax príkazu IF-THEN:

1
2
3

IF boolean-podmienka THEN
	príkazy
END IF;

Spustenie príkazov medzi blokom THEN a END závisí od splnenia booleovskej podmienky. Pokiaľ sa teda podmienka splní, pričom jej hodnota bude TRUE, príkazy sa vykonajú. Inak bude blok kódu ignorovaný.
Nasledujúci príklad, poukazuje na jeden z mnohých prípadov použitia IF-THEN:

1
2
3
4

IF tst_id > 2 THEN
	UPDATE tst_tabulka
	SET mesto='Vavrečka'
	WHERE

IF-THEN-ELSE

V niektorých situáciách je potrebné zdôrazniť, ktoré príkazy se majú spustiť v prípade, že podmienka nie je splnená. V takom prípade pridáme sekciu ELSE (inak). Táto sekcia je nepovinná a nachádza sa vždy na konci výrazu IF. 1 Syntax je následujúca:

1
2
3
4
5

IF boolean-podmienka THEN
	príkazy
ELSE
	príkazy;
END IF;

Modifikujeme funkciu pocetZaznamov(), a použijeme blok IF-THEN-ELSE.

1
2
3
4
5
6
7
8
9
10
11
12
13

teoria_tst=# CREATE OR REPLACE FUNCTION pocetZaznamov()
RETURNS INTEGER AS $$
DECLARE
	pocet INTEGER;
BEGIN
	SELECT COUNT(*) INTO pocet FROM tst_tabulka;
	IF (pocet > 0) THEN
		RETURN pocet;
	ELSE
		RAISE NOTICE 'Tabuľka je prázdna';
	END IF;
END;
$$ LANGUAGE plpgsql;

Spustenie funkcie:

1
2
3
4
5

teoria_tst=# SELECT pocetZaznamov();
pocetZaznamov
------------
3
(1 row)

IF-THEN-ELSIF

Niektoré prípady vyžadujú viac ako dve vetvy programu, vtedy použijeme IF-THEN-ELSIF. Pokiaľ sa podmienka IF nesplní, príkazy sa ignorujú a vyhodnotí sa podmienený výraz ELSIF. Pokiaľ je pravdivý, spustia sa príkazy vo vnútri bloku.
Príkaz ELSIF najlepšie pochopíme použitím v príklade:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21

teoria_tst=# CREATE OR REPLACE FUNCTION vnorenie_if(body integer)
RETURNS text AS $$
DECLARE
	stav text;
BEGIN
	IF body > 33 THEN
	znamka := 'PRESIEL';
	RETURN znamka;
ELSIF body = 33 THEN
	znamka := 'PRIEMER' ;
	RETURN znamka;
ELSIF body < 33 THEN
	znamka := 'NEPRESIEL';
	RETURN znamka;
ELSE
	-- pokial nastane NULL, ziak nebol na skuske
	stav := 'Nedostavil sa na skusku';
	RETURN stav;
END IF;
END;
$$ LANGUAGE plpgsql;

Výsledok pri dosiahnutí 33 bodov z testu:

1
2
3
4
5

teoria_tst=# SELECT vnorenie_if(33);
vnorenie_if
---------
PRIEMER
(1 row)

Výsledok pri dosiahnutí 32 bodov z testu:

1
2
3
4
5

teoria_tst=# SELECT vnorenie_if(32);
vnorenie_if
---------
 NEPRESIEL
(1 row)

Výsledok pri dosiahnutí 34 bodov z testu:

1
2
3
4
5

teoria_tst=# SELECT vnorenie_if(34);
vnorenie_if
---------
 PRESIEL
(1 row)

Výsledok pokiaľ sa žiak nezúčastnil na teste:

1
2
3
4
5

teoria_tst=# SELECT vnorenie_if(null);
vnorenie_if
---------
Nedostavil sa na skusku
(1 row)

CASE

Direktíva vždy začína kľúčovým slovom CASE, vždy končí kľúčovým slovom END a vždy má aspoň jednu WHEN-THEN časť. Podmienka CONDITION sa vyhodnotí ako booleovský výraz (pravda alebo nepravda). Pokiaľ je pravda, výsledkom celého CASE je vyraz. Pokiaľ je nepravda, skúsi sa to v ďalšej WHEN-THEN časti. Pokiaľ nie je splnená žiadna CONDITION, výsledkom je výraz za ELSE. Pokiaľ nie je ELSE vyraz definovaný, tak už ostáva ako výsledok iba NULL.1
Syntax:

1
2
3
4

CASE
WHEN podmienka_boolean THEN
	Výrazy
END CASE;

Vytvoríme novú funkciu, ktorá obsahuje direktívu CASE:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18

CREATE OR REPLACE FUNCTION podmienka_case(body integer)
RETURNS text AS $$
DECLARE
	znamka text;
BEGIN
	CASE
		WHEN body >= 40 THEN
			znamka := 'PRESIEL';
			RETURN znamka;
		WHEN body <= 39 AND body > 0 THEN
			znamka := 'NEPRESIEL';
			RETURN znamka;
		ELSE
			znamka := 'Nedostavil sa na skusku';
			RETURN znamka;
	END CASE;
END;
$$ LANGUAGE plpgsql;

Výsledok pri dosiahnutí 30 bodov z testu:

1
2
3
4
5

teoria_tst=# SELECT podmienka_case(30);
podmienka_case
-------------
NEPRESIEL
(1 row)

Výsledok pri dosiahnutí 30 bodov z testu:

1
2
3
4
5

teoria_tst=# SELECT podmienka_case(40);
podmienka_case
-------------
PRESIEL
(1 row)

Cyklus LOOP

Zrejme nemôže existovať programovací jazyk bez konštrukcií cyklu. PL/pgSQL nie je výnimkou, obsahuje dokonca štyri rôzne konštrukcie: cyklus FOR IN interval pre prevedenie predom známeho počtu cyklov, cyklus FOR IN SELECT pre iteráciu cez všetky riadky výsledku SQL dotazu, a cykly LOOP a WHILE pre prevedenie dopredu neznámeho počtu iterácií príkazov. Prevedenie cyklu môžeme v všetkých štyroch prípadoch prerušiť príkazom EXIT [návestie][WHEN logický_výraz].
Syntax:

1
2
3
4

LOOP
	vysledok= vysledok -1
EXIT WHEN vysledok > 0;
END LOOP;

Cyklus implementujeme do novej PL/pgSQL funkcie, ktorá žiada číslo ako vstupný parameter. Toto číslo budeme v cykle násobiť známkou 10. Cyklus bude bežať pokiaľ hodnota známky nebude rovná alebo väčšia ako 100. 1

1
2
3
4
5
6
7
8
9
10
11
12
13
14

teoria_tst=# CREATE OR REPLACE FUNCTION cyklus_loop(cislo integer)
RETURNS integer AS $$
DECLARE
	znamka integer := 10;
BEGIN
	LOOP
		znamka := znamka * znamka;
		IF (grade >= 100) THEN
			EXIT;
		END IF;
	END LOOP;
	RETURN znamka;
END;
$$ LANGUAGE plpgsql;

Spustenie a výsledok funkcie:

1
2
3
4
5

teoria_tst=# SELECT cyklus_loop(10);
cyklus_loop
-------------
100
(1 row)

Cyklus WHILE

Cyklus WHILE-END WHILE zaistí opakované spúšťanie bloku SQL príkazov v prípade, že zadaný výraz je pravdivý. V prípade, že výraz je neplatný už pri zahájení príkazu, telo cyklu sa nespustí ani raz. Tento cyklus je podobný cyklu REPEAT-UNTIL, ktorý zaistí minimálne jedno vyhodnotenie tela cyklu.
Syntax:

1
2
3
4

WHILE podmienka_boolean
LOOP
	výrazy
END LOOP;

Ukážka obsahuje funkciu pre výpočet faktoriálu:

1
2
3
4
5
6
7
8
9
10
11
12
13
14

CREATE OR REPLACE FUNCTION faktorial(n INT)
	RETURNS INT AS $$
DECLARE
  	f INT DEFAULT 1;
  	pocitadlo INTEGER = n;
BEGIN
	WHILE pocitadlo > 1
	LOOP
		f := f * pocitadlo;
 		pocitadlo := pocitadlo - 1;
 	END LOOP;
 	RETURN f;
END;
$$ LANGUAGE plpgsql;

Spustenie funkcie:

1
2
3
4
5

teoria_tst=# SELECT cyklus_while(5);
cyklus_while
------------
250
(1 row)

Cyklus FOR

Ide o najčastejšie používaný druh cyklu. Tento druh cyklu pracuje vo vopred danom rozsahu hodnoty typu integer. Premennú s touto hodnotou nemusíme deklarovať v bloku DECLARE. Životnosť takejto premennej zaniká ukončením cyklu FOR. Verzia PostgreSQL 9.1 bola rozšírená o cyklus FOREACH – iteráciu nad poľom s možnosťou iterácií po riadkoch. 1
Syntax cyklu FOR:

1
2
3
4

FOR i in 1...10
LOOP
	výrazy
END LOOP;

Použitie cyklu FOR:

1
2
3
4
5
6
7
8
9
10
11
12

teoria_tst=# CREATE OR REPLACE FUNCTION 		cyklus_for(hodnota integer)
RETURNS integer AS $$
DECLARE
znamka integer := 2;
BEGIN
	FOR i IN 1..10
	LOOP
		znamka := znamka * hodnota;
END LOOP;
RETURN znamka;
END;
$$ LANGUAGE plpgsql;

Spustenie funkcie:

1
2
3
4
5

teoria_tst=# SELECT cyklus_for(2);
cyklus_for
----------------
2048
(1 row)

Cyklus FOR je možné použiť spolu s DML (Data Manipulation Language) príkazom SELECT a to pomocou kľúčového slova EXECUTE.

1
2
3
4
5
6
7
8
9
10
11
12
13
14

teoria_tst=# CREATE OR REPLACE FUNCTION
cyklus_for_a_select(dotaz VARCHAR)
RETURNS integer AS $$
DECLARE
	pocet integer := 0;
	zaznam_tabulky RECORD;
BEGIN
	FOR zaznam_tabulky IN EXECUTE dotaz
	LOOP
		pocet := pocet + 1;
	END LOOP;
	RETURN pocet;
END;
$$ LANGUAGE 'plpgsql;

Po spustení funkcie spolu s SELECT dotazom ako parametrom funkcie. Bude výsledok takejto funkcie počet riadkov v tabuľke.

1
2
3
4
5
6

teoria_tst=# SELECT
cyklus_for_a_select('SELECT * FROM tst_tabulka');
cyklus_for_a_select
----------------
2
(1 row)

Zdroje:

https://www.root.cz/clanky/efektivni-pouzivani-pl-pgsql/
http://postgres.cz/wiki/PL/pgSQL;