85.160.69.92 v 10. 8. 2007, 17:25

2007-08-10T17:25:55Z

Nová stránka

<pre>
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
RETURN false;
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;

-- 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 form_rodne_cislo_to_date(varchar)
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 form_rodne_cislo_to_bool(varchar)
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 rc VARCHAR CHECK (check_form_rodne_cislo(value, null::boolean));
CREATE DOMAIN rc1 VARCHAR CHECK (check_form_rodne_cislo(value, false));
CREATE DOMAIN rc2 VARCHAR CHECK (check_form_rodne_cislo(value, true));

</pre>

Domény - Historie editací

85.160.69.92 v 10. 8. 2007, 17:25