Příručka SQL/PSM

Úvod do SQL/PSM (PL/pgPSM)

Začátkem devadesátých let minulého století začalo být jasné, že standard ANSI SQL postrádá prostředky pro tvorbu uložených procedur (zejména možnost deklarovat proměnné a dále s nimi pracovat, řízení toku - cykly a podmínky). Na tuto skutečnost reagovaly komerční firmy implementací vlastních proprietárních prostředí. Zřejmě nejpopulárnějšími v té době byly jazyky PL/SQL (Oracle, 1992), T-SQL (Sybase a Microsoft, 1995) a SPL (Informix, 1996). Od roku 1990 se v standardizační komisi ANSI SQl této problematice začala věnovat skupina vývojářů kolem Jima Meltona. Nejstarší zmínka o SQL/PSM je ze srpna roku 1994. (Prvním výstupem bylo PSM-96 (PSM - Persistent Stored Modules) postavené nad SQL92. Ve svém článku Budoucnost programování v SQL z ledna 1997 zmiňuje Joe Celko SQL/PSM jako jazyk vycházející z Algolu rozšířený o prvky Ady (řešení výjimek). V roce 1998 (draft) standard byl již součástí SQL3 a označen současným názvem SQL/PSM (ANSI/ISO/IEC 9075-4:1999). Bohužel v té době většina významných firem již měla své vlastní prostředí (nekompatibilní se standardem), které, jak se ukázalo později, neopustily. Standard byl implementován pouze v těch RDBMS, kde do roku 1998 nebyla možnost tvorby SQL uložených procedur. Kromě DB2 (SQL PL, IBM, 2001) se většinou jedná o minoritní RDNMS: Mimer, Solid, 602SQL server. Do popředí se SQL/PSM opět dostává po roce 2005, kdy je implementován v Advantage Database Serveru (Sybase iAnywhere, 2005), v MySQL (2005) a PostgreSQL (2007). Jen zřídka je SQL/PSM implementován v plném rozsahu. Za zdařilou implementaci se pokládá SQL PL v DB2. Implementace SQL/PSM v PostgreSQL se nazývá (jak je v PostgreSQL zvykem) PL/pgPSM.

Stručně lze programovací jazyk definovaný v SQL/PSM charakterizovat jako jednoúčelový, zcela nový, moderní, jednoduchý procedurální programovací jazyk s integrovaným SQL a úzkou vazbou na prostředí SQL serverů. Jedná se o jazyk s bohatým repertoárem řídících konstrukcí a komfortním modelem zachycení a zpracování chyb. Datové typy, funkce přebírá z hostitelského SQL serveru. I/O rutiny pak úplně chybí (přesně v duchu architektury uložených procedur). Standard SQL/PSM se nevěnuje pouze popisu SQL procedur. Obsahuje i popis uspořádání procedur do modulů a popis tzv. externích procedur (implementace procedur v dalších prg. jazycích, např. C, Cobol, atd). Přestože se jedná o zcela nový programovací jazyk, na celé řadě konstrukcí je zřejmá inspirace jazyky PL/1, jazyky ADA, Modula a dalšími jazyky.

Následující ukázka je modifikací klasického příkladu Hello world v prostředí SQL/PSM. Parametrem funkce je jedinečný identifikátor uživatele. Uvnitř funkce se na základě tohoto identifikátoru získá skutečné jméno a příjmení uživatele z tabulky Users. Všimněte si deklarace proměnné, plnění proměnné hodnotou z tabulky (integrace SQL příkazu SELECT), použití SQL datového typu (varchar) a SQL oparátoru (operátor ||), komentáře.

CREATE OR REPLACE FUNCTION hello(uid integer)
RETURNS varchar AS
$$
  BEGIN
    DECLARE real_name varchar;
    -- Get real name
    SET real_name = (SELECT name || ' ' || surname 
                        FROM Users 
                       WHERE Users.uid = hello.uid);
    RETURN 'Hello, ' || real_name;
  END;
$$ LANGUAGE plpgpsm;

SELECT hello(123);

Vložení zdrojového kódu funkce mezi dvojici symbolů $$ je specifické pro PostgreSQL. PL/pgPSM je z pohledu PostgreSQL externí programovací jazyk, jako ostatní podporované PL jazyky (PL/pgSQL, PL/Perl, PL/Python), a tudíž PostgreSQL k tomuto jazyku přistupuje stejně a vynucuje si stejná pravidla zápisu. Implementace SQL/PSM má ještě další dvě podstatné odlišnosti od standardu. Za prvé, PostgreSQL umožňuje definovat pouze funkce (nikoliv procedury), což se projevuje určitými omezeními v řízení transakcí. Za druhé, PostgreSQL implicitně spouští funkce v režimu SECURITY CALLER, kdežto standard předpokládá režim SECURITY DEFINER. Mezi standardem a implementací SQL/PSM v PostgreSQL je ještě několik dalších rozdílů daných určitou volností standardu a implementačními závislostmi, nicméně nejedná se o nijak zvlášť markantní rozdíly. Asi je ještě předčasné počítat s plnou přenositelností kódu mezi RDBMS podporujícími SQL/PSM, nicméně v celé řadě případů sou jsou nutné úpravy minimální. Problémy mohou nastat u normou neošetřených konstrukcí (unbound selects, multirecordsets, atd).

Jazyk SQL/PSM je v RDBMS PostgreSQL (PL/pgPSM) běží na modifikovaném run-time PL/pgSQL. Tudíž výkonnostně i funkčně je na tom velice podobně jako jazyk PL/pgSQL. PL/pgSQL je v tuto chvíli vyzrálejší a časem prověřené prostředí vhodné pro jakkoliv kritické aplikace. PL/pgPSM naopak přináší shodu se standardem a novější, a bohatší programovací jazyk.

Rozlišujeme mezi tzv. externími uloženými procedurami a SQL uloženými procedurami. Rozdíl je v použitém jazyce a v přístupu. Pokud uložené procedury jsou realizovány v klasických prg. jazycích, a běží mimo prostředí SQL serveru (vlastní funkce, vlastní datové typy), jedná se o externí procedury. V případě, že se použije specializovaný jazyk a procedura běží v prostředí SQL serveru (sdílí datové typy a funkce), pak takovou proceduru označujeme jako SQL proceduru. Příklady SQL procedur jsou prostředí PL/SQL, T-SQL, nebo PSQL či PL/pgSQL, PL/pgPSM. Externí procedury jsou standardizované pouze pro jazyk Java (SQL/J). Existuje ovšem celá řada dalších implementací např. CLR pro SQLServer 2005 nebo PL/Perl, PL/Python pro PostgreSQL. Obě třídy procedur mají své pro a proti, doporučuje se ale upřednostnění SQL procedur vyjma těch případů, kdy jsou neefektivní (např. iterační výpočet integrálu) nebo chybí dostatečná funkcionalita (např. I/O operace). Velkou výhodou SQL procedur je integrace s prostředím. Zpravidla nedochází k zbytečným konverzím, a obvykle se používají předzpracované SQL příkazy (prepared statements). Kromě toho všechny statické SQL příkazy jsou verifikované (což ještě neznamená, že jsou 100% správné, nicméně to znamená, že jsou syntakticky správné).

Praktické tipy návrhu uložených procedur v prostředí PL/pgPSM

V podstatě platí veškerá doporučení pro návrh jakéhokoliv software, tj. požadavky na čitelnost, srozumitelnost kódu:

dbejte na dodržování jmenné konvence,
používejte komentáře a názorné názvy proměnných,
používejte jednotnou konvenci pro odsazování bloků,
dodržujte rozumnou délku procedury (kolem 50 řádků),
používejte "upovídaný" styl .. využívejte volitelná návěstí smyček.

K tomu ještě specifická doporučení platná pouze pro SQL uložené procedury (bez ohledu na konkrétní prostředí):

pomocí prefixů a kvalifikovaných jmen atributů se vyhněte možným kolizím názvů sloupců a jmen proměnných,
jednotným způsobem řešte zachycení a ošetření chyb,
vyhněte se ISAM programování - co lze vyřešit pomocí SQL řešte pomocí SQL (pozor na cykly napříč celými tabulkami),
omezte kurzory a dočasné tabulky na nezbytné minimum,
v triggerech neopravujte data,
procedury by měli realizovat určitou činnost, nikoliv jen zapouzdřit selecty.

Na následujících dvou příkladech si všimněte špatného (ISAM) přístupu a dobrého přístupu k návrhu uložených procedur. Obě procedury volají pro určitou podmnožinu zaměstnanců proceduru print_info.

-- spatne (ISAM pristup)
CREATE OR REPLACE FUNCTION report_a()
RETURNS void AS 
$$
  main: FOR outer AS 
            SELECT id FROM Users 
        DO
          BEGIN
            DECLARE name, surname varchar;
            DECLARE age int;
            SET (name, surname, age) = (SELECT e.name, e.surname, e.age
                                           FROM Employers e
                                          WHERE e.id = outer.id);
            IF age >= 20 AND age <= 29 THEN
              CALL print_info(name, surname, age, 'y');
            ELSE IF age >=30 AND age <= 50 THEN
              CALL print_info(name, surname, age, 'o');
            END IF;                     
          END;
        END FOR main;
$$ LANGUAGE plpgpsm;

-- dobre
CREATE OR REPLACE FUNCTION report_b()
RETURNS void AS
$$
  main: FOR fc AS
            -- veskere mozne podminky a transformace resim v prikazu SELECT
            SELECT e.*, CASE WHEN e.age BETWEEN 20 AND 29 THEN 'y'
                             WHEN e.age BETWEEN 30 AND 50 THEN 'o' END AS tp
               FROM Employers e
              WHERE e.age BETWEEN 20 AND 50
        DO
          CALL print_info(fc.name, fc.surname, fc.age, fc.tp);
        END FOR main;
$$ LANGUAGE plpgpsm;

Příručka jazyka PL/pgPSM

Každá funkce obsahuje jeden PL/pgPSM příkaz. Následující jedno příkazové funkce jsou korektní. Pozn. Pro uživatele PL/SQL jazyků to může být mírný šok. Na rozíl od PL/SQL, kde základním příkazem funkce nebo procedury je složený příkaz, je v SQL/PSM základním příkazem libovolný příkaz. Pokud funkce neobsahuje žádný SQL příkaz, používejte atribut IMMUTABLE. Kromě jiného i zajistíte efektivnější provádění funkce. Speciálním PL/pgPSM příkazem je složený příkaz, který umožňuje zadat libovolně dlouhou syntakticky správnou posloupnost PL/pgPSM příkazů.

CREATE OR REPLACE FUNCTION sum2params(IN a integer, IN b integer, OUT c integer) AS 
$$
  SET c = a + b;
$$ LANGUAGE plpgpsm IMMUTABLE;

CREATE OR REPLACE FUNCTION insert_val(a integer)
RETURNS void AS
$$
  INSERT INTO Foo VALUES(a);
$$ LANGUAGE plpgpsm;

CREATE OR REPLACE FUNCTION get_sum(IN a integer, OUT b integer) AS
$$
  SET b = (SELECT sum(f.a) 
              FROM Foo f
             WHERE f.a > get_sum.a);
$$ LANGUAGE plpgpsm;

CREATE OR REPLACE FUNCTION dummy() 
RETURNS void AS
$$
  BEGIN
  END;
$$ LANGUAGE plpgpsm IMMUTABLE;

K dispozici jsou následující příkazy:

SQL příkaz,
složený příkaz - mezi dvojicí BEGIN a END je libovolný počet PL/pgPSM příkazů oddělených středníkem,
Volání a ukončení procedury - příkazy CALL a RETURN,
přiřazovací příkaz - SET proměnná = hodnota,
Podmíněné provádění příkazů:
- IF ELSEIF ELSE END IF,
- CASE WHEN THEN END CASE.
Příkazy cyklu
- LOOP co END LOOP,
- WHILE podmínka DO co END WHILE,
- REPEAT co UNTIL podmínka END REPEAT,
- FOR select DO co END FOR (iterace napříč výsledkem dotazu).
signalizace chyb - SIGNAL a RESIGNAL
zobrazení výsledku na konzoli - PRINT (nestandardní konstrukce),
opuštění a nová iterace cyklu - příkazy LEAVE a ITERATE,
dynamické SQL - EXECUTE a EXECUTE IMMEDIATE,
Příkaz GET DIAGNOSTICS - získání diagnostických údajů.

Ještě před samotným popisem PL/pgPSM příkazů si projděte následující jednoduché ukázky:

-- case statement
CREATE OR REPLACE FUNCTION foo1(a integer)
RETURNS void AS $$
  CASE a
    WHEN 1, 3, 5, 7, 9 THEN
      PRINT a, 'is odd number';
    WHEN 2, 4, 6, 8, 10 THEN
      PRINT a. 'is odd number';
    ELSE 
      PRINT a, 'isn't from range 1..10';
  END CASE;
$$ LANGUAGE plpgpsm;

-- while statement
CREATE OR REPLACE FUNCTION foo2(a integer)
RETURNS void AS 
$$
  BEGIN
    DECLARE i integer DEFAULT 1;
    WHILE i <= a 
    DO
      PRINT i;
      SET i = i + 1;
    END WHILE;
  END
$$ LANGUAGE plpgpsm;

-- for statement
CREATE OR REPLACE FUNCTION foo3(a integer)
RETURNS void AS 
$$
  FOR fc AS
      SELECT i 
         FROM generate_series(1,a) AS g(i)
  DO
    PRINT fc.i;
  END FOR;
$$ LANGUAGE plpgpsm;

Použití jazyka PL/pgPSM

Parametry ovlivňující provádění funkcí

V PL/pgPSM jsou k dispozici dva přepínače: DUMP a RECOMPILE. Použití prvního způsobí výpis přeloženého kódu funkce do systémového logu. Druhý zajistí vynulování nakešovaných prováděcích plánů při každém startu funkce. Parametry se zapisují ještě před vlastní kód za klíčové slovo #OPTION.

#OPTION DUMP

Účelem tohoto přepínače je zobrazení přeloženého kódu do systémováho logu. Rozhodně se nejedná o typickou činnost - většina uživatelů PL/pgSQL o této možnosti v životě neslyšela. Dokáže být velice užitečný při hledání kolize názvů proměnných a SQL atributů. V případě této chyby nám systém hlásí (v lepším případě), že dotaz nelze přeložit nebo (v horším případě) je výsledek dotazu jiný než očekáváme.

Tento přepínač demonstruje skutečnost, že PL/pgSQL je v podstatě preprocesor jazyka SQL. Vlastní interpret je natolik minimalistický, že pro provádění veškerých operací (logických, aritmetických) se spoléhá na SQL. Díky tomu je zajištěna kompatibilita s SQL. Na druhou stranu PL/pgSQL se nehodí pro výpočetně náročné úlohy (s provedením každého SQL příkazu je spojená nezanedbatelná režie). K těmto účelům v PostgreSQL slouží PL/Perl, PL/Python nebo klasické C.

-- tabulka foo obsahuje sloupec a
CREATE OR REPLACE FUNCTION kolize(a integer)
RETURNS SETOF Foo AS
$$
#option dump
  SELECT * 
    FROM Foo 
   WHERE a = a;
$$ LANGUAGE plpgpsm;

Jedná se o skutečně záludnout chybu (a poměrně častou). Přeložený kód výpadá následovně:

Execution tree of successfully compiled PL/pgSQL function kolize(integer):                            
                                                                                                      
Function's data area:                                                                                 
    entry 0: VAR $1               type int4 (typoid 23) atttypmod -1                                  
    entry 1: VAR found            type bool (typoid 16) atttypmod -1                                  
                                                                                                      
Function's statements:                                                                                
  0: *unnamed*:                                                                                       
     BLOCK                                                                                            
  2:   SQL 'SELECT * FROM Foo WHERE  $1  =  $1  {$1=0}'                                               
  0:   RETURN NULL                                                                                    
     END *unnamed*                                                                                    
                                                                                                      
End of execution tree of function kolize(integer)

Chyba je v podmínce WHERE. PL/pgSQL nechápe zápis a = a jako podmínku typu: SQL atribut je roven proměnné. Zápis znamená, že proměnná a je rovna proměnné a, což je splněno pro všechny řádky tabulky, a také výsledkem je celá tabulka Foo.

#OPTION RECOMPILE

Tento přepínač snižuje pravděpodobnost nekonzistence prováděcích plánů tím, že před každým startem procesury se procedura znovu přeloží a tím dojde k vyčištění cache prováděcích plánů. Zároveň se tím ale prodlouží doba spuštění procedury (dochází k překladu) a doba běhu (dochází k průbežnému vytváření prováděcích plánů). Tudíž tento přepínač používejte jen v nutných případech, nebo ještě lépe, nepoužívejte jej vůbec. Jak se vyhnout použití přepínače RECOMPILE je popsáno v sekci věnované dočasným tabulkám.Účelem tohoto přepínače je usnadnit portování uložených procedur z jiných RDBMS, kde jinak funguje ukládání prováděcích plánů.

CREATE OR REPLACE FUNCTION testr()
RETURNS SETOF Foo AS
$$
#option dump recompile
  BEGIN
    DROP TABLE IF EXISTS FooG;
    INSERT INTO FooG
       SELECT * 
          FROM Foo;
    RETURN TABLE(SELECT *
                    FROM FooG);
  END;
$$ LANGUAGE plpgpsm;

Tento přepínač je analogií přepínači WITH RECOMPILE v T-SQL Microsoft SQL Serveru.

Portování uložených procedur z MySQL 5.x

Originální zdrojový kód procedury generující fraktály je přebrán z archivu MySQL dev. PostgreSQL řeší jinak:

změnu aktuálního schématu,
zápis zdrojového kódu procedury (nepoužívá mechanismus separátoru),
pro spojení řetězců používá operátor || nikoliv funkci concat,
neumožňuje volné SQL dotazy, jelikož nepodporuje procedury, ale pouze funkce.

Všimněte si minimálních rozdílů v upraveném kódu pro PostgreSQL.

Instalace PL/pgPSM

Vzhledem k nezralosti implementace PL/pgPSM ještě tento interpret není zařazen do distribuce. Zatím je ke stažení v jeslích postgresql projektů http://pgfoundry. Přesun do distribuce bude možný až po plné implementaci standardu a po důkladném otestování. Zatím zbývá dopsat podporu příkazů RESIGNAL a GET STACKET DIAGNOSTIC. Pokud se obejdete bez těchto příkazů, můžete používat PL/pgPSM bez obav. Interpret PL/pgPSM je modifikací interpretu PL/pgSQL, který je lety i tisíci projekty důkladně prověřen.

Pro překlad PL/pgPSM je potřeba instalovat PostgreSQL ze zdrojových kódů. Potřebujete alespoň verzi 8.2. Poslední verzi PL/pgPSM naleznete na http://pgfoundry.org/frs/?group_id=1000238&release_id=767.

Postup

Stáhněte si nejnovější zdrojové soubory PL/pgPSM z adresáře http://pgfoundry.org/frs/?group_id=1000238
pokud máte PostgreSQL instalovaný ze zdrojových kódů, rozbalte archív do adresáře contrib
proveďte příkazy make a make install
jako superuser spusťe psql postgres < plpgpsm.sql
od tohoto okamžiku můžete používat plpgpsm stejně jako ostatní jazyky
test instalace make installcheck

Pokud nemáte PostgreSQL instalovaný ze zdrojových kódů, musíte mít alespoň develop knihovny PostgreSQL

kdekoliv rozbalte archiv plpgpsm
spusťte příkazy make USE_PGXS=1 a make USE_PGXS=1 install
další postup je stejný jako v předchozím případě

Uvítáme jakoukoliv formu spolupráce. A to ať doplněním této dokumentace, její korekturou, překladem, rozšířením testovacích scénářů, doplněním funkcionality nebo samotným použitím.