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řes srdce při testování. Mnou vytvořené funkce AddWDays a AddWDays2 jsem kontroloval porovnává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, jejich počet je určen druhým parametrem. Funkce DiffWDays vrací počet pracovních dnů v intervalu určeném parametry. Pokud jsou všechny funkce správně navržené, musí být výsledkem testovacích dotazů prázdná tabulka.
CREATE OR REPLACE FUNCTION DaysInYear(INTEGER) RETURNS SETOF DATE AS '
DECLARE st DATE;
BEGIN st := to_date(''0101''||$1,''MMDDYYYY'');
WHILE EXTRACT(year FROM st) < $1 + 1 LOOP
RETURN NEXT st;
st := st + 1;
END LOOP;
RETURN;
END' LANGUAGE plpgsql;
CREATE OR REPLACE FUNCTION Seq(INTEGER) RETURNS SETOF INTEGER AS '
DECLARE i INTEGER;
BEGIN i := 1;
WHILE i <= $1 LOOP
RETURN NEXT i;
i := i + 1;
END LOOP;
RETURN;
END' LANGUAGE plpgsql;
-- testy
SELECT DaysInYear, Seq FROM DaysInYear(2003) CROSS JOIN Seq(40)
WHERE AddWDays(DaysInYear, Seq) <> AddWDays2(DaysInYear, Seq);
SELECT DaysInYear, Seq FROM DaysInYear(2003) CROSS JOIN Seq(40)
WHERE DiffWDays(DaysInYear, AddWDays(DaysInYear, Seq)) <> Seq;
Pro určení pracovních dnů musím znát i státní svátky vyskytující se v relevantním období. České státní svátky jsou pevně určené. Jedinou výjimkou jsou velikonoce. Použil jsem Carterterův algoritmus pro určení velikonoční neděle.
CREATE OR REPLACE FUNCTION velnedele(INTEGER) RETURNS DATE AS '
DECLARE B INTEGER; D INTEGER; E INTEGER; Q INTEGER;
DD INTEGER; MM INTEGER;
BEGIN
IF $1 < 1900 OR $1 > 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($1,''0000''), '' DD MM YYYY'');
END' LANGUAGE plpgsql;
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 StatniSvatky vrací tabulku se státními svátky včetně názvu pro období dané prvním a druhým parametrem. Funkce PocetStatnichSvatku 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().
CREATE TYPE StatniSvatek AS (termin DATE, nazev varchar(50));
CREATE OR REPLACE FUNCTION StatniSvatky(DATE, DATE) RETURNS SETOF StatniSvatek AS '
DECLARE
svatek VARCHAR [][] DEFAULT ARRAY
[[''Nový rok'', ''0101''],
[''Neděle velikonoční'', ''xxxx''],
[''Pondělí velikonoční'',''xxx1''],
[''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;
sv StatniSvatek%ROWTYPE;
BEGIN stepd := $1;
WHILE stepd <= $2 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 = ''xxxx'' THEN
sv.termin := velnedele(rok);
ELSIF d = ''xxx1'' THEN
sv.termin := sv.termin +1;
ELSE
sv.termin := to_date(d||rok,''MMDDYYYY'');
END IF;
IF sv.termin BETWEEN stepd AND $2 THEN
sv.nazev := svatek[i][1]; RETURN NEXT sv;
END IF;
END LOOP;
stepd := date_trunc(''year'', stepd) + interval ''1 year'';
END LOOP;
RETURN;
END' LANGUAGE plpgsql STRICT;
CREATE OR REPLACE FUNCTION PocetStatnichSvatku(DATE, DATE, INTEGER[]) RETURNS INTEGER AS '
DECLARE
svatek VARCHAR [] DEFAULT ARRAY
[''0101'', ''xxxx'', ''xxx1'', ''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 := $1;
WHILE stepd <= $2 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 = ''xxxx'' THEN
sv := velnedele(rok);
ELSIF d = ''xxx1'' THEN
sv := sv +1;
ELSE
sv := to_date(d||rok,''MMDDYYYY'');
END IF;
IF NOT EXTRACT(dow FROM sv) = ANY ($3) THEN
IF sv BETWEEN stepd AND $2 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 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 ode 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(DATE, INTEGER) RETURNS DATE AS '
DECLARE st DATE; ed DATE; svatky DATE[]; d INTEGER; k INTEGER;
BEGIN
IF $2 < 0 THEN RAISE EXCEPTION ''Out of range''; END IF;
st := $1; d := $2; 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á funkci 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í. Poté probíhá jakási normalizace, posouvám se zpět na neděli, o kolik se posunu, o tolik 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(DATE, INTEGER) RETURNS DATE AS '
DECLARE st DATE; ed DATE; korekce INTEGER; sv INTEGER; d INTEGER;
BEGIN
IF $2 < 0 THEN RAISE EXCEPTION ''Out of range''; END IF;
st := $1; ed := AddWDay(st,$2);
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 STRICT;
CREATE OR REPLACE FUNCTION AddWDay(DATE, INTEGER) RETURNS DATE AS '
DECLARE st DATE; wd INTEGER; k INTEGER;
BEGIN k := CAST(EXTRACT(DOW FROM $1) AS INTEGER);
IF k = 0 THEN st := $1 + 1; k := 1;
ELSIF k = 6 THEN st := $1 + 2; k := 1;
ELSE st := $1; END IF;
st := st - k; wd := $2 + k - 2;
st := st + ((wd) / 5) * 7 + ((wd) % 5) + 1;
RETURN st;
END' LANGUAGE plpgsql 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(DATE, DATE) RETURNS INTEGER AS '
DECLARE wc INTEGER; st DATE; ed DATE; p INTEGER;
BEGIN
p := ISODOW($1); IF p > 5 THEN st := $1 + (8 - p); ELSE st := $1; END IF;
p := ISODOW($2); IF p > 5 THEN ed := $2 - (p - 5); ELSE ed := $2; END IF;
wc := ($2 - $1) / 7;
IF EXTRACT(DOW FROM st - 1) + 1 > EXTRACT(DOW FROM ed - 1) + 1 THEN
wc := wc + 1;
END IF;
RETURN (ed - st - (wc * 2) - PocetStatnichSvatku(st,ed, ARRAY[6,0]) + 1);
END' LANGUAGE plpgsql STRICT;
Vůči kalendáři můžeme snadno ověřit správnou funkčnost
testdb011=> SELECT DaysInYear, Seq FROM DaysInYear(2003) CROSS JOIN Seq(40)
testdb011-> WHERE DiffWDays(DaysInYear, AddWDays(DaysInYear, Seq)) <> Seq;
daysinyear | seq
------------+-----
(0 řádek)
testdb011=> SELECT AddWDays('20031024'::DATE, 3);
addwdays
------------
2003-10-29
(1 řádka)
Zde uvedené funkce jsou pouze jedním z příkladů použití SRF. V současnosti řešíme určení přístupových práv k dokumentům včetně dočasné zastupitelnosti, kde si vzhledem k rekurzivnímu charakteru zastupitelnosti nevystačíme s jedním dotazem a SRF jsou jednou z cest, jak se vyhnout dočasným tabulkám a zároveň nekomplikovat vlastní aplikaci.