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 Brak komentarzy Komentuj Kategorie: postgresql, Praca
Brak komentarzy