Domény

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));