Hlavní navigace

Použití SRF funkcí v jazyce Pl/pgSQL při návrhu plánovacích funkcí

Pavel Stěhule

PostgreSQL již více než rok podporuje tzv. funkce vracející tabulky (Set Returned Function - SRF). Pole byla podporována verzí 6.0 (leden 1997), konstrukce =ALL() a konstruktor pole ARRAY jsou ale až ve verzi 7.4. Kromě ukázek demonstrujících tyto možnosti jsem se však nesetkal s praktickým příkladem využívajícím těchto schopností PostgreSQL. Pokusil jsem se, inspirován UDF Firebirdu (vracela tabulku termínů německých pivních festivalů), je použít při návrhu funkcí pro určení dne získaného přičtením n pracovních dnů k zadanému dni.

SRF funkce se hodí pro generování menších dynamických (parametrizova­telný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.

Našli jste v článku chybu?

25. 9. 2003 20:06

Pavel (neregistrovaný)

Jeste si to po sobe precist :-(. Presne naopak, napred jsem mel AddWDay a pak teprv AddWDays

25. 9. 2003 20:01

Pavel (neregistrovaný)

Pochopil jsem to spravne :->. Kdybych nemel uz funkci AddWDays, tak bych to napsal, stejne jako ty, pomoci LOOPu. Jenze jsem uz ji mel - napred jsem si testoval jestli mi to spravne pocita bez svatku, tak mne nic nekopalo k tomu, bych dbal na eleganci. :->

Vitalia.cz: 7 originálních adventních kalendářů pro mlsné

7 originálních adventních kalendářů pro mlsné

DigiZone.cz: SES zajistí HD pro M7 Group

SES zajistí HD pro M7 Group

120na80.cz: Rovnátka, která nejsou vidět

Rovnátka, která nejsou vidět

120na80.cz: Boreliózu nelze žádným testem prokázat

Boreliózu nelze žádným testem prokázat

DigiZone.cz: R2B2 a Hybrid uzavřely partnerství

R2B2 a Hybrid uzavřely partnerství

Podnikatel.cz: Podnikatelům dorazí varování od BSA

Podnikatelům dorazí varování od BSA

Vitalia.cz: Test na HIV je zdarma i za pět set

Test na HIV je zdarma i za pět set

Lupa.cz: Kdo pochopí vtip, může jít do ČT vyvíjet weby

Kdo pochopí vtip, může jít do ČT vyvíjet weby

Měšec.cz: Za palivo zaplatíte mobilem (TEST)

Za palivo zaplatíte mobilem (TEST)

Lupa.cz: Insolvenční řízení kvůli cookies? Vítejte v ČR

Insolvenční řízení kvůli cookies? Vítejte v ČR

DigiZone.cz: V Plzni odstartovalo Radio 1

V Plzni odstartovalo Radio 1

DigiZone.cz: Ohrozí Freedom TV přechodové sítě?

Ohrozí Freedom TV přechodové sítě?

Podnikatel.cz: Udávání a účtenková loterie, hloupá komedie

Udávání a účtenková loterie, hloupá komedie

Vitalia.cz: Jak vybrat ořechy do cukroví a kde mají levné

Jak vybrat ořechy do cukroví a kde mají levné

Vitalia.cz: Nejlepší obranou při nachlazení je útok

Nejlepší obranou při nachlazení je útok

Podnikatel.cz: Chtějte údaje k dani z nemovitostí do mailu

Chtějte údaje k dani z nemovitostí do mailu

Podnikatel.cz: K EET. Štamgast už peníze na stole nenechá

K EET. Štamgast už peníze na stole nenechá

120na80.cz: Horní cesty dýchací. Zkuste fytofarmaka

Horní cesty dýchací. Zkuste fytofarmaka

Vitalia.cz: Láska na vozíku: Přitažliví jsme pro tzv. pečovatelky

Láska na vozíku: Přitažliví jsme pro tzv. pečovatelky

Lupa.cz: Levný tarif pro Brno nebude, je to kartel

Levný tarif pro Brno nebude, je to kartel