Automatická kontrola rodného čísla
Profesionální programování je více-méně o tvorbě knihoven. Programátor, ktrerý staví stále na zelené louce nebude těžko bude produktivní. Klasické jazyky podporují vytváření knihoven a modulů. K dispozici je dostatek kvalitních knihoven, kterými se můžeme inspirovat při tvorbě vlastních knihoven.
To bohužel neplatí u programování v prostředí uložených procedur. Zatím se nijak zvlášť oproti ostatním neprosadil žádný koncept. Jednou z možností, jak organizovat vlastní knihovny je vytváření vlastních "inteligentních" datových typů. Jelikož PostgreSQL vzniklo v akademickém prostředí, které je co se týče potřeby ukládání specializovaných dat podstatně pestřejší a náročnější než ekonomicko-finanční sféra (na kterou bylo původně SQL cílené), PostgreSQL od samotného svého počátku disponovalo možností vytváření vlastních datových formátů. V klasickém SQL je příliš úzký rejstřík datových typů, v čehož důsledku musíme používat obecné typy TEXT nebo BLOB. Díky jejich obecnosti dokážeme do nich uložit libovolnou textovou nebo binární hodnotu, ale nic víc. Tyto typy neposkytují žádné další (specializované) operace.
Vlastní datový typ v PostgreSQL může být implementován pouze v jazycích C nebo Java. Kromě skutečných datových typů ještě existují složené datové typy (obdoba typu RECORD v Pascalu). Kromě vlastních binárních datových typů můžeme definovat vlastní doménové typy. Následující příklad obsahuje podporu rodného čísla právě pomocí domén.
Rodné číslo je hodnota s celkem dost vysokou redundanci, zvlášť když máme k dispozici datum narozeni a pohlaví osoby. Ve formuláři nedokáže zabránit záměrně falšované hodnotě, ale s celkem s vysokou pravděpodobností dokaze zachytit překlepy. Vzhledem k tomu, že obsahuje citlivé udaje (věk, pohlaví) předpokládá se, že bude v dohledné době nahrazen neutrálním jedinečným identifikátorem osob (uvažuje se o číslu sociálního zabezpečení).
CREATE OR REPLACE FUNCTION check_form_rodne_cislo(varchar, boolean)
RETURNS boolean AS $$
DECLARE
str_parts varchar[];
num_parts integer[];
after_1953 boolean;
birthday_str varchar;
BEGIN
SELECT INTO str_parts regexp_matches
FROM regexp_matches($1, E'^(\\d{2})(\\d{2})(\\d{2})(/)?(\\d{3,4})$');
IF FOUND THEN
-- Test existence lomitka v rodnem cisle rizeny druhym parametrem,
-- ve vyznamu NULL (nema vyznam), true - povinne, false - nepovinne.
IF $2 <> (str_parts[4] IS NOT NULL) THEN
RETURN false;
END IF;
-- Test modulo 11 se provadi pouze tehdy, pokud existuje
-- kontrolni cislice, tj. pocinaje rokem 1954.
after_1953 := char_length(str_parts[5]) = 4;
IF after_1953
AND to_number($1, CASE WHEN str_parts[4] IS NULL
THEN '9999999999'
ELSE '999999/9999' END) % 11 <> 0 THEN
-- Nicmene z tohoto pravidla existuje vyjimka cca 1000
-- rodnych cisel, kdy modulo 9 mistneho cisla je rovno 10
-- a kontrolni cislice je 0
IF NOT (to_number($1, CASE WHEN str_parts[4] IS NULL
THEN '999999999'
ELSE '999999/999' END) % 11 = 10
AND to_number($1, CASE WHEN str_parts[4] IS NULL
THEN '?????????9'
ELSE '??????/???9' END) = 0) THEN
RETURN false;
END IF;
END IF;
-- Kontrola validniho datumu, po roce 2004 se mohou
-- k mesici pricitat hodnoty 20 (pro muze) a 70 pro zeny.
num_parts := ARRAY[to_number(str_parts[1],'99'),
to_number(str_parts[2],'99'),
to_number(str_parts[3],'99')];
num_parts := ARRAY[CASE WHEN NOT after_1953
THEN 1900 + num_parts[1]
WHEN after_1953 AND num_parts[1] >= 54
THEN 1900 + num_parts[1]
ELSE 2000 + num_parts[1] END,
CASE WHEN num_parts[2] > 70
THEN num_parts[2] - 70
WHEN num_parts[2] > 50
THEN num_parts[2] - 50
WHEN num_parts[2] > 20
THEN num_parts[2] - 20
ELSE num_parts[2] END,
num_parts[3]];
birthday_str := replace(to_char(num_parts[1],'9999')
|| to_char(num_parts[2],'09')
|| to_char(num_parts[3],'09'),
' ','');
RETURN birthday_str = to_char(to_date(birthday_str, 'YYYYMMDD'),
'YYYYMMDD');
END IF;
RETURN false;
END;
$$ LANGUAGE plpgsql;
V případě binárního vlastního typu potřebujeme ještě konverzní funkce z typu rc, rc1 a rc2 do typů date a boolean, které použijeme v definici přetypování (CREATE CAST). Nic takového pro domény neexistuje. Nicméně mohu napsat funkce to_date a to_boolean a ty používat. Aby nedocházelo k opakování kódu, existuje implicitní přetypování rc1->rc a rc2->rc, což je korektní (rc je obecnější než rc1 nebo rc2). Funkce to_bool a to_date musím volat explicitně.
CREATE DOMAIN rc VARCHAR CHECK (check_form_rodne_cislo(value, null::boolean));
-- Predpokladam validni vstup (overeny funkci check_form_rodne_cislo).
-- Typ parametru varchar je zvolen proto, protoze je predkem domen rc,
-- rc1 a rc2. Domeny se lisi vztahem vuci lomitku uvnitr rodneho cisla.
CREATE OR REPLACE FUNCTION to_date(rc)
RETURNS date AS $$
DECLARE
str_parts varchar[];
num_parts integer[];
after_1953 boolean;
birthday_str varchar;
BEGIN
SELECT INTO str_parts regexp_matches
FROM regexp_matches($1, E'^(\\d{2})(\\d{2})(\\d{2})(/)?(\\d{3,4})$');
IF FOUND THEN
after_1953 := char_length(str_parts[5]) = 4;
-- Po roce 2004 se mohou k mesici pricitat
-- hodnoty 20 (pro muze) a 70 pro zeny. Pred timto rokem
-- se vzdy pricita 50 pro zeny
num_parts := ARRAY[to_number(str_parts[1],'99'),
to_number(str_parts[2],'99'),
to_number(str_parts[3],'99')];
num_parts := ARRAY[CASE WHEN NOT after_1953
THEN 1900 + num_parts[1]
WHEN after_1953 AND num_parts[1] >= 54
THEN 1900 + num_parts[1]
ELSE 2000 + num_parts[1] END,
CASE WHEN num_parts[2] > 70
THEN num_parts[2] - 70
WHEN num_parts[2] > 50
THEN num_parts[2] - 50
WHEN num_parts[2] > 20
THEN num_parts[2] - 20
ELSE num_parts[2] END,
num_parts[3]];
RETURN to_date(
replace(to_char(num_parts[1],'9999')
|| to_char(num_parts[2],'09')
|| to_char(num_parts[3],'09'),
' ',''),
'YYYYMMDD');
END IF;
RAISE NOTICE 'Incorrect format of type rodne cislo (value: %)', $1;
END;
$$ LANGUAGE plpgsql;
-- Predpokladam validni vstup (overeny funkci check_form_rodne_cislo).
-- Typ parametru varchar je zvolen proto, protoze je predkem domen rc,
-- rc1 a rc2. Domeny se lisi vztahem vuci lomitku uvnitr rodneho cisla.
CREATE OR REPLACE FUNCTION to_bool(rc)
RETURNS boolean AS $$
DECLARE
str_part varchar[];
birthday_mm integer;
BEGIN
SELECT INTO str_part regexp_matches
FROM regexp_matches($1, E'^\\d{2}(\\d{2})\\d{2}/?\\d{3,4}$');
IF FOUND THEN
birthday_mm := to_number(str_part[1],'09');
RETURN CASE WHEN birthday_mm > 50
THEN false
ELSE true END;
END IF;
RAISE NOTICE 'Incorrect format of type rodne cislo (value: %)', $1;
END;
$$ LANGUAGE plpgsql;
-- Registrace domen
CREATE DOMAIN rc1 VARCHAR CHECK (check_form_rodne_cislo(value, false));
CREATE DOMAIN rc2 VARCHAR CHECK (check_form_rodne_cislo(value, true));
-- vytvoreni konverznich pravidel, rc je zevseobecnenim rc1 a rc2
CREATE CAST (rc1 AS rc) WITHOUT FUNCTION AS IMPLICIT;
CREATE CAST (rc2 AS rc) WITHOUT FUNCTION AS IMPLICIT;
Použití
CREATE TABLE foo (r rc1);
INSERT INTO foo('7307--/----');
-- ziskani narozeni z udaju v db
SELECT to_date(r)
FROM foo;