Teoretický úvod

Pokud bychom používali PgSQL tak, jak bylo zatím ukazováno v seriálu, bylo by zbytečné tento server studovat a nasazovat, protože takto využijeme asi 5-10% možností PgSQL. Většina databází je rozšižitelná zvenčí pomocí doplňků, které si můžete napsat sami, nebo v případě PgSQL použít contrib. Takovéto rozšiřování funkčnosti je ale nutné dělat ve spolupráci s administrátorem, který smí pracovat se systémem. Ne všechny databáze jsou, podobně jako PgSQL, rozšiřitelné ze strany uživatele. Tato uživatelská rozšíření se ukládají do takzvaných uložených procedur (stored procedures).

Pokud se podíváme ke konkurenčním databázovým serverům, tak tato funkčnost je dneska poměrně rozšířena. FirebirdSQL ji umí od počátku své existence, MySQL umožňuje použití stored procedur od 5. verze a SQLite má jednoduchý nástroj v podobě uložených dotazů, ale je nutné nezapomínat na zaměření SQLite. Ve světě komerčních db serverů snad nenaleznete žádný, který by stored procedury neuměl. Obvyklým jazykem pro psaní stored procedur je PL/PgSQL, který představuje jakousi nadmnožinu SQL. PostgreSQL je na tom ale ještě lépe. Pro stored procedury můžete použít jazyky PL/Perl, PL/Python a PL/Tcl. Poměrně čerstvou novinkou v PgSQL je podpora standardního PL/PSM, jak je u PgSQL zvykem, pojmenované PL/pgPSM, přičemž na začlenění tohoto jazyka, mocnějšího, než klasický PL/PgSQL, má český vývojář Pavel Stěhule.

Kdy uložené procedury oceníte?

• Když máte část kódu, funkčnost, která je v klientech a ta je přepsatelná. Pokud máte několik různých klientů, kteří přistupují k jediné databázi a často využívají některé funkce. Je výhodné tuto funkčnost přepsat do stored procedury, kterou máte uloženu na serveru. Oprava/změna v této proceduře znamená změnu pro všechny klienty/uživatele, nemusíte kvůli tomu upravovat a uživatelům distribuovat nové verze klientských aplikací.
• Pokud děláte náročné operace s daty. Možná vás napadne, že v binární aplikaci budou operace rychlejší, než nascriptované na straně serveru. Pokud je funkčnost na serveru, odpadne neustálé analyzování dotazů poslaných na server, konverze proměnných mezi serverem a klientskou aplikací a mimo jiné se výrazně zmenší provoz mezi aplikací a serverem. Stored procedura je uložena v databázi, pracuje s daty přesně tak, jak jsou na serveru a především je zanalyzována.
• Nejen náročné operace, ale i složité dotazy, případně dynamicky generovaná omezení výběrů, má-li tato dynamičnost rozumné pravidla, jsou výborným tématem k použití stored procedur.

Využití stored procedur se dá považovat za další stupeň k vícevrstvým aplikacím. Zatímco některé aplikace lze napsat jako jednovrstvé (většina her), kdy všechno řeší program, je manipulace s databázemi příklad dvouvrstvé aplikace, kde jedna vrstva je aplikace jako taková a druhou vrstvou je databáze (lhostejno, běží-li společně na jednom počítači, nebo na různých). Třívrstvé aplikace mají mezi klientskou část a databázový server zařazen ještě aplikační server. Pochopitelně existují i další vrstvy, ale to se jedná, většinou, o rozvrstvení aplikačního serveru. Použití stored procedur se dá považovat za dvaapůl vrstvou aplikaci, některé části kódu jsou na serveru, ale není to plný aplikační server.

My se teď budeme věnovat rozšiřování funkčnosti pomocí stored procedur. Contribové přídavky k PgSQL si necháme na pozdější dobu i z důvodů velké živosti contribu, protože občas je některé rozšíření uznáno za natolik kvalitní, že je vyjmuto z contribu a přesunuto do hlavního vývojového stromu PgSQL.

Praktický začátek

Pokud chcete použít některý z programovacích jazyků, pro psaní stored procedur, je potřebné mít nainstalovánu jeho podporu, technická realizace tohoto kroku závisí na balíčkovacím systému a balíčcích, jaké máte v systému. Například v Debianovské distribuci nainstaluji podporu pro PL/Perl v PostgreSQL 8.2 jednoduchým příkazem aptitude install postgresql-plperl-8.2.

Dalším krokem je zapnutí podpory tohoto jazyka pro vybranou databázi. Potud to vše vypadá velmi jednoduše, čiže upozorníme na drobný zádrhel. Nativním a nejrychlejší jazyk je PL/PgSQL, který, zejména začátečníkům, na první pohled nevypadá jako mocný nástroj. Ostatní jazyky, ať je to Perl, Python, nebo jiný, spouští vnější interpet, proto jsou pomalejší. Jak je v PgSQL obvyklé, řada administračních věcí se dá zrealizovat na úrovni SQL i konzolovými scripty. Z konzole lze použít příkaz createlang, jehož parametry zjistíte obligátním parametrem --help.

createlang --list  //napred se podivame, jake jazyky mame pro db aktivni
createlang plpgsql mzdy  //pro databazi mzdy aktivujeme PL/PgSQL

Jen pro úplnost dodám, že dodanou podporu jazyka pro databázi lze kdykoliv odebrat jednoduchým příkazem:

droplang --list mzdy //napred se podivame, jake jazyky mame pro db aktivni
droplang plpgsql mzdy  //z databaze mzdy odebereme PL/PgSQL

Protože ne vždy chceme vše dělat z terminálu, ale chceme povolit tvorbu stored procedur i z programu, existuje možnost zavést jazyk stored procedur i z SQL. K tomu slouží příkaz:

CREATE [PROCEDURAL] LANGUAGE jmeno_jazyka;
CREATE [TRUSTED] [PROCEDURAL] LANGUAGE jmeno_jazyka HANDLER vol_handler
  [VALIDATOR validat_funkce]

Kde význam jednotlivých parametrů je v dokumentaci, ale nám v tento moment bude postačovat ta nejjednodušší forma:

-- Napriklad:
CREATE LANGUAGE plpgsql;

Podobně pro odebrání jazyka z databáze slouží DROP LANGUAGE jmeno_jazyka; a pro předefinování jazyka ALTER LANGUAGE jmeno_jazyka RENAME TO nove_jmeno.

Pokud máme jazyk již zavedený, můžeme si napsat první vlastní stored proceduru:

CREATE FUNCTION dph(float, int)
RETURNS float
AS 'SELECT ($1 * ($2/100.0))::float;'
LANGUAGE 'sql';

Zkuste nyní tuto funkci zavolat pomocí SELECT dph(15000, 19); a uvidíte výsledek.

Závěr

Tato kapitola byla naprostým úvodem ke stored procedurám. Ano na konci minulého dílu jsem vám slíbil triggery, ale ty jsou bez stored procedur poněkudsi bezzubým nástrojem. Příště postoupíme v práci a definici stored procedur v PL/PgSQL.