http://postgres.cz/api.php?action=feedcontributions&user=85.160.69.92&feedformat=atom
PostgreSQL - Příspěvky [cs]
2024-03-29T13:31:02Z
Příspěvky
MediaWiki 1.36.0
http://postgres.cz/index.php?title=Dom%C3%A9ny&diff=305
Domény
2007-08-10T17:25:55Z
<p>85.160.69.92: </p>
<hr />
<div><pre><br />
CREATE OR REPLACE FUNCTION check_form_rodne_cislo(varchar, boolean)<br />
RETURNS boolean AS $$<br />
DECLARE<br />
str_parts varchar[];<br />
num_parts integer[];<br />
after_1953 boolean;<br />
birthday_str varchar;<br />
BEGIN<br />
SELECT INTO str_parts regexp_matches<br />
FROM regexp_matches($1, E'^(\\d{2})(\\d{2})(\\d{2})(/)?(\\d{3,4})$');<br />
IF FOUND THEN<br />
-- Test existence lomitka v rodnem cisle rizeny druhym parametrem, <br />
-- ve vyznamu NULL (nema vyznam), true - povinne, false - nepovinne. <br />
IF $2 <> (str_parts[4] IS NOT NULL) THEN<br />
RETURN false;<br />
END IF;<br />
-- Test modulo 11 se provadi pouze tehdy, pokud existuje <br />
-- kontrolni cislice, tj. pocinaje rokem 1954. <br />
after_1953 := char_length(str_parts[5]) = 4;<br />
IF after_1953<br />
AND to_number($1, CASE WHEN str_parts[4] IS NULL<br />
THEN '9999999999'<br />
ELSE '999999/9999' END) % 11 <> 0 THEN<br />
RETURN false;<br />
END IF;<br />
-- Kontrola validniho datumu, po roce 2004 se mohou <br />
-- k mesici pricitat hodnoty 20 (pro muze) a 70 pro zeny. <br />
num_parts := ARRAY[to_number(str_parts[1],'99'),<br />
to_number(str_parts[2],'99'),<br />
to_number(str_parts[3],'99')];<br />
num_parts := ARRAY[CASE WHEN NOT after_1953<br />
THEN 1900 + num_parts[1]<br />
WHEN after_1953 AND num_parts[1] >= 54<br />
THEN 1900 + num_parts[1]<br />
ELSE 2000 + num_parts[1] END,<br />
CASE WHEN num_parts[2] > 70<br />
THEN num_parts[2] - 70<br />
WHEN num_parts[2] > 50<br />
THEN num_parts[2] - 50<br />
WHEN num_parts[2] > 20<br />
THEN num_parts[2] - 20<br />
ELSE num_parts[2] END,<br />
num_parts[3]];<br />
birthday_str := replace(to_char(num_parts[1],'9999')<br />
|| to_char(num_parts[2],'09')<br />
|| to_char(num_parts[3],'09'),<br />
' ','');<br />
RETURN birthday_str = to_char(to_date(birthday_str, 'YYYYMMDD'),<br />
'YYYYMMDD');<br />
END IF;<br />
RETURN false;<br />
END;<br />
$$ LANGUAGE plpgsql;<br />
<br />
-- Predpokladam validni vstup (overeny funkci check_form_rodne_cislo). <br />
-- Typ parametru varchar je zvolen proto, protoze je predkem domen rc, <br />
-- rc1 a rc2. Domeny se lisi vztahem vuci lomitku uvnitr rodneho cisla. <br />
CREATE OR REPLACE FUNCTION form_rodne_cislo_to_date(varchar)<br />
RETURNS date AS $$<br />
DECLARE<br />
str_parts varchar[];<br />
num_parts integer[];<br />
after_1953 boolean;<br />
birthday_str varchar;<br />
BEGIN<br />
SELECT INTO str_parts regexp_matches<br />
FROM regexp_matches($1, E'^(\\d{2})(\\d{2})(\\d{2})(/)?(\\d{3,4})$');<br />
IF FOUND THEN<br />
after_1953 := char_length(str_parts[5]) = 4;<br />
-- Po roce 2004 se mohou k mesici pricitat <br />
-- hodnoty 20 (pro muze) a 70 pro zeny. Pred timto rokem <br />
-- se vzdy pricita 50 pro zeny <br />
num_parts := ARRAY[to_number(str_parts[1],'99'),<br />
to_number(str_parts[2],'99'),<br />
to_number(str_parts[3],'99')];<br />
num_parts := ARRAY[CASE WHEN NOT after_1953<br />
THEN 1900 + num_parts[1]<br />
WHEN after_1953 AND num_parts[1] >= 54<br />
THEN 1900 + num_parts[1]<br />
ELSE 2000 + num_parts[1] END,<br />
CASE WHEN num_parts[2] > 70<br />
THEN num_parts[2] - 70<br />
WHEN num_parts[2] > 50<br />
THEN num_parts[2] - 50<br />
WHEN num_parts[2] > 20<br />
THEN num_parts[2] - 20<br />
ELSE num_parts[2] END,<br />
num_parts[3]];<br />
RETURN to_date(<br />
replace(to_char(num_parts[1],'9999')<br />
|| to_char(num_parts[2],'09')<br />
|| to_char(num_parts[3],'09'),<br />
' ',''),<br />
'YYYYMMDD');<br />
END IF;<br />
RAISE NOTICE 'Incorrect format of type rodne cislo (value: %)', $1;<br />
END;<br />
$$ LANGUAGE plpgsql;<br />
<br />
-- Predpokladam validni vstup (overeny funkci check_form_rodne_cislo). <br />
-- Typ parametru varchar je zvolen proto, protoze je predkem domen rc, <br />
-- rc1 a rc2. Domeny se lisi vztahem vuci lomitku uvnitr rodneho cisla. <br />
CREATE OR REPLACE FUNCTION form_rodne_cislo_to_bool(varchar)<br />
RETURNS boolean AS $$<br />
DECLARE<br />
str_part varchar[];<br />
birthday_mm integer;<br />
BEGIN<br />
SELECT INTO str_part regexp_matches<br />
FROM regexp_matches($1, E'^\\d{2}(\\d{2})\\d{2}/?\\d{3,4}$');<br />
IF FOUND THEN<br />
birthday_mm := to_number(str_part[1],'09');<br />
RETURN CASE WHEN birthday_mm > 50<br />
THEN false<br />
ELSE true END;<br />
END IF;<br />
RAISE NOTICE 'Incorrect format of type rodne cislo (value: %)', $1;<br />
END;<br />
$$ LANGUAGE plpgsql;<br />
<br />
-- Registrace domen <br />
CREATE DOMAIN rc VARCHAR CHECK (check_form_rodne_cislo(value, null::boolean));<br />
CREATE DOMAIN rc1 VARCHAR CHECK (check_form_rodne_cislo(value, false));<br />
CREATE DOMAIN rc2 VARCHAR CHECK (check_form_rodne_cislo(value, true));<br />
<br />
</pre></div>
85.160.69.92