PL/pgSQL
RDBMS PostgreSQL umožňuje (stejně jako většina obdobných RDBMS) navrhovat a používat tzv. uložené procedury (Stored procedure). Uložená procedura je kód, který je uložen a spouštěn SQL serverem. Pro PostgreSQL můžeme uložené procedury psát v některém z následujících programovacích jazyků: SQL, Perl, Python, TCL, PL/pgSQL. 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. Ideově vychází z PL/SQL fy. Oracle. Velkou výhodou je automatické kešování prováděcích plánů. Nevýhodou, že jej nemůžeme použít k návrhu vlastních datových typů.
Tento dokument předpokládá, že používáte PostgreSQL 8.1 a vyšší.
Kdy nepoužívat PL/pgSQL
Ne vždy lze použít uložené procedury. Podmínkou pro přenesení kódu do uložených procedur je absolutní neinteraktivita a bezestavovost. Jelikož většina aplikací jistý stupeň interakce vyžaduje, je nepravděpodobné a jak zkušenosti uživatelů napovídají i neefektivní psát aplikace čistě v uložených procedurách. SQL server nemůže nahradit aplikační server, pouze částečně, a je na úvaze uživatele aby zvážil všechna pro a proti.
Stejně tak jako mají uložené procedury svá omezení ma svá omezení jazyk PL/pgSQL. V několika určitých případech nelze PL/pgSQL použít buďto z nedostatečnosti jazyka (PL je příliš statický) nebo z důvodu neefektivity.
PL/pgSQL není vhodné používat k inicializaci velkých polí - důvodem je pomalý zápis do pole. Pokud není zbytí je mnohem rychlejší prvky pole vytvořit pomocí konverze tabulky, kterou vytvoříme SRF funkcí, na pole. Například nejrychlejší způsob vytvoření pole vzestupných hodnot je SELECT ARRAY(SELECT * FROM generate_series(1,100));. Funkce generate_series je SRF funkce generujcí posloupnost hodnot ze zadaného intervalu.
Rychlostí nevyniká ani iterační sestavení řetězce. Pokud nejste spokojeni s rychlostí funkce, zkuste ještě následující trik. Svou funkci převeďte na SRF funkci - část, kde slučujete řetězce nahraďte příkazem RETURN NEXT část_řetězce. Konečné sloučení provedete mimo tělo funkce pomocí funkce array_to_string.
CREATE OR REPLACE FUNCTION generate_string(integer) RETURNS SETOF varchar AS $$
BEGIN
FOR _i IN 1 .. $1 LOOP
RETURN NEXT '<item>'||_i||'</item>';
END LOOP;
RETURN;
END; $$ LANGUAGE plpgsql;
SELECT array_to_string(
ARRAY(SELECT * FROM generate_string(1000)), '');
PL/pgSQL nepodporuje žádné I/O operace, a ani se nepředpokládá, že by je v blizké budoucnosti byly podporovány - PL/pgSQL existuje pouze v trusted variantě. Řešením je použít některý z untrusted jazyků jako je plperlu nebo plpython.
PL/pgSQL není vhodný k návrhnu univerzálních triggerů. V PL/pgSQL neexistuje možnost jak převést hodnotu typu RECORD na pole (Položka typu RECORD je přístupná pouze prostřednictvím statického identifikáturu známého v době kompilace, pole je přístupné prostřednictvím dynamického indexu). Tudíž neexistuje žádná možnost jak dynamicky iterovat skrz všechny položky typu RECORD, nebo dynamicky přistupovat k typu RECORD. Řešením je opět využít jiný programovací jazyk, tentokrát však postačí trusted varianty.
Jak nepoužívat PL/pgSQL
Asi nejdůležitější je uvědomit si, že plpgsql je interpret bez vlastní "aritmetické" jednotky. Veškeré výrazy převádí na triviální SQL příkazy - jejich vyhodnocení je řádově rychlejší než u plných SQL příkazů, nicméně stále řádově pomalejší než u vestavěné funkcionality. Je zásadní používat vestavěnou funkcionalitu kdekoliv, kde je to možné a kde je to výhodné. Jako ukázku špatného kódu uvádím kód, který jsem nalezl na internetu - jde o kontrolu telefonního čísla:
create or replace function kontrola_telefonniho_cisla (text)
returns boolean as $$
declare
text alias for $1;
znak text;
i int4;
begin
i=1;
while true loop
if length (text) != 16 then exit; end if;
znak := substring(text from i for 1);
if znak != '+' then exit; end if;
i:=i+1; znak := substring(text from i for 1);
if znak < '0' or znak>'9' then exit; end if;
i:=i+1; znak := substring(text from i for 1);
if znak < '0' or znak>'9' then exit; end if;
i:=i+1; znak := substring(text from i for 1);
if znak < '0' or znak>'9' then exit; end if;
i:=i+1; znak := substring(text from i for 1);
if znak != ' ' then exit; end if;
i:=i+1; znak := substring(text from i for 1);
if znak < '0' or znak>'9' then exit; end if;
i:=i+1; znak := substring(text from i for 1);
if znak < '0' or znak>'9' then exit; end if;
i:=i+1; znak := substring(text from i for 1);
if znak < '0' or znak>'9' then exit; end if;
i:=i+1; znak := substring(text from i for 1);
if znak != ' ' then exit; end if;
i:=i+1; znak := substring(text from i for 1);
if znak < '0' or znak>'9' then exit; end if;
i:=i+1; znak := substring(text from i for 1);
if znak < '0' or znak>'9' then exit; end if;
i:=i+1; znak := substring(text from i for 1);
if znak < '0' or znak>'9' then exit; end if;
i:=i+1; znak := substring(text from i for 1);
if znak != ' ' then exit; end if;
i:=i+1; znak := substring(text from i for 1);
if znak < '0' or znak>''9'' then exit; end if;
i:=i+1; znak := substring(text from i for 1);
if znak < '0' or znak>'9' then exit; end if;
i:=i+1; znak := substring(text from i for 1);
if znak < '0' or znak>'9' then exit; end if;
return true;
end loop;
raise exception e'Chybny znak \'%\' s poradim %. Telefonni cislo zadavejte ve tvaru +XXX XXX XXX XXX',znak,i;
return false; -- << mrtvy kod
end;
$$ language plpgsql;
Tato ukázka obsahuje celou řadu chyb - použití názvu typu jako identifikátor, zbytečně dlouhý kód - minimálně vnořený cyklus by dokázal kód razantně zkrátit, vysledkem je řetězec obsahující nečíselné znaky, což nepraktické, a principiálně tato funkce měla být napsána úplně jinak. Tato varianta by možná odpovídala kódu v C případně Fortranu, ale je absolutně nevhodná pro moderní jazyky. Když už, tak použijme regulární výrazy:
create or replace function kontrola_telefonniho_cisla (text)
returns boolean as $$
begin
if not $1 ~ e'^\\+\\d{3}\\ \\d{3} \\d{3} \\d{3}$' then
raise exception 'Chybny vstup pro telefonni cislo "%". Ocekavan je vstup ve tvaru +999 999 999 999';
end if;
return true;
end;
$$ language sql;
Osobně bych ještě z řetězce kontrolu napsal o něco méně restriktivní, povolil devítimístná čísla, a jako výsledek vracel čistý řetězec devíti nebo dvanácti číslic:
CREATE OR REPLACE FUNCTION normalise_phone_number(text)
RETURNS text AS $$
DECLARE aux text := translate($1, ' ','');
BEGIN
IF aux ~ e'^\\+\\d{12}$' THEN
RETURN substring(aux FROM 2);
ELSEIF aux ~ e'^\\d{9}$' THEN
RETURN aux;
ELSE
RAISE EXCEPTION 'Unexpected format of phone number "%".', $1;
END IF;
END;
$$ LANGUAGE plpgsql STRICT IMMUTABLE;
Použití:
postgres=# select normalise_phone_number('+420 222 222 222');
normalise_phone_number
------------------------
420222222222
(1 row)
postgres=# select normalise_phone_number('+420 222 222222');
normalise_phone_number
------------------------
420222222222
(1 row)
postgres=# select normalise_phone_number('+420 22 222222');
ERROR: Unexpected format of phone number "+420 22 222222".
postgres=# select normalise_phone_number('724 191 000');
normalise_phone_number
------------------------
724191000
(1 row)
Začátečnickou chybou je používání uložených procedur v situacích, kdy si vystačíme s jinými prostředky - např. použití triggeru místo unikátního indexu:
-- špatne použití triggeru
CREATE OR REPLACE FUNCTION kontrola_unikatnosti()
RETURNS TRIGGER AS $$
BEGIN
IF EXISTS(SELECT * FROM tab WHERE id = NEW.id) THEN
RAISE ERROR 'id neni unikatni';
END IF;
RETURN NEW;
END;
$$ LANGUAGE plpgsql;
-- správně použití unikátního indexu
CREATE UNIQUE INDEX ON tab(id);
Kdy je SQL rychlejší než PLpgSQL?
Odpověď je jednoduchá - vždy, když dokážeme využít vestavěnou funkcionalitu. Ve svých školení používám jako jedno zadání implementaci bublesortu. Bublesort představuje jeden z jednodušších nicméně netriviálních algoritmů vhodných pro výuku programovacího jazyka. Sám o sobě se bublsort nehodí pro větší objem dat (nad 1000 hodnot). A to bez ohledu na to, zda je implementace v PLpgSQL nebo v C. Pg používá quicksort, který je efektivnější pro větší množiny. Setkal jsem se, a setkávám se s tím, že vývojáři používají bublesort nevhodně - např. pro několik desítek stovek tisíc řádků. Implementace v SQL bude mnohem výhodnější:
CREATE OR REPLACE FUNCTION sort(anyarray) RETURNS anyerray AS $$ SELECT array(SELECT unnest($1) ORDER BY 1); $$ LANGUAGE sql;
Představení jazyka PL/pgSQL
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í prog. jazyk 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 každou novou verzí zvětšují, proto se i vyvýjí příkaz CREATE FUNCTION, kterým definujeme novou funkci.
CREATE [OR REPLACE] FUNCTION název ([IN|OUT|INOUT] název typ [,...])
RETURNS návr_typ AS $$
{tělo funkce}
$$ LANGUAGE plpgsql
[IMMUTABLE | STABLE | VOLATILE]
[CALLED ON NULL INPUT | RETURNS NULL ON NULL INPUT | STRICT]
[EXTERNAL SECURITY INVOKER | EXTERNAL SECURITY DEFINER]
Doporučuji psát kód funkcí v libovolném textovém editoru produkujícím čistý text. Kód uložit do souboru a v psql příkazem \i název_souboru provést příkaz (příkazy) uložené v souboru 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 povolit 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(a int, b int) RETURNS int AS $$
BEGIN
/*
Moje první triviální PL/pgSQL funkce.
*/
RETURN a + b;
END;
$$ LANGUAGE plpgsql IMMUTABLE STRICT;
Pokud funkci přepíšete bezchybně, pak můžete funkci otestovat příkazem SELECT soucet(10,10). V sloupci soucet (jmenuje se stejně, jako funkce) se objeví hodnota 20. Komentáře se v PL/pgSQL zapisují stejně, jako v SQL - jednořádkové začínají zdvojenou pomlčkou, víceřádkové mají C notaci, tj. /* komentář */. Provádění každé funkce v PL/pgSQL musí být ukončeno příkazem RETURN
Funkce má dvě části - deklaraci proměnných (ta zatím nebyla použitá) 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. Starší verze PostgreSQL nepodporovaly pojmenované parametry funkce. Podporován byl pouze tzv. poziční zápis, s kterým se ještě můžete setkat (symbol $ a číslo pořadí parametru). Výchozím atribut pro parametry funkce je IN. Každý takový parametr je konstanta. Bloky se, na rozdíl od Pascalu, nepoužívají k vymezení rozsahu konstrukcí IF, WHILE, FOR, LOOP, ale pouze k vymezení existence 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 všech 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 více řádků, pak se použíjí hodnoty z prvého řádku. Proměnná typu RECORD nikdy není nastavena na NULL. Pokud je výsledkem prázdná množina, je proměnným přiřazena hodnota NULL.
Kromě testu na NULL, který nemusí být vždy jednoznačný, můžeme testovat hodnotu vestavěné proměnné FOUND, která obsahuje hodnotu 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ákazát přiřazení hodnoty NULL a také ji můžeme označit jako konstantní. Při porušení těchto pravidel,prostředí PL/pgSQL přeruší 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|(:=)konstanta|parametr funkce]
Kromě běžných typů můžeme použít typ RECORD, který může obsahovat řádek libovolné tabulky nebo tzv. odvozené typy. Ty vycházejí buďto 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. Vývojáři v PL/pgSQL by se měli vyvarovat kolizí názvů PL/pgSQL proměnných a databázových objektů - důsledkem těchto kolizí mohou být relativně obtížně detekovatelné chyby. Jednou z řešení je používání prefixů na proměnné PL/pgSQL. V tomto případě symbolu podtržítka "_". Na uživatele znalé PL/SQL tu číhá jedna zrada. Zatímco v Oracle má v případě kolize názvu (název sloupce je stejný jako název lokální proměnné) přednost název sloupce, v PostgreSQL je tomu přesně naopak. V Oracle můžete konflikt názvu vyřešit upřesněním definičního bloku proměnné. Ten sice v PostgreSQL můžete použít také, vzhledem k větší prioritě lokálních proměnných konflikt nevyřešíte.
CREATE OR REPLACE FUNCTION MonthName1 (_month INTEGER) RETURNS VARCHAR(10) AS $$
DECLARE vysledek RECORD;
BEGIN
IF _month <1 OR _month > 12 THEN
RAISE EXCEPTION E'Parametr je mimo přípustné meze\n';
END IF;
SELECT INTO vysledek
CASE _month
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 IMMUTABLE STRICT;
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 vždy vrací 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á. Prvním argumentem příkazu RAISE je úroveň vyjímky. 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.
RAISE NOTICE se běžně používá pro zobrazení ladících hlášení, zobrazení obsahu proměnných. V podstatě je to jediný ladící prostředek, který je k dispozici.
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).
Pokud chceme přistupovat z PL/pgSQL funkcí do dočasných tabulek, je použití EXECUTE nezbytné. Jednou vytvořené prováděcí plány si drží id dočasných tabulek, která se však již mohou být neplatná. Název tabulky je v tomto případě nedůležitý, tj. pokud tabulku zrušíme a vytvoříme pod stejným názvem, bude mít nová tabulka jiné id než zrušená tabulka.
Následující příklad vytvoří a naplní tabulku s hodnotami fce sin v zadaném rozsahu. Používá konstrukci WHILE a parametrizovaný SQL příkaz INSERT. Tabulka tabsin se vytvoří pouze v případě, že dosud neexistovala.
CREATE OR REPLACE FUNCTION tabsin(_p1 float, _p2 float, _p3 float) RETURNS void AS $$
DECLARE _i float = _p1; _do float = _p2; _krok float = _p3;
BEGIN
IF NOT EXISTS(SELECT relname FROM pg_class
WHERE relname = 'tabsin' AND relkind = 'r' AND pg_table_is_visible(oid)) THEN
RAISE NOTICE 'Vytvářím tabulku tabsin';
CREATE TEMP TABLE tabsin (x NUMERIC(5,4) PRIMARY KEY, fx NUMERIC(5,4));
ELSE
RAISE NOTICE 'Ruším všechny zaznamy v tabulce tabsin';
TRUNCATE TABLE tabsin;
END IF;
WHILE _i < _do LOOP
INSERT INTO tabsin VALUES(CAST(_i AS NUMERIC(5,4)), SIN(_i));
_i := _i + _krok;
END LOOP;
END;
$$ LANGUAGE plpgsql;
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á na zhruba dvě vteřiny na mém 1.6G notebooku. Požadovanou hodnotu funkce zjistíme příkazem SELECT
SELECT * FROM tabsin WHERE x=0.1234;
Cyklus LOOP ... END LOOP můžeme opustit příkazem EXIT s volitelným návěstím (specifikuje cyklus, který má být přerušen) a volitelnou podmínkou. Návěstí cyklu můžeme volitelně uvést v místě ukončení cyklu.
<<hlavni>>
LOOP
EXIT hlavni WHEN c > 10;
RAISE NOTICE ''%'', c;
c := c + 1;
END LOOP hlavni;
Návrh vlastních PL/pgSQL funkcí
To nejdůležitější na PL/pgSQL funkcích (resp. všech UDF funkcích) je fakt, že je můžeme použít v dotazech - zjednodušší se tím celý proces zpracování dat. Údaje můžeme zprocesovat ještě na serveru.
Jedna z funkcí, která v PostgreSQL chybí je určení velikonoc. Používá se pro určení státních svátků v plánovacích funkcích. Použil jsem Carterterův algoritmus pro určení velikonoční neděle.
CREATE OR REPLACE FUNCTION velnedele(_rok INTEGER) RETURNS DATE AS $$
DECLARE B INTEGER; D INTEGER; E INTEGER; Q INTEGER;
DD INTEGER; MM INTEGER;
BEGIN
IF _rok < 1900 OR _rok > 2099 THEN
RAISE EXCEPTION 'Out of range';
END IF;
B := 255 - 11 * ($1 % 19); D := ((B - 21) % 30) + 21;
IF D > 38 THEN D := D - 1; END IF;
E := ($1 + $1/4 + D + 1) % 7; Q := D + 7 - E;
IF Q < 32 THEN DD:=Q; MM := 3; ELSE DD := Q - 31; MM := 4; END IF;
RETURN to_date(to_char(DD, '00') ||
to_char(MM, '00') || to_char(_rok,'0000'), 'DD MM YYYY');
END;
$$ LANGUAGE plpgsql IMMUTABLE STRICT;
Dalším případem, kdy je použití UDF funkce prakticky nezbytností, je použití čistících funkcí pro párování záznamů v tabulce. Tyto funkce představují poslední záchranu, když máme provádět výběry nad historickými databázemi, kde si nikdo nelámal hlavu s doménovou integritou. V přikladu pracuji s sloupcem hodnot, kde ekvivalentní by mohly být záznamy: Vyhl, Vyhl., Vyhláška, Vyhl.č.j., vyhl. čj., občas chybí mezera, sem tam jsou navíc.
CREATE OR REPLACE FUNCTION clean(VARCHAR) RETURNS VARCHAR AS $$
DECLARE pom varchar DEFAULT $1;
BEGIN pom := to_ascii(lower(trim(both FROM pom)));
pom = replace(pom,'c.' ,' ');
pom = replace(pom,'j.' ,' ');
pom = replace(pom,'cj.',' ');
pom = replace(pom,'cj ',' ');
pom = replace(pom,'vyhl.','vyhlaska ');
pom = replace(pom,'vyhl ','vyhlaska ');
pom = replace(pom,'.','');
WHILE position(' ' IN pom) <> 0 LOOP
pom := replace(pom, ' ',' ');
END LOOP;
RETURN trim(both FROM pom);
END;
$$ LANGUAGE plpgsql IMMUTABLE STRICT;
Použití PL/pgSQL 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ů.
CREATE OR REPLACE FUNCTION drop_students_databases() RETURNS INTEGER AS $$
DECLARE
helpstr VARCHAR(300);
BEGIN
helpstr := E'Funkce pro zrušení všech databází uživatelů jejichž\n' ||
E'jméno (jméno uživatele) vyhovuje masce příkazu LIKE.\n\n' ||
E'např. SELECT drop_students_databases(' || quote_literal('group%') || E')\n\n' ||
E'pozn. musíte být přihlášen jako uživatel postgres.\n' ||
E'autor: Pavel Stěhule, stehule@kix.fsv.cvut.cz 19.1.2002\n';
RAISE NOTICE '%', helpstr;
RETURN 0;
END;
$$ LANGUAGE plpgsql;
CREATE OR REPLACE FUNCTION drop_students_databases(_mask varchar(100)) RETURNS INTEGER AS $$
DECLARE
db RECORD;
deleted INTEGER := 0;
helpstr VARCHAR(300);
BEGIN
IF length(_mask) = 0 OR _mask ISNULL OR current_user() <> 'postgres' THEN
helpstr := E'Funkce pro zrušení všech databází uživatelů jejichž\n' ||
E'jméno (jméno uživatele) vyhovuje masce příkazu LIKE.\n\n' ||
E'např. SELECT drop_students_databases(' || quote_literal('group%') || ')\n\n' ||
E'pozn. musíte být přihlášen jako uživatel postgres.\n' ||
E'autor: Pavel Stěhule, stehule@kix.fsv.cvut.cz 19.1.2002\n';
IF current_user <> 'postgres' THEN
RAISE EXCEPTION E'Funkci může volat pouze uživatel postgres\n\n%', helpstr;
ELSE
RAISE EXCEPTION E'Argument funkce musí být neprázdný řetězec\n\n%', helpstr;
END IF;
END IF;
FOR db IN
select datname, usename from pg_database, pg_user
where datdba=usesysid and usename like _mask
LOOP
deleted := deleted + 1;
RAISE NOTICE 'Drop database %,%', db.datname, db.usename;
EXECUTE 'drop database "' || db.datname || '"';
END LOOP;
RETURN deleted;
END;
$$ LANGUAGE plpgsql;
Všimněte si konstrukce FOR IN LOOP END LOOP, která iteruje napříč výsledkem SQL dotazu (v tomto případě seznamu databází jejiž 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.
Použití PL/pgSQL funkcí v CHECK podmínkách
Funkce v PL/pgSQL můžeme použít jako kontrolní v CHECK podmínkách. Tyto funkce musí mít pouze jeden argument odpovídajícího typu a pokud je hodnota argumentu NULL, pak musí vrátit NULL. Toto chování lze ošetřit programově, nebo použít atribut isstrict, který zajistí, že kdykoliv je alespoň jeden z argumentů funkce NULL, pak jako výsledek funkce se použije hodnota NULL aniž by se vykonala funkce. Následující příklad testuje validitu ISBN kódu (sice existuje doplněk isbn_issn, ten však ve většině případů nebude nainstalován). Všimněte si použití proměnné weight typu pole.
CREATE OR REPLACE FUNCTION check_ISBN(ISBN CHAR(12)) RETURNS boolean AS $$
DECLARE
pos INTEGER; asc INTEGER; suma INTEGER DEFAULT 0;
weight INTEGER[] DEFAULT '{10,9,8,7,6,5,4,3,2,1}'; -- pro ISSN {8,7,6,5,4,3,2,1}
digits INTEGER DEFAULT 1;
BEGIN
FOR pos IN 1..length(ISBN) LOOP
asc := ascii(substr(ISBN,pos,1));
IF asc IN (88, 120) THEN -- ISDN muze obsahovat kontrolni cislo X
suma := suma + 10;
digits := digits + 1;
ELSIF asc >= 48 AND asc <= 57 THEN
suma := suma + (asc - 48)*weight[digits];
digits := digits + 1;
END IF;
END LOOP;
IF digits <> 11 THEN -- pro ISSN <> 9
RETURN 'f';
ELSE
RETURN (suma % 11) = 0;
END IF;
END;
$$ LANGUAGE plpgsql STRICT IMMUTABLE;
Definovanou funkci můžeme použít CHECK podmínce, např (sloupec isbn):
CREATE TABLE ZCHU_RezervacniKnihy (
id INTEGER PRIMARY KEY DEFAULT nextval('doc_seq'),
idoo INTEGER NOT NULL, -- id objektu ochrany
taboo CHAR(1) NOT NULL CHECK(taboo IN ('m','v','s')),
isign varchar(50) NOT NULL UNIQUE CHECK (isign <> ''),
typ CHAR(2) NOT NULL REFERENCES cv_TypDocRK(kod),
autor_prijmeni VARCHAR(30) NOT NULL CHECK (autor_prijmeni <> ''),
autor_jmeno VARCHAR(30) NULL CHECK (autor_jmeno <> ''),
nazev VARCHAR(100) NOT NULL CHECK (nazev <> ''),
poznamka VARCHAR(300) NULL,
rok_vydani INTEGER NULL CHECK (rok_vydani > 1918 AND rok_vydani <EXTRACT(year FROM current_date)),
zalozeno DATE DEFAULT current_date NOT NULL,
vyrazeno DATE NULL CHECK (vyrazeno > zalozeno),
isbn CHAR(12) NULL CHECK (check_ISBN(isbn)),
url VARCHAR(100) NULL CHECK (url ~* '^(file|http|ftp)://.+')
);
Použití PL/pgSQL funkcí při návrhu vlastních operátorů
PostgreSQL nepodporuje operaci dělení intervalu intervalem. Není ovšem žádným problémem tento nedostatek překonat a definovat si vlastní operátor / pro tuto kombinaci operandů.
omega=# select '1hour'::interval / '10min'::interval;
ERROR: operator does not exist: interval / interval
CREATE OR REPLACE FUNCTION div_op(a interval, b interval)
RETURNS double precision AS $$
BEGIN
RETURN EXTRACT(EPOCH FROM a) / EXTRACT(EPOCH FROM b);
END;
$$ LANGUAGE plpgsql IMMUTABLE RETURNS NULL ON NULL INPUT;
CREATE OPERATOR / (procedure = div_op, leftarg = interval, rightarg = interval);
omega=# select '1hour'::interval / '10min'::interval;
?column?
----------
6
V CHECK výrazech můžeme používat binární operátory OR a AND. V standardní distribuci chybí operátory XOR a implikace. Nicméně není žádným problémem tyto chybějící operátory do systému doplnit.
CREATE OR REPLACE FUNCTION op_xor (a boolean, b boolean) RETURNS boolean AS $$
BEGIN
RETURN ((NOT a) AND b) OR (a AND (NOT b));
END;
$$ LANGUAGE plpgsql IMMUTABLE STRICT;
CREATE OR REPLACE FUNCTION op_imp_lr (a boolean, b boolean) RETURNS boolean AS $$
BEGIN
RETURN (NOT a OR b);
END;
$$ LANGUAGE plpgsql IMMUTABLE STRICT;
CREATE OR REPLACE FUNCTION op_imp_rl (a boolean, b boolean) RETURNS boolean AS $$
BEGIN
RETURN (NOT b OR a);
END;
$$ LANGUAGE plpgsql IMMUTABLE STRICT;
Obě funkce musíme zaregistrovat a přiřadit jim nějaký symbol. To provede příkaz CREATE OPERATOR. Jméno pro operátor vytvoříme kombinací následujících znaků +-*/<>=~!@#%^&|'?$.
DROP OPERATOR # (boolean, boolean); CREATE OPERATOR # ( procedure = op_xor, leftarg = boolean, rightarg = boolean, commutator = # ); DROP OPERATOR >>> (boolean, boolean); CREATE OPERATOR >>> ( procedure = op_imp_lr, leftarg = boolean, rightarg = boolean, commutator = <<< ); CREATE OPERATOR <<< ( procedure = op_imp_rl, leftarg = boolean, rightarg = boolean, commutator = >>> );
Oba operátory si můžeme vyzkoušet na tabulce:
DROP TABLE logtab; CREATE TABLE logtab (l boolean, p boolean); INSERT INTO logtab VALUES(FALSE,FALSE); INSERT INTO logtab VALUES(TRUE,FALSE); INSERT INTO logtab VALUES(FALSE,TRUE); INSERT INTO logtab VALUES(TRUE,TRUE); SELECT l, p, l # p AS XOR FROM logtab; SELECT l, p, l >>> p AS IMPL FROM logtab;
Použití obou těchto operátorů významně zjednoduší CHECK podmínky. V tabulce Hlaseni požaduji, aby bylo zadáno buďto id uživatele nebo jméno uživatele. Dále je požadováno, aby byl vyplněn sloupec popis chyby, pokud typ hlašení je chyba.
DROP TABLE Hlaseni;
DROP SEQUENCE Hlaseni_id_seq;
CREATE TABLE Hlaseni (
id SERIAL PRIMARY KEY,
zalozeno DATE DEFAULT current_date NOT NULL,
zalozil_neprihlasen VARCHAR(60) CHECK (zalozil_neprihlasen <> ''),
zalozil INTEGER REFERENCES Uzivatele(id) CHECK (
zalozil_neprihlasen IS NOT NULL # zalozil IS NOT NULL),
trida_chyby CHAR(2) NOT NULL REFERENCES TridaChHlaseni(kod),
chybove_hlaseni TEXT CHECK (
(trida_chyby IN ('ch','zc') >>> chybove_hlaseni IS NOT NULL) AND
(chybove_hlaseni <> '')),
podrobny_popis TEXT NULL CHECK (podrobny_popis <> '')
);
INSERT INTO Hlaseni (zalozil, trida_chyby, podrobny_popis)
VALUES (1,'po', 'Nainstaloval jsem novou verzi.'); -- ok
INSERT INTO Hlaseni (zalozil, zalozil_neprihlasen, trida_chyby, podrobny_popis)
VALUES (1, 'Pavel Stěhule','po', 'Nainstaloval jsem novou verzi.'); -- selže
INSERT INTO Hlaseni (zalozil_neprihlasen, trida_chyby, podrobny_popis)
VALUES ('Pavel Stěhule','po', 'Nainstaloval jsem novou verzi.'); -- ok
INSERT INTO Hlaseni (zalozil_neprihlasen, trida_chyby, podrobny_popis)
VALUES ('Pavel Stěhule','ch', 'Nainstaloval jsem novou verzi.'); -- selže
Neexistenci operátoru XOR lze obejít poměrně jednoduše (bez nutnosti psaní PL/pgSQL funkce).
zalozil INTEGER REFERENCES Uzivatele(id) CHECK (
(zalozil_neprihlasen IS NOT NULL) <> (zalozil IS NOT NULL))
Použití PL/pgSQL funkcí při návrhu vlastních agregačních funkcí
Porovnáme počet vestavěných agregačních funkcí v PostgreSQL s jinými srovnatelnými RDBMS, zjistíme, že v PostgreSQL mnohé agregační funkce chybí. Naštěstí si v PostgreSQL si můžeme agregační funkci navrhnout sami.
V PostgreSQL agregační funkci vytvoříme pomocí dvou funkcí. První, která se spouští pro každou hodnotu (prvním parametrem je mezivýsledek, druhým pak samotná hodnota) a finální, jejíž parametrem je stávající mezivýsledek. První příklad tuto finální funkci nepotřebuje - vytvoří seznam čárkou oddělených položek. U agregačních funkcích je třeba (aby se chovali, tak jak se očekává) aby ignorovaly hodnotu NULL.
CREATE OR REPLACE FUNCTION comma_aggreg (state text, p text) RETURNS text AS $$
BEGIN
IF p IS NULL THEN
RETURN state;
END IF
IF length(state) > 0 THEN
RETURN state || ', ' || p;
ELSE
RETURN p;
END IF;
END;
$$ LANGUAGE plpgsql IMMUTABLE;
DROP AGGREGATE comma(text);
CREATE AGGREGATE comma (
basetype = text,
sfunc = comma_aggreg,
stype = text,
initcond = ''
);
SELECT comma(jmeno) FROM jmena;
Drobnou úpravou agregační funkce sum získáme hladovou funkci - tj. pokud je daný text již agregován, tak se bude ignorovat. Při použití této funkce zpracování dotazu nebude nejrychlejší, je to ale nejkratší cesta, jak docílit chtěného výsledku.
CREATE OR REPLACE FUNCTION comma_aggreg(state text,p text) RETURNS text AS $$
BEGIN
IF p IS NULL THEN
RETURN state;
END IF;
IF length(state) > 0 THEN
IF position(p in state) = 0 THEN
RETURN state || ', ' || p;
ELSE
RETURN state;
END IF;
ELSE
RETURN p;
END IF;
END;
$$ LANGUAGE plpgsql IMMUTABLE;
Tabulka obsahuje působnosti poboček v okresech a krajích. Pokud bych nepoužil hladové sčítání řetězců, tak by se mi kraj ve výpisu objevil dvakrát.
aopk=> select * from pusobnost;
pobocka | okres | kraj
---------------+---------------+------
Benešov | Benešov | SČ
Benešov | Kutná Hora | SČ
Č. Budějovice | Č. Budějovice | JČ
(3 řádek)
aopk=> select pobocka, sum(okres), sum(kraj) from pusobnost group by pobocka;
pobocka | sum | sum
---------------+---------------------+-----
Č. Budějovice | Č. Budějovice | JČ
Benešov | Benešov, Kutná Hora | SČ
(2 řádek)
Počínaje verzí 8.4 je efektivnější použití vestavěné funkce array_agg (vždy vyzkoušejte rychlost plpgsql fce a sql funkce - skládání řetězců a polí plpgsql nesvědčí):
postgres=# SELECT * FROM g;
v
---------
Praha
Benesov
Benesov
(3 rows)
postgres=# SELECT array_to_string(array_agg(distinct v),',') FROM g;
array_to_string
-----------------
Benesov,Praha
(1 row)
Následující, mírně komplikovanější, agregační funkce vrací seznam položek v HTML formátu.
CREATE OR REPLACE FUNCTION html_li_aggr (state text,p text) RETURNS text AS $$
BEGIN
IF p IS NULL THEN
RETURN state;
ELSE
RETURN state || '<li>' || p || E'</li>\n';
END IF;
END;
$$ LANGUAGE plpgsql IMMUTABLE;
CREATE OR REPLACE FUNCTION html_ul_final (state text) RETURNS text AS $$
BEGIN
IF $1 <> '' THEN
RETURN E'<ul>\n' || state || '</ul>';
ELSE
RETURN '';
END IF;
END;
$$ LANGUAGE plpgsql IMMUTABLE;
DROP AGGREGATE html_ul (text);
CREATE AGGREGATE html_ul (
basetype = text,
sfunc = html_li_aggr,
stype = text,
initcond = '',
finalfunc = html_ul_final
);
Výsledkem dotazu SELECT html_ul(jmeno) FROM jmena je jedna hodnota (i když více řádků textu), kterou můžeme přímo vložit do HTML stránky.
Návrh trigerů v PL/pgSQL
Trigger, česky spoušť je uložená procedura, kterou RDBMS aktivuje před nebo po provedení příkazů INSERT, UPDATE a DELETE na nějaké tabulce, které předtím určíme trigger. Jako trigger můžeme použít libovolnou PL/pgSQL proceduru bez parametrů vracející hodnotu typu TRIGGER, což je vlastně RECORD kompatibilní s tabulkou pro kterou je spouštěn. Existují dva typy triggerů: BEFORE a AFTER. BEFORE triggery se volají před provedením operace, AFTER triggery po provedení operace.
Představme si situaci, kdy z nějakého důvodu chceme mít data rozdělená do dvou tabulek. Pracovat však chceme se sjednocením těchto tabulek. Pokud použijeme pohled, vzdáme se možnosti používat referenční integritu. Další řešení je používat pomocnou tabulku, která bude obsahovat hodnoty obou zdrojových tabulek.
CREATE TABLE zdroj1 ( kod CHAR(2) PRIMARY KEY, popis VARCHAR(100) CHECK (popis <> '') ); CREATE TABLE zdroj2 ( kod CHAR(2) PRIMARY KEY, popis VARCHAR(100) CHECK (popis <> '') ); CREATE TABLE cil ( kod CHAR(2) PRIMARY KEY, popis VARCHAR(100) CHECK (popis <> ''), zdroj CHAR(1) NOT NULL CHECK (zdroj = '1' OR zdroj = '2') );
Předesílám, že jakákoliv funkce PL/pgSQL běží pod implicitní transakcí, tj. selže-li libovolný příkaz v proceduře, pak se veškeré změny v datech provedených funkcí anulují. Na tomto chování je postavena funkce triggeru. Zkusím napřed provést požadovanou funkci na cílové tabulce, pokud příkaz selže, pak se nepodaří provést příkaz ani na zdrojové tabulce.
CREATE OR REPLACE FUNCTION trig_build_cil_F() RETURNS OPAQUE AS $$
BEGIN
IF TG_OP = 'DELETE' THEN
DELETE FROM cil WHERE kod = OLD.kod;
RETURN OLD;
ELSE
IF TG_OP = 'UPDATE' THEN
UPDATE cil SET kod = NEW.kod, popis = NEW.popis WHERE kod = OLD.kod;
RETURN NEW;
ELSE
INSERT INTO cil VALUES(NEW.kod, NEW.popis,
CASE TG_RELNAME WHEN 'zdroj1' THEN 1 WHEN 'zdroj2' THEN 2 END);
RETURN NEW;
END IF;
END IF;
END;
$$ LANGUAGE plpgsql SECURITY DEFINER;
Při provádění PL/pgSQL funkce jako triggeru máme k dispozici několik vestavěných proměnných. TG_OP popisuje příkaz vedoucí k spuštění triggeru, TG_RELNAME nese název tabulky převedený na málá písmena, na které se trigger spustil, NEW obsahuje řádek s novou verzí hodnot (pouze pro INSERT a UPDATE), OLD obsahuje řádek s původní verzí hodnot (pouze pro DELETE a UPDATE). Pokud procedura vrátí NULL, pak se neprovede změna dat. Trigger řeší pouze přenos dat ze zdrojových tabulek do cílové tabulky. Pokud budeme měnit přímo cílovou tabulku, pak cílová tabulka nebude odpovídat zdrojovým tabulkám. Můžeme ale nastavit práva tak, aby pouze vlastník tabulky měl právo zápisu do tabulky. Vlastník bude jediný, kdo bude moci modifikovat cílovou tabulku. Ostatní budou moci cílovou tabulku pouze číst.
Trigger se vždy spouští s právy uživatele, který provedl akci spouštějící trigger. Pokud bychom ale odebrali uživatelům práva modifikovat cílovou tabulku, pak by musel selhat INSERT. Naštěstí máme možnost změnit toto chování atributem SECURITY DEFINER. Potom se v těle funkce SQL příkazy provádějí s právy vlastníka funkce, což je v našem případě nutné.
Funkci musíme přiřadit triggeru a tabulce příkazem CREATE TRIGGER. V zásadě můžeme vytvořit dva základní typy: ty které se provádějí před provedením příkazu, který vyvolal trigger, a ty, které se spouštějí po provedení příkazu.
CREATE TRIGGER t_zdroj1 BEFORE INSERT OR UPDATE OR DELETE ON zdroj1 FOR EACH ROW EXECUTE PROCEDURE trig_build_cil_F(); CREATE TRIGGER t_zdroj2 BEFORE INSERT OR UPDATE OR DELETE ON zdroj2 FOR EACH ROW EXECUTE PROCEDURE trig_build_cil_F();
V některých případech chceme zamezit UPDATE některých sloupců tabulek (většinou se jedná o sloupce typu SERIAL, kde UPDATE nemá smysl). Opět existuje doplněk noupdate, který řeší tento problém, ale který není defaultně nainstalován. Pokud pokusů o UPDATE nebude mnoho, můžeme bez rozpaků použít trigger a krátkou PL/pgSQL proceduru:
DROP SEQUENCE ciselnik_id_seq;
DROP TABLE ciselnik;
CREATE TABLE ciselnik (
id SERIAL PRIMARY KEY,
popis VARCHAR(100) CHECK (popis <> '')
);
CREATE OR REPLACE FUNCTION trig_static_id() RETURNS TRIGGER AS $$
BEGIN
IF TG_OP = 'UPDATE' AND NEW.id <> OLD.id THEN
RAISE EXCEPTION 'You can not update PRIMARY KEY column on table %', TG_RELNAME;
ELSE
RETURN NEW;
END IF;
END;
$$ LANGUAGE plpgsql;
CREATE TRIGGER static_id_ciselnik
BEFORE UPDATE ON ciselnik
FOR EACH ROW EXECUTE PROCEDURE trig_static_id();
Triggery typu BEFORE se spouští ještě před vyhodnocením CHECK podmínek sloupců a tabulek. Doménové typy jsou vyhodnocovány ještě před aktivací BEFORE triggerů. Díky tomu máme možnost v funkcích triggerů doplňovat některé chybějící hodnoty a nemáme možnost vyhnout se omezením. Na druhou stranu v triggeru můžeme detekovat hodnoty nevyhovující CHECK podmínkám. Řešením této situace není vyvolat výjimku, ale normálně ukončit provádění triggeru. RDBMS pak při následující kontrole zjistí nevyhovující hodnotu a výjimku vyvolá sám. Pokud je nad jednou tabulkou definováno více triggerů, pak se spouští v abecedním pořadí podle názvu (odchylka od ANSI SQL, zde se spouští v pořadí v kterém byly definovány).
Trigger v PostgreSQL může mít specifikovány i statické parametry - můžeme pak jednu proceduru použít v různých kontextech, atd. Parametry se ovšem nepředávají klasicky prostřednictvím parametrů funkce obsluhující trigger, ale jsou přístupné v externí proměnné TG_ARGV[].
Mějme například požadavek na logování zápisu do tabulkek, kdy hodnotu, která se bude zapisovat do logu, nelze jednoduše odvodit z názvu tabulky. Potřebovali bychom komplikované větvení.
CREATE TABLE foo_a(i integer);
CREATE TABLE foo_b(i integer);
CREATE OR REPLACE FUNCTION trig() RETURNS trigger AS $$
BEGIN
RAISE NOTICE 'Trigger: %,Insert do tabulky: %,Parametr: %',
TG_NAME, TG_RELNAME, TG_ARGV[0];
RETURN NULL;
END;
$$ LANGUAGE plpgsql;
CREATE TRIGGER foo_a_trg AFTER INSERT ON foo_a
FOR EACH ROW EXECUTE PROCEDURE trig('a');
CREATE TRIGGER foo_b_trg AFTER INSERT ON foo_b
FOR EACH ROW EXECUTE PROCEDURE trig('b');
INSERT INTO foo_a VALUES(1);
INSERT INTO foo_b VALUES(1);
<pre>
Vysledkem je hlášení
<pre>
psql:testtr.sql:16: NOTICE: Trigger: foo_a_trg,Insert do tabulky: foo_a,Parametr: a
INSERT 336812 1
psql:testtr.sql:17: NOTICE: Trigger: foo_b_trg,Insert do tabulky: foo_b,Parametr: b
INSERT 336813 1
Použití PL/pgSQL funkcí s parametry typu tabulka
V některých případech potřebujeme spouštět funkci na celý řádek tabulky, např. když potřebujeme nahradit opakující se výraz CASE v příkazu SELECT. Mějme následující tabulky prodejců a prodeje. Tabulka prodeje obsahuje sloupce c1, c2, c3, t2, ot, oec1, oec2, oec3, t1, b1, b2, c050, c150, c300, t1n, t2n, které obsahují počet prodaných kusů v daném tarifu v rámci jednoho prodeje. Tarify jsou stanovené direktivně a nemění se.
CREATE TABLE prodejci (
id SERIAL PRIMARY KEY,
rc VARCHAR(10) NOT NULL UNIQUE CHECK (rc ~ '^[0-9]{9,10}$'),
jmeno VARCHAR(20) NOT NULL CHECK (jmeno <> ''),
prijmeni VARCHAR(20) NOT NULL CHECK (prijmeni <> '')
);
CREATE TABLE prodej (
id SERIAL PRIMARY KEY,
prodejce int REFERENCES prodejci(id),
closing_date DATE NOT NULL DEFAULT current_date::DATE,
c1 integer NULL CHECK(c1 > 0),
c2 integer NULL CHECK(c2 > 0),
c3 integer NULL CHECK(c3 > 0),
t2 integer NULL CHECK(t2 > 0),
ot integer NULL CHECK(ot > 0),
oec1 integer NULL CHECK(oec1 > 0),
oec2 integer NULL CHECK(oec2 > 0),
oec3 integer NULL CHECK(oec3 > 0),
t1 integer NULL CHECK(t1 > 0),
b1 integer NULL CHECK(b1 > 0),
b2 integer NULL CHECK(b2 > 0),
c050 integer NULL CHECK(c050 > 0),
c150 integer NULL CHECK(c150 > 0),
c300 integer NULL CHECK(c300 > 0),
t1n integer NULL CHECK(t1n > 0),
t2n integer NULL CHECK(t2n > 0)
);
Zákazník bude požadovat rozpis prodaných kusů, nikoliv však podle jednotlivých tarifů, ale podle celkového počtu kusů rozděleného do tříd podle objemu na jeden provedený prodej. Třídy jsou:
0 - 4, 5 - 9, 10 - 19, 20 - 49, nad 50
Z rozpisu pak mohu určit jednak výši prodeje jedním prodejcem, ale i jeho zaměření na určitý segment trhu. Rozpis můžeme realizovat jako výběr s použitím příkazu CASE (níže), nebo výběr s PL/pgSQL funkcí, která má jako argument řádek tabulky.
CREATE OR REPLACE FUNCTION c_prodej_seg (rec prodej, l int,h int) RETURNS integer AS $$
DECLARE s INTEGER;
BEGIN
s := coalesce(rec.c1,0) + coalesce(rec.c2,0) + coalesce(rec.c3,0) + coalesce(rec.t2,0)
+ coalesce(rec.oec1,0) + coalesce(rec.oec2,0) + coalesce(rec.oec3,0) + coalesce(rec.t1,0)
+ coalesce(rec.b1,0) + coalesce(rec.b2,0) + coalesce(rec.c150,0) + coalesce(rec.c300,0)
+ coalesce(rec.t1n,0) + coalesce(rec.t2n,0) + coalesce(rec.ot,0);
IF l IS NOT NULL THEN
IF s < l THEN RETURN 0; END IF;
END IF;
IF h IS NOT NULL THEN
IF s > h THEN RETURN 0; END IF;
END IF;
RETURN s;
END;
$$ LANGUAGE plpgsql;
Použití těchto funkcí zásadně zjednoduší návrh. Funkce c_prodej_seg sečte prodej ve všech tarifech. Pokud je objem prodeje v intervalu určeným druhým a třetím argumentem, pak vrátí tuto hodnotu, jinak vrací nulu. Všimněte si, že (ač trochu nelogicky) první argument funkce je typu tabulka. Ve funkci samotné však s touto hodnotou pracujeme způsobem jako kdyby byla tabulka%ROW.
INSERT INTO prodejci (rc, jmeno, prijmeni)
VALUES ('7307150000','Pavel', 'Stěhule');
INSERT INTO prodejci (rc, jmeno, prijmeni)
VALUES ('7807150000','Zdeněk', 'Stěhule');
INSERT INTO prodej (prodejce,c1,c2) VALUES (1,18,11);
INSERT INTO prodej (prodejce,c1,c2) VALUES (1,18,6);
INSERT INTO prodej (prodejce,c3,c2) VALUES (1,50,24);
INSERT INTO prodej (prodejce,t1,c3) VALUES (1,1,1);
INSERT INTO prodej (prodejce,c300) VALUES (2,10);
INSERT INTO prodej (prodejce,c1,c2) VALUES (2,11,2);
INSERT INTO prodej (prodejce,c1,c2) VALUES (2,1,6);
INSERT INTO prodej (prodejce,c3,c2) VALUES (2,5,12);
INSERT INTO prodej (prodejce,t1,ot) VALUES (2,1,1);
INSERT INTO prodej (prodejce,c300) VALUES (2,252);
Vrací tento dotaz:
SELECT SUM(c_prodej_seg(prodej,NULL, 4)) AS lt5, SUM(c_prodej_seg(prodej,5, 9)) AS be5a9, SUM(c_prodej_seg(prodej,10, 19)) AS be10a19, SUM(c_prodej_seg(prodej,20, 49)) AS be20a49, SUM(c_prodej_seg(prodej,50, NULL)) AS ge50, jmeno || ' ' || prijmeni as prodejce from prodej join prodejci on prodejce = prodejci.id group by jmeno || ' ' || prijmeni ;
tabulku:
lt5 | be5a9 | be10a19 | be20a49 | ge50 | prodejce -----+-------+---------+---------+------+---------------- 2 | 0 | 0 | 53 | 74 | Pavel Stěhule 2 | 7 | 40 | 0 | 252 | Zdeněk Stěhule
Funkce vracející tabulky
Jednotlivé řádky vytvoříme opakovaným voláním RETURN NEXT hodnota (tato varianta RETURNu neukončí funkci) a voláním RETURNu bez parametrů, kterým ukončíme provádění funkce. Jako navratový typ můžeme použít skalární typy, typy tabulka nebo složené typy (ty jsou víceméně ekvivalentem typu tabulka (vytvoříme je příkazem CREATE TYPE)). Pokud nechceme definovat nový typ, můžeme použít OUT parametry (RETURNS SETOF RECORD).
CREATE TYPE tf AS (f1 varchar(10), f2 varchar(10));
CREATE OR REPLACE FUNCTION makesettf(mx integer) RETURNS SETOF tf AS $$
DECLARE f tf%ROWTYPE;
BEGIN
FOR i IN 1..mx LOOP
f.f1 := CAST(i AS varchar(10));
f.f2 := 'bbbbb '||CAST(i AS varchar(10));
RAISE NOTICE '%', f.f1;
RETURN NEXT f;
END LOOP;
RETURN;
END;
$$ LANGUAGE plpgsql;
SELECT a.*, b.* FROM
makesettf(10) a JOIN makesettf(5) b ON a.f1 = b.f1;
SELECT * FROM makesettf(3) UNION ALL SELECT * FROM makesettf(8);
Praktičtějším příkladem, inspirován UDF Firebirdu (vracela tabulku konání německých pivních festivalů), je použítí SRF při návrhu funkcí pro určení dne získané přičtením n pracovních dnů k zadanému dni.
SRF funkce se hodí pro generování menších dynamických (parametrizovatelných) tabulek, jelikož jimi vytvořené tabulky nelze indexovat. Toto omezení přeneseme lehce přez srdce při testování. Mnou vytvořené funkce AddWDays a AddWDays2 jsem kontroloval porovnáním hodnot (každá funkce je jinak implementována, musí dávat shodné výsledky) nad fiktivní tabulkou vytvořenou spojením tabulek vytvořených SRF funkcemi.
Funkce DaysInYear vrací tabulku dní v daném roce, fce. Seq vrací celočíselnou posloupnost omezenou parametrem. Funkce AddWDays a AddWDays2 má dva parametry - první den, ke kterému se mají přičítat pracovní dny, počet je určen druhým parametrem). Funkce DiffWDays vrací počet pracovních dnů v intervalu určeného parametry. Pokud jsou všechny funkce správně navržené, pak výsledkem testovacích dotazů musí být prázdná tabulka.
CREATE OR REPLACE FUNCTION DaysInYear(y integer) RETURNS SETOF date AS $$
DECLARE st date;
BEGIN st := to_date('0101'||y,'MMDDYYYY');
WHILE EXTRACT(year FROM st) < y + 1 LOOP
RETURN NEXT st;
st := st + 1;
END LOOP;
RETURN;
END;
$$ LANGUAGE plpgsql;
-- testy
SELECT DaysInYear, generate_series FROM DaysInYear(2003) CROSS JOIN generate_series(1,40)
WHERE AddWDays(DaysInYear, generate_series) <> AddWDays2(DaysInYear, generate_series);
SELECT DaysInYear, generate_series FROM DaysInYear(2003) CROSS JOIN generate_series(1,40)
WHERE DiffWDays(DaysInYear, AddWDays(DaysInYear, generate_series)) <> generate_series;
Státních svátků nemáme mnoho, nicméně pro každý rok jsou trochu jiné (díky velikonocím), a tak se pro generování tabulky hodí dobře SRF. Pokud by nastaly problémy s výkonem, mohu tabulku materializovat, tj. příkazem SELECT INTO uložit do klasické tabulky. Funkce StátníSvátky vrací tabulku se státními svátky, včetně názvu pro rozsah daný prvním a druhým parametrem. Funkce PočetStatníchSvátků spočítá počet, vyřadí dny předané v poli jako třetí parametr (prakticky vždy se bude jednat o sobotu a neděli - {6, 7}). Za povšimnutí stojí snad jen způsob zápisu porovnání hodnoty s obsahem pole = ANY(). Test <>ALL() může způsobit záludnou chybu - v případě, že je pole prázdné, pak je cokoliv <> ALL() vždy splněno.
CREATE OR REPLACE FUNCTION StatniSvatky(start date, finish date, OUT termin date, OUT nazev varchar(50))
RETURNS SETOF RECORD AS $$
DECLARE
svatek VARCHAR [][] DEFAULT ARRAY
[['Nový rok', '0101'],
['Neděle velikonoční', 'xx'],
['Pondělí velikonoční','x1'],
['Svátek práce','0501'],
['Den osvobození','0508'],
['Den slovanských věrozvěstů Cyrila a Metoděje','0705'],
['Den upálení mistra Jana Husa','0706'],
['Den české státnosti','0928'],
['Den vzniku samostatného československého státu', '1028'],
['Den boje za svobodu a demokracii','1117'],
['Štědrý den','1224'],
['1. svátek vánoční','1225'],
['2. svátek vánoční','1226']];
rok integer; stepd date; d varchar;
BEGIN stepd := start;
WHILE stepd <= finish LOOP
rok := CAST(EXTRACT(year FROM stepd) AS INTEGER);
FOR i IN array_lower(svatek,1)..array_upper(svatek,1) LOOP
d := svatek[i][2];
IF d = 'xx' THEN
termin := velnedele(rok);
ELSIF d = 'x1' THEN
termin := velnedele(rok) + 1;
ELSE
termin := to_date(d||rok,'MMDDYYYY');
END IF;
IF termin BETWEEN stepd AND finish THEN
nazev := svatek[i][1]; RETURN NEXT;
END IF;
END LOOP;
stepd := date_trunc('year', stepd) + interval '1 year';
END LOOP;
RETURN;
END;
$$ LANGUAGE plpgsql IMMUTABLE STRICT;
CREATE OR REPLACE FUNCTION PocetStatnichSvatku(start date, finish date, nwd INTEGER[]) RETURNS INTEGER AS $$
DECLARE
svatek VARCHAR [] DEFAULT ARRAY
['0101', 'xx', 'x1', '0501', '0508', '0705', '0706',
'0928', '1028', '1117', '1224', '1225', '1226'];
rok INTEGER; stepd date; d varchar; sv date; pss INTEGER DEFAULT 0; OM INTEGER;
BEGIN stepd := start;
WHILE stepd <= finish LOOP
rok := CAST(EXTRACT(year FROM stepd) AS INTEGER);
FOR i IN array_lower(svatek,1)..array_upper(svatek,1) LOOP
d := svatek[i];
IF d = 'xx' THEN
sv := velnedele(rok);
ELSIF d = 'x1' THEN
sv := velnedele(rok) + 1;
ELSE
sv := to_date(d||rok,'MMDDYYYY');
END IF;
IF NOT EXTRACT(dow FROM sv) = ANY (nwd) THEN
IF sv BETWEEN stepd AND finish THEN
pss := pss + 1;
END IF;
END IF;
END LOOP;
stepd := date_trunc('year', stepd) + interval '1 year';
END LOOP;
RETURN pss;
END;
$$ LANGUAGE plpgsql IMMUTABLE STRICT;
Funkce AddWDays2 je jednodušší, pro menší počet dnů pravděpodobně i rychlejší. Do proměnné svatky uložím pole svátků na rok od dne, ke kterému přičítám pracovní dny. Funkce StatniSvatky vrací tabulku a tak je nutné převést tabulku na pole konstrukcí ARRAY(SELECT FROM). Pak pro každý pracovní den (není sobotou, nedělí a svátkem) snižuji počet pracovních dnů a posouvám se dopředu.
CREATE OR REPLACE FUNCTION AddWDaysSlow2(start date, c integer) RETURNS date AS $$
DECLARE st date = start; ed date; svatky date[]; d integer = c; k integer;
BEGIN ed := st + 365;
SELECT INTO svatky ARRAY(SELECT termin FROM StatniSvatky(st, ed));
LOOP k := CAST(EXTRACT(DOW FROM st) AS INTEGER);
WHILE k = 6 OR k = 0 OR st = ANY(svatky) LOOP
st := st + 1; k := CAST(EXTRACT(DOW FROM st) AS INTEGER);
IF st > ed THEN ed = st + 365;
SELECT INTO svatky ARRAY(SELECT termin FROM StatniSvatky(st, ed));
END IF;
END LOOP;
EXIT WHEN d = 0 OR d = 1;
st := st + 1; d := d - 1;
END LOOP;
RETURN st;
END;
$$ LANGUAGE plpgsql;
Druhá (první) funkce AddWDays je komplikovanější, a pro delší intervaly rozhodně rychlejší. Využívá funkce AddWDay, která k počtu pracovních dní přičte x dní za víkendy. Pak se opakovaně zjišťuje korekce vůči státním svátkům. Pokud je parametrem AddWDay víkendový den, pak se nejdříve posune na pondělí. Pak probíhá jakási normalizace, posouvám se zpět na neděli, o to co se posunu zpět zvýším počet pracovních dní. Pak mohu jednoduše převést pracovní dny na skutečné (předposlední řádek funkce).
CREATE OR REPLACE FUNCTION AddWDays(start date, c integer) RETURNS date AS $$
DECLARE st date = start; ed DATE; korekce INTEGER; sv INTEGER; d INTEGER;
BEGIN ed := AddWDay(st,c);
korekce = PocetStatnichSvatku(st, ed, ARRAY[6,0]);
WHILE korekce > 0 LOOP
st := ed + 1; ed := AddWDay(st, korekce);
korekce = PocetStatnichSvatku(st, ed, ARRAY[6,0]);
END LOOP;
RETURN FirstWDay(ed);
END;
$$ LANGUAGE plpgsql IMMUTABLE STRICT;
CREATE OR REPLACE FUNCTION AddWDay(d date, c integer) RETURNS date AS $$
DECLARE st DATE; wd INTEGER; k INTEGER;
BEGIN k := CAST(EXTRACT(DOW FROM d) AS INTEGER);
IF k = 0 THEN st := d + 1; k := 1;
ELSIF k = 6 THEN st := d + 2; k := 1;
ELSE st := d; END IF;
st := st - k; wd := c + k - 2;
st := st + ((wd) / 5) * 7 + ((wd) % 5) + 1;
RETURN st;
END;
$$ LANGUAGE plpgsql IMMUTABLE STRICT;
Pokud je první parametr funkce DiffWDays sobota nebo neděle, posunu začátek na pondělí, pokud je druhým parametrem sobota nebo neděle, posunu konec na pátek. Pokud je začátek za koncem, musím zvýšit počet týdnů. V daném intervalu ještě odečtu počet státních svátků. Funkce ISODOW vrací ISO číslo dne, tj. sobota 6, neděle 7.
CREATE OR REPLACE FUNCTION ISODOW(date) RETURNS INTEGER AS $$
SELECT CAST(EXTRACT(DOW FROM $1 - 1) + 1 AS INTEGER);
$$ LANGUAGE sql STRICT;
CREATE OR REPLACE FUNCTION DiffWDays(d1 date, d2 date) RETURNS integer AS $$
DECLARE wc INTEGER; st DATE; ED DATE; p INTEGER;
BEGIN
p := ISODOW(d1); IF p > 5 THEN st := d1 + (8 - p); ELSE st := d1; END IF;
p := ISODOW(d2); IF p > 5 THEN ed := d2 - (p - 5); ELSE ed := d2; END IF;
wc := (d2 - d1) / 7;
IF EXTRACT(DOW FROM st - 1) + 1 > EXTRACT(DOW FROM ed - 1) + 1 THEN
wc := wc + 1;
END IF;
RETURN (d2 - d1 - (wc * 2) - PocetStatnichSvatku(st,ed, ARRAY[6,7]) + 1);
END;
$$ LANGUAGE plpgsql IMMUTABLE STRICT;
Rekurzivní volání SRF funkcí
Každé volání SRF funkce má svůj vlastní tzv. kontext, do něhož se zapisuje množina vracených hodnot. Tato vlastnost se projeví pouze při rekurzivním volání SRF funkcí, kdy při chybně navržené funkci, budeme mít pocit, že se nám ztrácí výsledky. Příklad takto chybné funkce je:
CREATE OR REPLACE FUNCTION foo (pid integer) RETURNS SETOF foo AS $$
DECLARE rec RECORD;
BEGIN
FOR rec IN SELECT * FROM foo WHERE foo.pid=pid LOOP
RETURN NEXT rec;
RAISE NOTICE 'uid=% pid=%',rec.uid,rec.pid;
SELECT INTO rec * FROM foo (rec.uid);
END LOOP;
RETURN;
END;
$$ LANGUAGE plpgsql;
Chybná je doměnka, že volání select into rec * from foo() způsobí přenesení hodnot vnořené funkce. Správný tvar funkce foo je tento:
CREATE OR REPLACE FUNCTION foor (pid integer) RETURNS SETOF foo AS $$
DECLARE rec RECORD; fo bool;
BEGIN
FOR rec IN SELECT * FROM foo WHERE foo.pid=pid LOOP
RETURN NEXT rec;
FOR rec IN SELECT * FROM foor (rec.uid) LOOP
RETURN NEXT rec;
END LOOP;
END LOOP;
RETURN;
END;
$$ LANGUAGE plpgsql;
Počínaje PostgreSQL 8.4 lze řadu rekurzivních volání SRF funkcí nahradit rekurzivním SELECTem, případně použít efektivnější příkaz RETURN QUERY:
WITH RECURSIVE x AS (SELECT *
FROM foo
WHERE uid = 3 -- find a root
UNION ALL
SELECT foo.*
FROM foo
JOIN
x
ON x.uid = foo.pid) -- append rows where pid is same as some searched uid
SELECT * FROM x; -- show content of stacked table x without changes
Rekurzivní příkaz by měl být téměř vždy rychlejší než rekurzivní volání funkce.
Návrh polymorfních funkcí, používání polymorfních typů
Polymorfní funkce jsou ty, které při definici návratového typu nebo argumentů používají některý z následujících polymorfních typů: anyarray, anyelement.
Pokud funkce vrací polymorfní typ, pak alespoň jeden z argumentů funkce musí být polymorfního typu. Dynamicky se určí návratový typ funkce podle aktuálního typu tohoto argumentu. Pokud funkce obsahuje více parametrů typu anyelement, pak skutečné parametry musí být stejného typu. Stejná podmínka platí pro anyarray.
CREATE OR REPLACE FUNCTION polyfoo1(a anyelement) RETURNS anyelement AS $$
BEGIN
RETURN a + 1;
END;
$$ LANGUAGE plpgsql;
testdb011=> select polyfoo1(date '1.1.2004'), polyfoo1(10);
polyfoo1 | polyfoo1
------------+----------
2004-01-02 | 11
(1 řádka)
CREATE OR REPLACE FUNCTION polyfoo2(a anyelement, b anyelement) RETURNS integer AS $$
BEGIN
RETURN a - b;
END;
$$ LANGUAGE plpgsql;
testdb011=> select polyfoo2(date '1.1.2004',date '1.2.2003');
polyfoo2
----------
334
(1 řádka)
Time: 4,158 ms
testdb011=> select polyfoo2(20,10);
polyfoo2
----------
10
(1 řádka)
Příkladem použití polymorfní funkce je tělo agregační funkce jejíž výsledkem je pole hodnot podmnožiny.
CREATE OR REPLACE FUNCTION aggregate_array(state ANYARRAY, a ANYELEMENT) RETURNS ANYARRAY AS $$
SELECT CASE
WHEN state IS NULL THEN ARRAY[a]
WHEN a IS NULL THEN state
ELSE array_append(state,a)
END;
$$ LANGUAGE sql;
testdb011=> SELECT count(*), aggarray(prijmeni) FROM lide GROUP BY prijmeni ~ '.*á';
count | aggarray
-------+---------------------------------------------
1 | {Gregorová}
6 | {Kolík,Čurda,Hojka,Chytlík,Stěhule,Novotný}
(2 řádek)
Polymorfní funkce se někdy také označují jako generické. Interně PostgreSQL nezná žádný datový typ ANYELEMENT. PostgreSQL vždy, když požadujete aktivaci nějaké funkce zjišťuje, zda-li neexistuje připravený kód pro požadovanou funkci (testuje shodu na počet a typ argumentů). Pokud ještě funkce nebyla spuštěna, hledá její zdrojový kód a přípravuje funkci pro její první spuštění. Pokud nalezne shodu s polymorfní funkcí, pak nahradí neznámé typy požadovanými a připraví funkci, kde už jsou všechny parametry určené - využívá se standardní mechanismus.
Pokud potřebujeme identifikovat variantu polymorfní funkce, můžeme použít operátor IS OF.
IF a IS OF (INTEGER) THEN
RAISE NOTICE 'Parametr a je cele cislo';
ELSIF a IS OF (varchar) THEN
RAISE NOTICE 'Parametr a je retezec';
END IF;
PL/pgSQL funkce s (IN)OUT parametry
PL/pgSQL je specifický tím, že podporuje pouze návrh funkcí, nikoliv procedur. Hlavní rozdíl mezi procedurou a funkcí je způsob předání výsledné hodnoty. Klasicky procedura vrací výsledek prostřednictvím proměnných předávaných referencí. Problém je v tom, že vyjma prostředí PL/pgSQL PostgreSQL nepodporuje proměnné. V starších verzích PostgreSQL jsme museli použít složený typ, když jsme potřebovali získat z kódu víc jak jednu hodnotu. Aktuální verze PostgreSQL podporují OUT proměnné, čímž nám šetří práci s deklarací speciáního out typu.
CREATE OR REPLACE FUNCTION foo(IN a int, IN b int, OUT aa int, OUT bb int) AS $$ BEGIN aa := a*a; bb := b*b; END $$ SELECT foo(10,10);
Přidáno v PostgreSQL 8.3
Předchozí verze neumožňovaly vrátit množinu záznamů jako výsledek SRF funkce. Jediným řešením bylo volání příkazu RETURN NEXT pro každý řádek výsledku dotazu. V podstatě totéž (ale na nižší úrovni, tudíž efektivněji) provádí příkaz RETURN QUERY. Jeho parametrem je SQL dotaz. Výsledek tohoto dotazu (množina) se připojí k výstupu. Podobně jako RETURN NEXT neukončuje provádění funkce.
CREATE OR REPLACE FUNCTION dirty_series(m integer, n integer)
RETURNS SETOF integer AS $$
BEGIN
RETURN QUERY SELECT * FROM generate_series(1,m) g(i)
WHERE i % n = 0;
RETURN;
END; $$ LANGUAGE plpgsq;
Další novou vlastností je lokální přenastavení (pouze pro konkrétní funkci) systémových proměnných. Podobně se chová T-SQL nebo MySQL, kde se implicitně ukládá aktuální nastavení systémových proměnných v čase registrace funkce. V PostgreSQL dosud podobný mechanismus nebyl. Lokálním přenastavením systémové proměnné search_path (resetem) lze konečně zabezpečit SECURITY DEFINER funkce. Zápis je zřejmý z následujícího příkladu:
CREATE FUNCTION report_guc(text) RETURNS TEXT AS $$ SELECT current_setting($1) $$ LANGUAGE sql SET regex_flavor = basic; ALTER FUNCTION report_guc(text) RESET search_path SET regex_flavor = extended;
Přidáno v PostgreSQL 8.4
Doposud bylo používání dynamického SQL limitováno možným rizikem SQL injektáže. Kromě toho také docela nečitelným výsledným kódem. Tyto problémy z části odstraňuje klauzule USING v příkazu PL/pgSQL EXECUTE. Díky ní totiž můžeme parametrizovat SQL příkaz, podobně jako v PREPARED STATEMENTS.
nezabezpečené dynamické SQL:
EXECUTE 'SELECT * FROM ' || tabname || ' WHERE value = \'' || value || '\'';
zabezpečené dynamické SQL:
EXECUTE 'SELECT * FROM ' || tabname::regclass || ' WHERE value = $1' USING value;
Díky klauzuli USING bude použití dynamického SQL o dost bezpečnější a o bude s ním méně práce. I tak ale platí, že DSQL by se mělo používat, pokud možno co nejméně a v opravdu nutných případech (např. z důvodu nutnosti generování prováděcího plánu se znalostí parametrů).
Příkaz CASE
Z prostředí SQL/PSM (standardizovaný jazyk pro uložené procedury dle ANSI - setkat se s ním můžete v DB2 nebo MySQL) byla převzata syntaxe příkazu CASE.
/* simple case */
CASE x
WHEN 1, 2 THEN
msg := 'one or two';
ELSE
msg := 'other value than one or two';
END CASE;
/* search case */
CASE
WHEN x BETWEEN 0 AND 10 THEN
msg := 'value is between zero and ten';
WHEN x BETWEEN 11 AND 20 THEN
msg := 'value is between eleven and twenty';
END CASE;
Vlastní výjimky
PostgreSQL umožňuje předat ve výjimce relativně dost informací. Bohužel PL/pgSQL příkaz RAISE byl příliš jednoduchý - výsledkem výjimky v PL/pgSQL byl pouze jeden řetězec (nebylo možné zadat kód, hint, detail). Toto omezení je minulostí. Syntaxe příkazu RAISE byla rozšířena tak, aby umožnila specifikovat všechny dostupné parametry výjimky:
RAISE division_by_zero; RAISE SQLSTATE '22012'; RAISE EXCEPTION 'Nonexistent ID --> %', user_id USING HINT = 'Please check your user id'; RAISE 'Duplicate user ID: %', user_id USING ERRCODE = 'unique_violation'; RAISE 'Duplicate user ID: %', user_id USING ERRCODE = '23505';
Vlastní vyjímky lze samozřejmě zachytit - v minulých verzích bylo možné zachytit pouze pojmenované výjimky:
BEGIN ... EXCEPTION WHEN SQLSTATE '22012' THEN ...
Variadické funkce
Funkce, které pracují s variabilním počtem parametrů označujeme jako variadické. Mezi variadické funkce např. patří funkce least, greatest, coalesce. Implementace těchto funkcí není nijak zvlášť komplikovaná, nicméně není triviální a trochu vypadává z kontextu C funkcí (vynucuje si úpravy parseru). Na uživatelské úrovni nezbývalo než přetěžovat funkce, což zase vedlo k zbytečnému duplikování kódu. Řešením je zavedení tzv. variadických funkcí, resp. modifikátoru VARIADIC v seznamu parametrů. Ten pozměňuje chování parseru. Všechny reálné parametry, které jsou pozičně na pozici a za pozicí variadického parametru jsou sloučeny do pole, které je pak předáno funkci.
CREATE FUNCTION myleast(VARIADIC a numeric[])
RETURNS NUMERIC AS $$
SELECT min($1[i])
FROM generate_subscripts($1,1) g(i)
$$ LANGUAGE SQL IMMUTABLE;
CREATE FUNCTION
Time: 5,274 ms
postgres=# SELECT myleast(1,2,3,4);
myleast
---------
1
(1 row)
Variadické funkci lze předhodit i pole (tj. obejít parser) pomocí modifikátoru hodnoty VARIADIC:
postgres=# SELECT myleast(VARIADIC ARRAY[1,3,4,-5,6,8]);
myleast
---------
-5
Tabulkové funkce
Funkce, které vrací tabulku, jsou podle ANSI SQL tzv. tabulkové funkce. Totéž (s mírně odlišnou syntaxí) se v PostgreSQL označuje jako SRF funkce (Set Returned Functions). Do 8.4 byla zahrnuta i podpora ANSI SQL syntaxe, která je podle mého názoru přehlednější a názornější:
--ANSI SQL
create or replace function tab(i int)
returns table(a int, b int) as $$
select i, i+1
from generate_series(1, $1) g(i);
$$ language sql immutable strict;
postgres=# SELECT * FROM tab(2);
a | b
---+---
1 | 2
2 | 3
(2 rows)
-- PostgreSQL syntaxe
create or replace function tab(in int, out a int, out b int)
returns setof record as $$
select i, i+1
from generate_series(1, $1) g(i);
$$ language sql immutable strict;
Defaultní hodnoty parametrů funkce
Možnost definovat defaultní hodnoty parametrů funkce patří zvyšuje programátorský komfort a snižuje duplicitu kódu. Implementace zatím odpovídá zhruba implementaci ve Firebirdu, tj. chybějící parametry se zprava doplňují defaultní hodnoty.
postgres=# create or replace function instr(varchar, varchar, int = 1)
returns int as $$
select position($2 in substring($1 from $3));
$$ language sql immutable strict;
CREATE FUNCTION
Time: 2,738 ms
postgres=# SELECT instr('abcabc','a');
instr
-------
1
(1 row)
Time: 1,907 ms
postgres=# SELECT instr('abcabc','a',2);
instr
-------
3
(1 row)
Time: 1,381 ms
RETURN QUERY EXECUTE
RETURN QUERY se docela ujalo, což mne příjemně překvapilo. Jednak jsem vůbec nečekal, že by podobné rozšíření mohlo projít do core, jednak vlastní implementace byla docela jednoduchá - v podstatě pár nových řádek v parseru - jinak se použil stávající kód. To je to, co mne na hackingu nejvíc baví. Přidá se pár řádků, pár řádků kódu se upraví a nová funkce je na světě.
V 8.4 RETURN QUERY podporuje také dynamické SQL - tj. zčásti odpadá stereotyp:
FOR r IN EXECUTE '....' LOOP RETURN NEXT r; END LOOP
který lze nahradit rychlejším a čitelnějším kódem:
RETURN QUERY EXECUTE '...';
Přidáno v 9.0
V devítce došlo k zásadnímu překopání parseru PL/pgSQL. Díky tomu se trochu mění chování prostředí. Zásadní změnou je možnost rozpoznat kolizi identifikátorů plpgsql a SQL. Jedinou významnou novinkou je tzv. jmenné konvence předávání hodnot parametrů - ostatní změny jazyka jsou více-méně kosmetické. Počínaje 9.0 jsou k dispozici i sloupcové a podmíněné triggery, které ovšem s plpgsql přímo nesouvisí (stejně jako jmenná konvence).
Režim řešení kolizí identifikátorů
Zásadním nedostatkem syntaktické kontroly plpgsql byla neschopnost odhalit kolizi SQL a PLpgSQL identifikátorů. Kód obsahující kolizní identifikátory málokdy fungoval správně - a to ještě spíš omylem - nicméně nehlásil chybu. Od verze 9.0 je kontrolována unikátnost identifikátorů v bloku:
CREATE OR REPLACE FUNCTION foo()
RETURNS void AS $$
DECLARE a integer;
BEGIN
FOR a IN SELECT a FROM omega
LOOP
RAISE NOTICE '%', a;
END LOOP;
END;
$$ LANGUAGE plpgsql;
CREATE FUNCTION
Time: 3,501 ms
postgres=# SELECT foo();
ERROR: column reference "a" is ambiguous
LINE 1: SELECT a FROM omega
^
DETAIL: It could refer to either a PL/pgSQL variable or a table column.
QUERY: SELECT a FROM omega
CONTEXT: PL/pgSQL function "foo" line 3 at FOR over SELECT rows
Jmenná konvence předávání parametrů
Tato změna se týká všech PL jazyků v PostgreSQL. Obvyklý způsob předávání parametrů je poziční. Parametry funkce se plní hodnotami na základě shodné pozice. Kromě této notace (způsobu předávání) existují notace jmenná a kombinovaná (použím terminologii jazyka ADA - positional, named and mixed notation). V Pg se pro pojmenovanné parametry používá zápis: název_parametru := hodnota (pozn. tento zápis je definitivní a bude trvale podporovaný, nicméně není ve shodě s připravovanou ANSI SQL 2011, proto v příští verzi přibude zápis název_parametru => hodnota. Tento zápis v 9.0 nemohl být zaveden z důvodu kolize s modulem hstore):
CREATE OR REPLACE FUNCTION foo(a varchar, b varchar)
RETURNS void AS $$
BEGIN
RAISE NOTICE 'A:% B:%', a, b;
RETURN;
END;
$$ LANGUAGE plpgsql;
SELECT foo('Ahoj','Svete');
SELECT foo('Ahoj', b := 'Svete'); -- kombinovaná notace
SELECT foo(b := 'Svete', a := 'Ahoj'); -- jmenná notace
Ostatní změny
- V této verzi došlo ke zrušení omezení zápisu do proměnných odpovídajících IN parametrům funkce.
- Jazyk PL/pgSQL je dostupný ihned po instalaci
Přidáno v 9.1
cyklus FOREACH
pomocí cyklu FOREACH můžeme jednoduše a efektivně iterovat přes prvky pole. S klauzulí SLICE lze jednoduše pracovat i s vícerozměrnými poly:
CREATE OR REPLACE FUNCTION public.ft()
RETURNS void
LANGUAGE plpgsql
AS $function$
DECLARE
_a int;
_b int;
_xx foo[];
BEGIN
_xx := (SELECT ARRAY(SELECT (a,b) FROM foo));
FOREACH _a, _b IN ARRAY _xx
LOOP
RAISE NOTICE 'a: %, b: %', _a, _b;
END LOOP;
END;
$function$;
postgres=# SELECT ft();
NOTICE: a: 10,b: 20
NOTICE: a: 30,b: 40
ft
────
(1 row)
CREATE OR REPLACE FUNCTION public.ft()
RETURNS void
LANGUAGE plpgsql
AS $function$
DECLARE
_a int[];
_xx int[] := ARRAY[[1,2,3], [4,5,6]];
BEGIN
FOREACH _a SLICE 1 IN ARRAY _xx
LOOP
RAISE NOTICE '%', _a;
END LOOP;
END;
$function$;
postgres=# SELECT ft();
NOTICE: {1,2,3}
NOTICE: {4,5,6}
ft
────
(1 row)
Přidáno v 9.2
GET STACKED DIAGNOSTICS v PL/pgSQL
Na jednom projektu, na kterém jsem spolupracoval jsem potřeboval zachytit výjimku, zalogovat ji, a přeposlat dál. Což v PL/pgSQL bylo možné jen částečně, takže jsem narazil. V PL/pgSQL jsem neměl způsob, jak se dostat k jednotlivým položkám výjimky vyjma SQLERRM a SQLCODE. Po implementaci příkazu GET STACKED DIAGNOSTICS jsou veškerá data z výjimky (včetně kontextu) čitelná i z PL/pgSQL.
CREATE OR REPLACE FUNCTION stacked_diagnostics_test()
RETURNS void AS $$
DECLARE _detail text;
_hint text;
_message text;
BEGIN
perform raise_test();
EXCEPTION WHEN OTHERS THEN
GET STACKED DIAGNOSTICS
_message = message_text,
_detail = pg_exception_detail,
_hint = pg_exception_hint;
RAISE NOTICE 'message: %, detail: %, hint: %', _message, _detail, _hint;
END;
$$ LANGUAGE plpgsql;
SELECT stacked_diagnostics_test();
Přidáno v 9.3
RETURN a RETURN NEXT může obsahovat i výraz kompozitního typu.
Přístup k dalším položkám popisu výjimky
Příkaz GET STACKED DIAGNOSTICS byl rozšířen o přístup k položkám: COLUMN_NAME, TABLE_NAME, SCHEMA_NAME, CONSTRAINT_NAME a PG_DATATYPE_NAME. V příkazu RAISE lze specifikovat položky: COLUMN, TABLE, SCHEMA, CONSTRAINT a DATATYPE.
CREATE OR REPLACE FUNCTION stacked_diagnostics_test()
RETURNS void AS $$
DECLARE _column_name text;
_constraint_name text;
_datatype_name text;
_table_name text;
_schema_name text;
BEGIN
RAISE EXCEPTION USING
column = '>>some column name<<',
constraint = '>>some constraint name<<',
datatype = '>>some datatype name<<',
table = '>>some table name<<',
schema = '>>some schema name<<';
EXCEPTION WHEN OTHERS THEN
GET STACKED DIAGNOSTICS
_column_name = column_name,
_constraint_name = constraint_name,
_datatype_name = pg_datatype_name,
_table_name = table_name,
_schema_name = schema_name;
RAISE NOTICE 'column %, constraint %, type %, table %, schema %',
_column_name, _constraint_name, _datatype_name, _table_name, _schema_name;
END;
$$ LANGUAGE plpgsql;
Přidáno v 9.4
Možnost přečtení zásobníku volání kdekoliv
Prostřednictvím příkazu GET STACKED DIAGNOSTICS var = PG_EXCEPTION_CONTEXT je možné získat obsah zásobníku volání v obsluze výjimky. 9.4 tuto funkcionalitu vkládá i do příkazu GET DIAGNOSTICS (použití kdekoliv):
CREATE OR REPLACE FUNCTION public.inner_func() RETURNS integer AS $$ DECLARE stack text; BEGIN GET DIAGNOSTICS stack = PG_CONTEXT; RAISE NOTICE e'--- Call Stack ---\n%', stack; RETURN 1; END; $$ LANGUAGE plpgsql;
Přidáno v 9.5
Interní změnou je reimplementace přetypování, a zrychlení přístupu k prvkům pole (read/write).
Příkaz ASSERT
Příkaz ASSERT slouží ke kontrole předpokladů běhu funkce. V případě, že první povinný výraz není true vyhodí nezachytitelnou výjimku s textem volitelného druhého parametru.
postgres=# DO $$BEGIN ASSERT false; END;$$; ERROR: assertion failed CONTEXT: PL/pgSQL function inline_code_block line 1 at ASSERT postgres=# DO $$BEGIN ASSERT false, 'some is wrong'; END;$$; ERROR: some is wrong CONTEXT: PL/pgSQL function inline_code_block line 1 at ASSERT
Přidáno v 9.6
V této verzi nedošlo k žádným viditelným změnám v syntaxi jazyka. Vyřešil se letitý problém s chybějícím kontextem výjimky vyhozené příkazem RAISE EXCEPTION.
Přidáno ve verzi 10
Novinkou je možnost používat tzv tranzitivní tabulky v statement triggerech:
Tranzitivní tabulky
CREATE TABLE test(id int, a int, b int);
CREATE OR REPLACE FUNCTION public.test_trg_func()
RETURNS trigger
AS $f$
DECLARE r record;
BEGIN
FOR r IN SELECT * FROM newtable
LOOP
raise notice '%', r;
END LOOP;
RETURN null;
END;
$$ LANGUAGE plpgsql;
CREATE TRIGGER test_trg AFTER INSERT ON test
REFERENCING NEW TABLE AS newtable
FOR EACH STATEMENT
EXECUTE FUNCTION test_trg_func();
Přidáno ve verzi 11
Ve verzi 11 se zavádí nová kategorie databázových objektů - procedury.
Procedury
Procedury představují aplikační kód spuštěný na serveru. Největším rozdílem vůči funkcím (pominuli syntax) je přístup k transakcím. Pokud je tzv top příkazem příkaz CALL, pak se procedury vykonávají v prostředí tzv zřetězených transakcí - ukončení jedné transakce automaticky startuje druhou - a v tomto prostředí máme možnost explicitně řídit (potvrzovat, odvolávat) transakce. Oproti tomu funkce běží vždy v rámci příkazu SELECT nebo INSERT, UPDATE, DELETE - který běží pod vnější transakcí, a je zodpovědností toho, kdo, co nastartovalo tuto transakci, také tuto transakci ukončit. Řízení transakcí není v kompetenci funkcí. Je ale možné použít implicitně safepoint - klauzule EXCEPTION WHEN nebo vyhozením výjimky RAISE, pokud nebude obsloužena, si vynutit odvolání vnější transakce.
Jelikož z pohledu řízení transakcí je každý příkaz uvnitř procedury top příkazem, je možné v procedurách volat příkazy, které vyžadují být top přikazem.
Pozor - anonymní blok (příkaz DO) zůstává nepojmenovanou funkcí.
CREATE OR REPLACE PROCEDURE public.p1(a integer, b integer)
AS $$
DECLATE counter int DEFAULT 0;
BEGIN
FOR i IN 1..a
LOOP
INSERT INTO test VALUES(i, random()*1000, random()*1000);
counter := counter + 1;
IF (counter % b) = 0 THEN
COMMIT;
END IF;
END LOOP;
ROLLBACK; -- poslední block, bude odvolán
END;
$$ LANGUAGE plpgsql;
CALL p1(99, 10);
V případě, že nedojde k výjimce, a příkaz CALL je top příkazem, pak se transakce automaticky potvrdí. Tedy není nutné explicitně volat příkaz COMMIT na konci procedury jako v některých jiných databázích. Procedury mohou používat IN, případně INOUT parametry.
Přidáno ve verzi 12
Během posledních tří verzí došlo k výrazným optimalizacím kódu. Navenek tyto změny nejsou nijak zvlášť viditelné - některé operace mohou být rychlejší, některé omezení pro práci s proměnnými (např. kompozitního typu) byly odstraněna. Pro vývojáře PLpgSQL je ve verzi 12 důležitá podpora API pro PLpgSQL profilery. Počínáje verzí 12 můžete profilovat kód v PLpgSQL v podstatě s minimální výkonnostní režií (téměř nulovou). Nově uživatel může aktivovat extra běhové (runtime) kontroly strict_multi_assignment a too_many_rows.
Přidáno ve verzi 13
V této verzi došlo k dramatickému zrychlení jednoduchých výrazů (neobsahující dotazy do tabulek).
Přidáno ve verzi 14
V podstatě jedinou změnou (ale vítanou na kterou jsme si museli počkat dlouho) je plnohodnotná podpora výrazů na levé straně přiřazovacího příkazu. Počínaje touto verzí můžeme jedním příkazem změnit zanořenou hodnotu atributu kompozitní položky v poli:
create type bod as (x int, y int);
do $$
declare b bod[];
begin
b[10].x := 20;
RAISE NOTICE '%', b;
end
$$;
NOTICE: [10:10]={"(20,)"}
Doporučení pro návrh uložených procedur v jazyce PL/pgSQL
Na internetu můžete nalézt řadu doporučení pro psaní uložených procedur v PL/SQL fy. Oracle. Implementace PL/pgSQL je jiná a tudíž ne všechna doporučení jsou relevantní pro PostgreSQL. V PL, stejně jako v jiných prg. jazycích lze napsat nečitelný a špatně udržovatelný, případně neefektivní kód. Riziko, že Váš kód bude nevyhovující, snížíte, pokud se budete držet následujících doporučení:
- kód pište v klasickém (programátorském) editoru a ukládejte do souboru. V souborech můžete lépe udržovat komentáře, můžete sdružovat funkčně blízké nebo závislé funkce, můžete verzovat kód. Nepoužívejte nástroje jako je pgAdmin nebo phpPgAdmin.
- braňte se kolizi lokálních proměnných a databázových objektů:
- používejte prefixy proměnných (např. pro lokální proměnné symbol "_")
- používejte kvalifikované atributy (tabulka.sloupec) ve všech SQL příkazech v procedurách)
- proměnné deklarujte pomocí odvozených typů - %TYPE a %ROWTYPE
- v PL používejte nativní SQL všude, kde je to možné a rozumné. Pokud byste měli použít příliš komplikovaný dotaz, je možné že PL kód bude rychlejší. Snažte se nepoužívat dynamické SQL. Přemýšlejte, zda nemůžete cyklus ve funkci nahradit klasickým SELECTem obsahujícím konstrukci CASE.
-- neefektivni kod
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;
-- efektivni kod
INSERT INTO tab(id, cena)
VALUES(_id, CASE WHEN _cena <= _cena_max
THEN _cena ELSE _cena_max END);
-- neefektivni kod
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;
-- efektivni kod
INSERT INTO tab2
SELECT 20,c2 FROM tab1 WHERE c1 > 20;
INSERT INTO tab3
SELECT c1,c2 FROM tab1 WHERE c1 <= 20;
-- neefektivní kód
FOR i IN array_lower(delitems,1) .. array_upper(delitems,1)
LOOP
DELETE FROM tab
WHERE tab.id = delitems[i];
END LOOP;
-- efektivni kod
DELETE FROM tab
WHERE tab.id = ANY(delitems);
- funkce má obsahovat pouze jeden příkaz RETURN - jedna cesta dovnitř, jedna cesta ven.
- nepište redundantní kód - v aplikaci by se neměl objevit dvakrát stejný kód.
- používejte funkci Assert, případně její modifikace:
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;
- dodržujte předem dohodnutou notaci pro texty výjimek, předem se domluvte na seznamu uživatelem definovaných chyb a chybových hlášení.
- netestujte na NULL proměnnou, která je deklarována jako NOT NULL. Je na PL, aby zajistil, že tato proměnná v žádném případě nebude obsahovat NULL. Pro zjištění úspěšnosti dotazu netestujte obsah proměnných modifikovaných dotazem, ale vždy obsah logické systémové proměnné FOUND.
- používejte návěstí <<label>> pro smyčky a bloky, využívejte typ boolean, tak aby Váš kód byl, co možná, nejčitelnější.
- nespoléhejte se na automatické konverze typu date a timestamp, které závisí na konfiguraci. Používejte funkce to_char a to_date.
- nepoužívejte IF pro naplnění logické proměnné:
is_ok := _age > 18;
- každou proměnnou používejte pouze k jednomu jasnému účelu. Z kódu odstraňte nepoužívané proměnné.
- Výchozí kategorie funkce je VOLATILE. Pokud v těle funkce nepřistupujete k tabulkám a nepoužíváte funkce typu random(), currval() atd, používejte kategorie IMMUTABLE nebo STABLE. Doba zpracování funkce může být i o polovinu kratší.
- zbytečně nezapouzdřujte SQL příkazy do jednoduchých funkcí.
- snažte se omezit kurzory a dočasné tabulky.
- preferujte standardizované funkce před vlastními.
- v triggerech "tajně" neopravujte data, posuďte zda nemůžete použít CHECK
- každá procedura by měla obsahovat maximálně 50 až 60 řádek
- používejte unit testy (k ověření identifikace chyby, k ověření korektnosti opravy)
- zapouzdřete vyvolání výjimky do vlastní procedury
- vždy ve svém kódu použijte pouze jeden ze dvou možných způsobů (výjimky a návratový kód) signalizace chyby
- snažte se opakované přiřazení do proměnné (u typů varchar a array) sloučit do jednoho výrazu
--špatně
DECLARE v varchar;
BEGIN
v := 'a';
v := v || 'b';
v := v || 'c';
RETURN v;
END;
--dobře
BEGIN
RETURN 'a' || 'b' || 'c';
END;
-- špatně
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ávně
DECLARE s varchar;
BEGIN
s := COALESCE(x1 || ',', 'NULL,')
|| COALESCE(x2 || ',', 'NULL,')
...
- Funkce obsahující pouze jeden příkaz nepiště v PL/pgSQL, ale v SQL:
--špatně CREATE OR REPLACE FUNCTION foo() RETURNS varchar AS $$ BEGIN RETURN 'a' || 'b' || 'c'; END; $$ LANGUAGE plpgsql IMMUTABLE; --dobře CREATE OR REPLACE FUNCTION foo() RETURNS varchar AS $$ SELECT 'a' || 'b' || 'c'; $$ LANGUAGE sql;
- pište, co nejčitelněji - nevymýšlejte složité konstrukce:
--špatně CREATE OR REPLACE FUNCTION hex_to_int(varchar) RETURNS integer AS ' DECLARE h alias for $1; exec varchar; curs refcursor; res int; BEGIN exec := ''SELECT x'''''' || h || ''''''::int''; OPEN curs FOR EXECUTE exec; FETCH curs INTO res; CLOSE curs; return res; END;' LANGUAGE 'plpgsql' IMMUTABLE STRICT; -- dobře CREATE FUNCTION hex2dec(in_hex TEXT) RETURNS INT IMMUTABLE STRICT AS $body$ DECLARE result integer; BEGIN EXECUTE 'SELECT (x' || quote_literal($1) || ')::int' INTO result; RETURN result; END; $body$ LANGUAGE plpgsql;