Dawno niczego nie pisałem z powodu braku czasu (praca, szkoła, konferencje, zmiany w życiu itp itd). Dzisiejszy wpis też nie będzie długi ;)

Tematem jest weryfikacja numeru pesel w PostgreSQL.

Funkcje nie wymagają dodatkowych języków, napisane są w zwykłym SQL więc powinny też działać w innych bazach danych. Pisałem je pod 8.3.

Kilka uwag ogólnych:

  • istnieją nie poprawne numery pesel
  • najwydajniej przechowywać informacje o poprawności w osobnej kolumnie – typ bool nie zabiera za dużo miejsca ale jest najszybszy pod względem obliczeniowym
  • przechowuj numer w polu char/text – jeżeli będzie to cyfra to może zaczynać się od 0 i wtedy będzie miała inną długość – po pobraniu numer będzie krótszy niż 11 znaków

Obliczanie sumy kontrolnej

-- Function: pesel_generate_check_digit(text)
-- @license GPL
-- @author Grzegorz Drozd (http://grzegorzdrozd.pl)
-- @param text numer pesel
-- @return int
-- DROP FUNCTION pesel_generate_check_digit(text);
CREATE OR REPLACE FUNCTION pesel_generate_check_digit(text)
  RETURNS integer AS
$BODY$
    -- wyciagniecie ostatniej cyfry z sumy pomnozonych cyfr numery pesel przez odpowiednia wage
    SELECT MOD(10 - SUBSTR(s, char_length(s),1)::int, 10)::int FROM (
        SELECT
            sum((ARRAY[1,3,7,9,1,3,7,9,1,3])[n]*digit)::text as s
        FROM (
            SELECT
                -- wyciegniecie ntej cyfry
                -- MOD( ( SUBSTRING($1::text FROM 0 FOR 11)::bigint / (10^n)::int8 ), 10::int8) AS digit, n
                SUBSTRING($1 FROM n FOR 1)::bigint AS digit, n
            FROM
                generate_series(1, 10) AS n
        ) AS foo
    ) AS foo2;
$BODY$
  LANGUAGE sql IMMUTABLE
  COST 100;
ALTER FUNCTION pesel_generate_check_digit(text) OWNER TO postgres;

Argumentem tej funkcji jest numer pesel (a właściwie pierwsze 10 cyfr numeru pesel).

Funkcja zwraca cyfra kontrolną.

Weryfikacja poprawności numeru pesel

-- weryfikacja czy pesel jest poprawny
-- @license GPL
-- @author Grzegorz Drozd (http://grzegorzdrozd.pl)
-- @param text numer pesel
-- @return bool
--  DROP FUNCTION pesel_is_valid(text);
CREATE OR REPLACE FUNCTION pesel_is_valid(text) RETURNS BOOLEAN AS
$BODY$
    -- wyciagniecie cyfry kontrolej i porownanie jej z wygenerowana cyfra
    SELECT (SUBSTRING(lpad($1,11,'0') FROM 11 )) = pesel_generate_check_digit(SUBSTRING($1 FROM 0 FOR 11))::text;
$BODY$
LANGUAGE 'SQL' IMMUTABLE
COST 100;
ALTER FUNCTION pesel_is_valid ( text ) OWNER TO postgres;

Argumentem jest weryfikowany numer pesel.

Wyciągnięcie daty z numeru pesel

-- wyciagniecie daty z numeru pesel
-- @license GPL
-- @author Grzegorz Drozd (http://grzegorzdrozd.pl)
-- @param text numer pesel
-- @return date
-- DROP FUNCTION pesel_get_date(text);
CREATE OR REPLACE FUNCTION pesel_get_date(text) RETURNS date AS
$BODY$
    SELECT
        -- zwrocenie daty
        date (
            -- stulecie
            (CASE
                WHEN 80 < m THEN '18'
                WHEN 60 < m THEN '22'
                WHEN 40 < m THEN '21'
                WHEN 20 < m THEN '20'
                ELSE '19'
            END )::text||
            -- zawsze 2 cyfry
            lpad(y::text, 2, '0')||
            '-'||
            -- wyciagniecie miesiaca
            -- zawsze 2 cyfry
            lpad((CASE
                WHEN 80 < m THEN m-80
                WHEN 60 < m THEN m-60
                WHEN 40 < m THEN m-40
                WHEN 20 < m THEN m-20
                ELSE m
            END )::text, 2, '0')||
            '-'||
            d::text
        )
    FROM (
        SELECT substring($1 FROM 1 FOR 2)::int AS y, substring($1 FROM 3 FOR 2)::int AS m, substring($1 FROM 5 FOR 2)::int AS d
    ) AS elements;
$BODY$
LANGUAGE 'SQL' IMMUTABLE
COST 25;
ALTER FUNCTION pesel_get_date ( text ) OWNER TO postgres;

Argumentem jest numer pesel, wynikiem zmienna typu date.

Generowanie losowego numeru dla podanej daty

-- generowanie liczb losowych w przedziale
-- @param numeric zakres od
-- @param numeric zakres do
-- @return double
-- DROP FUNCTION random(numeric, numeric);
CREATE OR REPLACE FUNCTION random(numeric, numeric)
RETURNS numeric AS
$$
   SELECT ($1 + ($2 - $1) * random())::numeric;
$$ LANGUAGE 'sql' VOLATILE COST 5;
ALTER FUNCTION random(numeric, numeric) OWNER TO postgres;

-- tworzenie numeru pesel z daty
-- @license GPL
-- @author Grzegorz Drozd (http://grzegorzdrozd.pl)
-- @param date data do wygenerowania
-- @param bool flaga informuje czy wygenerowany pesel ma byc NIEPOPRAWNY ( flaga = true)
-- @return string pesel
-- DROP FUNCTION pesel_create(date);
CREATE OR REPLACE FUNCTION pesel_create(date, bool) RETURNS text AS
$BODY$

    -- polaczenie wygenerowanego peselu z cyfra kontrolna + ewentualne dodanie 1 i obciecie do 11 znaków (jezeli generowany pesel ma byc nieprawidlowy
    SELECT substring(pesel||(pesel_generate_check_digit(pesel)+($2::int))::text FROM 1 FOR 11) FROM (
        SELECT
            substring(y::text FROM 3 FOR 2)||
            lpad(m::text, 2,'0')||
            lpad(d::text, 2, '0')||
            lpad((random(1,9999)::int)::text, 4, '0') AS pesel
        FROM (
            SELECT
                y,
                d,
                -- stulecie zwieksza miesiac o wage
                (CASE
                    WHEN y >= 2200 THEN m+60
                    WHEN y >= 2100 THEN m+40
                    WHEN y >= 2000 THEN m+20
                    WHEN y < 1900 THEN m+80
                    ELSE m
                END ) as m
            FROM (
                SELECT extract (year FROM $1) AS y, extract (month FROM $1) AS m, extract (day FROM $1) AS d
            ) AS inner_elements
        ) as elements
    ) as test;
$BODY$
LANGUAGE 'SQL' IMMUTABLE
COST 25;
ALTER FUNCTION pesel_create ( date, bool ) OWNER TO postgres;

Argumentem jest data oraz flaga informująca czy wygenerować nie poprawny numer.

Generowanie n numerów pesel dla dat w przedziale od do

-- generowanie n numerów pesel dla daty od do
-- @license GPL
-- @author Grzegorz Drozd (http://grzegorzdrozd.pl)
-- @param date f data od
-- @param date t data do
-- @param int t liczba rekordów do zwrócenia
-- @return setof pesel
-- DROP FUNCTION pesel_generate_range(date, date, int);
CREATE OR REPLACE FUNCTION pesel_generate_range(in f date, in t date, in n integer) RETURNS setof pesel AS
$BODY$
DECLARE
    -- liczba dni w zakresie dat
    days float;

    -- liczba rekordow na jeden dzien do wygenerowania
    records float;

    -- indeks petli
    i int;

    -- data w petli
    cdate timestamp;

    -- wiersz zwrotny
    r pesel%rowtype;
BEGIN

    -- liczba dni
    if f > t then
        days:= f-t+1;
    else
        days:= t-f+1;
    END IF;

    -- liczba rekordow na dzien, jako float, np: 2.7
    records:= (n/days);

    cdate:= f;

    -- iteracja od 1 do oczekiwanej liczby rekordow
    for i in 1 .. n loop
        -- utworzenie numeru pesel
        r:=ROW(i, pesel_create(cdate::date, false));
        -- dodanie do aktualnej daty kilku godzin, np 24/2.7 ~ 8 godzin, powoduje to równiejszy rozklad rekordów (tylko niektóre dni beda miały mniej rekordów)
        cdate:= cdate + ( 24/records|| 'hours')::interval;

        -- zwrócenie wiersza
        return next r;
    end loop;
end
$BODY$
LANGUAGE 'plpgsql' IMMUTABLE
COST 1125;
ALTER FUNCTION pesel_generate_range ( date, date, integer) OWNER TO postgres;

Argumentem jest zakres dat od do oraz liczba numerów do wygenerowania. Funkcja zwraca zestaw rekordów.

I tu drobna uwaga dotycząca ogólnie generowania czegokolwiek w zakresie dat od do. Zawsze trzeba obliczyć ile dni dzieli obie daty oraz ile rekordów na dzień powinno się wygenerować. W 99% przypadków wyjdzie ułamek – wtedy trzeba do kolejnych dni przez które iterujemy dodawać 24/rekordy_na_dzien w postaci godzin do daty. W innym przypadku  wystąpi zaokrąglenie w dół (nie wygeneruje się wystarczająca liczba danych) lub w górę (wyczerpie się limit danych ale nie zostanie osiągnięta data graniczna).

Indeksy

Wszystkie funkcje można oindeksować (zakładając tabelę o nazwie pesele z kolumną pesel):

-- indeksy na poprawnosc numeru pesel
DROP INDEX  IF EXISTS idx_pesel_is_valid;
CREATE INDEX idx_pesel_is_valid ON pesele (pesel_is_valid(pesel));

-- indeks na pobieranie daty
DROP INDEX IF EXISTS  idx_pesel_pesel_get_date;
CREATE INDEX idx_pesel_pesel_get_date ON pesele ((pesel_get_date(pesel)));

I to na dziś tyle.

18 kwietnia 2011 20:46 Grzegorz Drozd Brak komentarzy Komentuj Kategorie: postgresql, Praca

Bądź uprzejma(y).

Możesz używać następujących tagów HTML tags: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>

Twój adres e-mail nie będzie wyświetlony.

Twój adres e-mail nie będzie przekazany nikomu.

Wszystkie komentarze są moderowane.