Hlavní navigace

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

23. 9. 2003
Doba čtení: 7 minut

Sdílet

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.

root_podpora

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.

Byl pro vás článek přínosný?

Autor článku

Pavel Stěhule je odborníkem na relační databázový systém PostgreSQL, pracuje jako školitel a konzultant.