Hlavní navigace

PL/pgSQL prakticky

Pavel Stěhule 27. 7. 1999

SQL server PostgreSQL se stává stále populárnějším, zejména díky množství nabízených funkcí. Pokud si však nevystačíte ani s tím, co nabízí, můžete jeho možnosti relativně snadno rozšiřovat pomocí integrovaného jazyka PL/pgSQL. A co vlastně tento jazyk umožňuje?

O PL se dá říci asi následující: za prvé – je to extrémně zjednodušený programovací jazyk určený k psaní funkcích rozšiřující prostředí databáze, tj. obsluha triggerů, vlastní funkce a agregační funkce, za druhé – je velice dobře provázán s prostředím databáze. Alespoň do doby než bude k dispozici překladač do C bude použití PL vždy snižovat efektivitu db systému, na druhou stranu díky PL a jeho propojení s PostgreSQL máme k dispozici prostředek ve kterém snadno a přehledně pracujeme s logickým modelem db. aplikace a který se navíc, díky jeho jednoduchosti, dá snadno zvládnout.

Naprosto neoriginálně začnu s nejjednodušší možnou funkcí navrženou v PL. Jedná se o analogii, z každé příručky notoricky omílaného programu Hello Word!. Následující text uložte do souboru, např. ~/hello.sql. Přesměrováním vstupu front-endu psql uložíme program v databázi. Pro upřesnění musím dodat, PL neumožňuje psát klasické programy, jedná se o vlastní funkci mnou pojmenovanou Hello.

-- Začátek souboru

DROP FUNCTION Hello();
CREATE FUNCTION hello() RETURNS OPAQUE AS '
BEGIN
  RAISE NOTICE ''Hello, World!'';
  RETURN NULL;
END;
' LANGUAGE 'plpgsql'

-- Konec souboru

Uložíme do databáze příkazem psql demodb < ~/hello.sql. Poté spustíme front-end v interaktivním režimu psql demodb a necháme provést příkaz SELECT Hello(); Pokud máte správně nakonfigurovaný PostgreSQL měl by se na monitoru objevit text Hello, World!. Ve stávající konfiguraci PostgreSQL je třeba zvlášť přeložit interpret PL a zaregistrovat SQL příkazem CREATE PROCEDURAL LANGUAGE. Registrace platí pro aktivní databázi, tedy doporučuji provést registraci v databázi template1. Potom bude jazyk zaregistrován ve všech nově vytvořených databázích.

Nyní se vraťme k příkladu. Vymazání funkce příkazem DROP je jen
následováníhodný zvyk. Totiž v případě, že modifikujete nějakou funkci,
musíte předchozí verzi této funkce vymazat. Z tvaru příkazu CREATE FUNCTION
a zejména z jeho parametru LANGUAGE lze si domyslet, že můžete používat i
jiné programovací jazyky. Pokud si tak domýšlíte, pak si domýšlíte správně.
Na příkladu jednoduchou funkce, která nedělá nic jiného než vždy vrací
hodnotu jedna, vidíte použití jazyka SQL.

CREATE FUNCTION one() RETURNS int4 AS '
SELECT 1 AS RESULT
' LANGUAGE 'SQL';

Všimněte si, že text funkce je vlastně parametrem SQL příkazu. Drobná odbočka, SQL funkce lze tzv. přetěžovat, tj. můžeme mít několik funkcí stejného jména ale různých parametrů. Při volání funkce se systém sám snaží o přetypování parametrů (pokud je třeba), ne vždy se mu to však podaří a musíme mu napovědět explicitním přetypováním.

Dále si můžete všimnout, že příkazy PL jsou odděleny od sebe středníkem, nezáleží na řádkování (tj. nový řádek je ekvivalentní mezeře), že konstantní řetězce, tak jak je známe například z C nebo Pascalu (*char popř. string) se zapisují mezi zdvojené jednoduché uvozovky. Je to trochu nepohodlné a poněkud neobvyklé, jak sami autoři přiznávají, ale lepší řešení zatím zřejmě neexistuje. Zkušený programátor jistě nenechá bez povšimnutí dvojici BEGIN END, zřejmý to symptom strukturovaného jazyka. Tvarem řídících konstrukcí PL více připomíná jazyky vycházející z Wirthovi Moduly (jako je Oberon, Visual Basic) než-li Pascalu. Z příkladu již nevypozorujete, že PL je case insensitive, česky – v kódu nezáleží na velikosti písma, tedy identifikátor HELENA je ten samý identifikátor i když jej napíšete jako HeLeNa (Pascalisté ocenní, Céčkaři a jiní budou zuřit:-<). PL umožňuje dvojí způsob psaní komentářů. Zdvojením pomlčky – dáváte interpretu najevo, že další text, a to až do konce řádku, má být považován za komentář. Mimochodem, tento způsob psaní komentářů můžete použít i v SQL příkazech. Druhý způsob je znám z C a C++. Komentáře jsou omezeny dvojznaky /* a */.

Autoři sami popisují PL jako jazyk blokově orientovaný (všechny příkazy jsou
v nějakém bloku, bloky lze do sebe libovolně vnořovat). Blok má smysl
zejména pro deklarování lokálních proměnných. Tvar bloku je následující (v
obvyklé notaci, tj. hranaté závorky se nepíší, pouze poukazují na
nepovinnost části konstrukce.):

[<<návěstí>>]
[DECLARE deklarace]
BEGIN
  příkazy
END;

Návěstí se skutečně zapisuje mezi dvojici zdvojených znaků << a >>. Je nutné zdůraznit, že zde použité slova BEGIN a END nijak nesouvisí s příkazy SQL pro řízení transakcí. Ostatně PL neumožňuje transakce, a ačkoliv je to v některých případech nepohodlné, PL funkce běží ve vlastní transakci(pokud je interpretace funkce přerušena, zruší se všechny změny v databázi vyvolané příkazy funkce).

Velkou výhodou PL, např. vůči C, je možnost používání libovolných typů PostgreSQL přímo (bez ukazatelů na struktury) a se stejným jménem, tj. bez použití nejrůznějších prefixu a postfixů. Na následující funkci bych chtěl demonstrovat jednak parametrizaci funkce, tj. modifikovat chování funkce na základě předávaných parametrů, a použití řídící konstrukce IF. Funkce vrací absolutní hodnotu daného číselného argumentu.

DROP FUNCTION abs (float8);
CREATE FUNCTION abs (float8) RETURNS float8 AS '
BEGIN
  IF $1 < 0.0 THEN
    RETURN $1 * -1.0;
  ELSE
    RETURN $1;
  END IF
END;
' LANGUAGE 'plpgsql'

V příkazu CREATE FUNCTION jsou určeny přípustné typy argumentů funkce výčtem
typů argumentů oddělených navzájem čárkou. Funkce abs obsahuje pouze jeden
argument. Konstrukce IF má naprosto obvyklý tvar (snad převzatý z Moduly
nebo Visual Basicu). Pro ty kteří ještě neměli možnost se seznámit s těmito
jazyky doplním syntaxi:

IF logický_výraz THEN
  seznam_příkazů;
[ELSE
  seznam_příkazů;]
END IF;

S argumenty funkce se pracuje podobně jako např. v shellových skriptech,
první argument má identifikátor $1, další $2, atd. Pokud je potřeba, někdy
nezbytné (chceme-li použít argument jako identifikátor struktury a
zpřístupňovat si některé její složky tečkovou notací, tj. jméno.složka)
můžeme přiřadit argumentu alias. Použití aliasu je, snad, zřejmé z
následujícího příkladu:

CREATE FUNCTION logfunc1 (text) RETURNS datetime AS '
DECLARE
  logtxt ALIAS FOR $1;
BEGIN
  INSERT INTO logtable VALUES (logtxt, ''now'');
  RETURN ''now'';
END;
' LANGUAGE 'plpgsql'

Když jsem poprvé četl tento kód, nechápal jsem proč se do nějaké hodnoty
přiřazuje textová konstanta „now“. Skutečnost je taková, že interpret s
řetězcem „now“ provede implicitní přetypování, v tomto případě na typ
datetime, a přitom nahradí konstantní řetězec aktuálním datumem. Výše
uvedený příklad obsahuje jednu chybu (podle dokumentace), ne že bych zas tak
úplně pochopil v čem je jádro pudla. Funkce nám zajišťuje uložení nějakého
textu spolu s datumovým údajem (kdy insert proběhl) do nějaké log databáze.
Pokud bychom použili funkci v tomto tvaru, Postgres parser automaticky
převede „now“ na odpovídající konstantní datetime hodnotu, kterou pak již
pokaždé ukládá (ke všemu je to datum zhruba okamžiku uložení funkce).
Správně by funkce měla vypadat následovně:

CREATE FUNCTION logfunc2 (text) RETURNS datetime AS '
DECLARE
  logtxt ALIAS FOR $1;
  curtime datetime;
BEGIN
  curtime := ''now'';
  INSERT INTO logtable VALUES (logtxt, curtime);
  RETURN curtime;
END;
' LANGUAGE 'plpgsql';

Nyní je již vhodná chvíle zmínit podrobněji deklaraci proměnných. Všechny
proměnné jsou samozřejmě lokální (deklarujeme je v rámci nějakého bloku).
Pokud chceme pracovat s globálními proměnnými popř. persistentními musíme si
hodnoty uložit do db. tabulek. Pro globální proměnné se přímo nabízejí
dočasné, temp. tabulky. Deklarace má následující syntax (v rámci definice
bloku a části DECLARE):

jméno [CONSTANT] type [NOT NULL][DEFAULT|:=value]

Interpret kontroluje přiřazování hodnot proměnným a nedovolí změnit hodnotu,
pokud je deklarována jako konstantní. Obdobně, pokud proměnná je deklarována
jako NOT NULL a pokusíme se jí přiřadit hodnotu NULL interpret vyvolá
runtime chybu. Jelikož implicitní hodnota všech SQL typů je NULL, tak
všechny proměnné deklarované jako NOT NULL musí mít přiřazeny nějakou
počáteční (DEFAULT) hodnotu. Implicitní hodnota je vyhodnocena vždy, při
každém volání funkce. Pokud chceme uložit do proměnné nějakou strukturovanou
hodnotu, např. řádek tabulky databáze, obsahující několik hodnot, musíme
nadeklarovat proměnnou jako strukturovaný typ. Zde máme dvě možnosti, můžeme
použít typ jméno_tabulky%ROW­TYPE v případě, že dopředu víme o kterou tabulku
půjde, nebo konstrukt name RECORD, který je jistou obdobou Microsoftího typu
Variant. Také můžeme určit typ podle jiné proměnné, v tom případě použijeme
typ proměnná%TYPE nebo podle položky tabulky – typ tabulka.polož­ka%TYPE.
Pokud použijeme tyto odvozené typy, je velice pravděpodobné, že při změně
typu sloupečku tabulky, nebudeme nuceni modifikovat kód vlastních funkcí.

DROP FUNCTION foo();
CREATE FUNCTION foo(text) RETURNS int4 AS '
DECLARE
  cust_name ALIAS FOR $1;
  r customers%ROWTYPE;
BEGIN
  -- přečte první řádek tabulky customers a uloží jej do r,
  -- ostatní řádky tiše zapomene.
  SELECT * INTO r FROM customers WHERE name = cust_name;
  IS NOT FOUND THEN
    -- Pokud nebyl nalezen žádný odpovídající zákazník
    RAISE EXCEPTION ''Customers % not found'', cust_name;
  END IF;
  PERFORM writelog (''found'',r.id);
  RETURN r.id;
END;
' LANGUAGE 'plpgsql';

Uznávám, že ve funkci foo je toho najednou moc. Pokusím se popořadě osvětlit
dosud nevysvětlené. Již ve funkci logfunc1 byl použit SQL příkaz. SQL příkaz
SELECT má v PL mírně pozměněný tvar:

SELECT výraz INTO cíl FROM ...,

kde cílem je buďto proměnná typu RECORD nebo odpovídajícího ROWTYPu popř. seznam proměnných resp. RECORD/ROWTYPE proměnných oddělených navzájem čárkou (SELECT může vzniknout spojením dvou tabulek). FOUND v konstrukci IF je předdefinovaná proměnná, která obsahuje hodnotu „T“, pokud poslední příkaz SELECT vrátil alespoň jeden řádek, jinak „F“. Častou chybou, alespoň podle FAQ je, že se uživatel snaží parametrizovat SQL příkaz na nevhodném místě. Je třeba si uvědomit, že v příkazu SELECT (funkce foo) je cust_name parametr SQL příkazu vyhodnocovaný až při volání funkce, naopak např. customers je název tabulky, který nelze parametrizovat (je vyhodnocen a ověřen již při ukládání funkce).

Všechny funkce Postgresu vrací určitou hodnotu. Obvyklý způsob volání funkce je pomocí příkazu SELECT. V mnoha případech nám však nezáleží na návratové hodnotě, a také chceme tento fakt vyjádřit. Příkaz PERFORM jméno_funkce zajistí provedení funkce bez uložení návratové hodnoty.

Poslední, ve funkci foo, zatím nepopsanou konstrukcí je vyvolání vyjímky příkazem RAISE. Z příkladů je zřejmé, že má několik parametrů. První určuje úroveň vyjímky (přípustné hodnoty jsou DEBUG, NOTICE a EXCEPTION), druhý je textem hlášení, které přísluší k vyvolané vyjímce (viz. dále). Pokud tento text obsahuje jeden nebo více znaků %, je tento znak popř. více znaků nahrazen hodnotami proměnných předaných jako třetí až n-tý parametr příkazu RAISE. Pokud dojde k vyjímce EXCEPTION, přeruší se provádění všech funkcí a systém zahájí zpracování dalšího SQL příkazu zaslaného klientskou aplikací. Díky tomu je NEMOŽNÉ z PL uložit do databáze informace o příčinách, které způsobily vyjímku. Zachytit vyjímku PL neumožňuje. Pokud chceme přerušit interpretaci, musíme použít úroveň vyjímky EXCEPTION. Další dvě slouží pouze k zobrazení ladících informací.

Zřejmě nemůže existovat programovací jazyk bez konstrukcí cyklu. PL není vyjímkou, obsahuje dokonce čtyři různé konstrukce: cyklus FOR IN interval pro provedení předem známého počtu cyklů, cyklus FOR IN SELECT pro iteraci přes všechny řádky výsledku SQL dotazu, a cykly LOOP a WHILE pro provedení předem neznámého počtu iterací příkazů. Provádění cyklu můžeme ve všech čtyřech případech přerušit příkazem EXIT [návěstí][WHEN logický_výraz].

V některých případech potřebujeme zobrazit posloupnost výsledků určité
funkce. Poměrně elegantním řešením je naplnit tabulku posloupností čísel,
která nám vlastně představuje definiční množinu. Nad touto tabulkou pak
provedeme příkaz SELECT.

CREATE FUNCTION vyrob_mnozinu(float8,float8,float8) RETURNS BOOL AS '
DECLARE
  od ALIAS FOR $1;
  do ALIAS FOR $2;
  krok ALIAS FOR $3;
  i float8;
BEGIN
  i := od;
  -- Nelze použít příkaz FOR neboť jeho krok je 1
  WHILE (od <=i)AND(i<=do)
    INSERT INTO mnozina VALUES(i);
    i := i + krok;
  END LOOP;
END;
' LANGUAGE 'plpgsql';

I když si nedokážu představit, k čemu by to mohlo být užitečné, můžeme chtít
hypoteticky zobrazit hodnoty funkce Sin na intervalu ← 1,1>. Postup by byl
následují (v psql).

DELETE ALL FROM mnozina;
SELECT vyrob_množinu (-1.0, 1.0, 0.0001);
SELECT Sin(hodnota) AS Sin FROM mnozina;

Ve stávající verzi PostgreSQL nezná kontrolu referenční integrity. Pomocí PL
a triggerů můžeme tento drobný nedostatek snadno obejít. Odbočka, triggery
umožňují svázat určitou operaci na databázové tabulce s libovolnou funkcí,
např:

CREATE TRIGGER casznacka_zamestance BEFORE INSERT OR UPDATE
  ON zamestnanci FOR EACH ROW EXECUTE PROCEDURE casznacka_zamestnance();

Obecně, při psaní obsluhy triggerů, platí několik jednoduchých pravidel: za
prvé – funkce musí mít návratovou hodnotu deklarovanou jako OPAQUE, za druhé
- funkce vrací record odpovídající struktuře tabulky na které je s funkcí
spojený trigger definován.

DROP FUNCTION casznacka_zamestnance();
CREATE FUNCTION casznacka_zamestnance() RETURNS OPAQUE AS '
BEGIN
  NEW.casznacka := ''now'';
  RETURN NEW;
END;
' AS LANGUAGE 'plpgsql';

Tato obsluha triggeru zajistí, ať je příkaz UPDATE nebo INSERT jakýkoliv (musí být syntakticky správný) bude vždy atribut casznacka obsahovat čas poslední změny řádku. Identifikátor NEW představuje předdefinovanou proměnnou obsahující záznam odpovídající novému, resp. již upravenému řádku databáze. Předdefinovaných proměnných je více: proměnná OLD obsahuje odpovídá původní hodnotě řádku, proměnné TG_NAME, TG_WHEN, TG_LEVEL a TG_OP popisují příčiny vyvolání obsluhy triggeru.

Předpokládejme následující situaci. Tabulka divize obsahuje id a popis
divizí nějakého hypotetického podniku. Id je primárním klíčem této tabulky.
Další tabulka obsahuje seznam zaměstnanců, divize.id je cizím klíčem.
Podmínky referenční integrity jsou dvě. Atribut zamestnanci.di­vizeId může
obsahovat pouze přípustné hodnoty, tj. hodnoty z divize.Id. Řádek z tabulky
divize nesmí být zrušen, pokud se na jeho Id odkazuji někde v sloupečku
divizeId v tabulce zaměstnanci.

CREATE TABLE divize (
  Id int4 PRIMARY KEY,
  nazev text
);

CREATE TABLE zamestnanci (
  Id int4 PRIMARY KEY,
  divizeId int4;
  jmeno text,
  prijmeni text
);

CREATE FUNCTION fce_trg1() RETURNS OPAQUE AS '
DECLARE
  tmp divize%ROWTYPE;
BEGIN
  SELECT * INTO tmp FROM divize WHERE Id = NEW.Id;
  IF NOT FOUND THEN
    RAISE EXCEPTION ''Nelze pridat / modifikovat zamestnace %'', NEW.Id;
  END IF;
  RETURN NEW;
END;
' LANGUAGE 'plpgsql';

CREATE TRIGGER trg1 BEFORE INSERT OR UPDATE
  ON zamestnanci FOR EACH ROW EXECUTE PROCEDURE fce_trg1();

CREATE FUNCTION fce_trg2() RETURNS OPAQUE AS '
DECLARE
  tmp zamestnanci%ROWTYPE;
BEGIN
  SELECT * INTO tmp FROM zamestnanci WHERE divizeId = OLD.Id;
  IF FOUND THEN
    RAISE EXCEPTION ''Nelze zrusit / modifikovat divizi %'', OLD.nazev;
  END IF
  RETURN OLD;
END;
' LANGUAGE 'plpgsql';

CREATE TRIGGER trg2 BEFORE DELETE OR UPDATE
  ON divize FOR EACH ROW EXECUTE PROCEDURE fce_trg2();

Tak a to je zhruba vše.

Našli jste v článku chybu?
Podnikatel.cz: Přehledná titulka, průvodci, responzivita

Přehledná titulka, průvodci, responzivita

Podnikatel.cz: Přivýdělek u Airbnb nebo Uberu? Čekejte kontrolu

Přivýdělek u Airbnb nebo Uberu? Čekejte kontrolu

Vitalia.cz: Jak koupit Mikuláše a nenaletět

Jak koupit Mikuláše a nenaletět

Vitalia.cz: I církev dnes vyrábí potraviny

I církev dnes vyrábí potraviny

Lupa.cz: E-shopy: jen sleva už nestačí

E-shopy: jen sleva už nestačí

Měšec.cz: Zdravotní a sociální pojištění 2017: Připlatíte

Zdravotní a sociální pojištění 2017: Připlatíte

Vitalia.cz: „Připluly“ z Německa a možná obsahují jed

„Připluly“ z Německa a možná obsahují jed

Podnikatel.cz: Dárky v podnikání. Jak je uplatnit v daních?

Dárky v podnikání. Jak je uplatnit v daních?

DigiZone.cz: Další dva kanály nabídnou HbbTV

Další dva kanály nabídnou HbbTV

Vitalia.cz: Žloutenka v Brně: Nakaženo bylo 400 lidí

Žloutenka v Brně: Nakaženo bylo 400 lidí

DigiZone.cz: Flix TV má set-top box s HEVC

Flix TV má set-top box s HEVC

Podnikatel.cz: 1. den EET? Problémy s pokladnami

1. den EET? Problémy s pokladnami

Vitalia.cz: Paštiky plné masa ho zatím neuživí

Paštiky plné masa ho zatím neuživí

DigiZone.cz: Recenze Westworld: zavraždit a...

Recenze Westworld: zavraždit a...

Vitalia.cz: 9 největších mýtů o mase

9 největších mýtů o mase

Měšec.cz: Kdy vám stát dá na stěhování 50 000 Kč?

Kdy vám stát dá na stěhování 50 000 Kč?

Podnikatel.cz: Na poslední chvíli šokuje vyjímkami v EET

Na poslední chvíli šokuje vyjímkami v EET

Lupa.cz: Avast po spojení s AVG propustí 700 lidí

Avast po spojení s AVG propustí 700 lidí

Lupa.cz: Propustili je z Avastu, už po nich sahá ESET

Propustili je z Avastu, už po nich sahá ESET

Lupa.cz: Google měl výpadek, nejel Gmail ani YouTube

Google měl výpadek, nejel Gmail ani YouTube