PESEL validation PostgreSQL procedure
If you need PESEL validation procedure for PostgreSQL, here it is. I’ll appreciate saying thank you in comment if it worked for you and saved your time.
create or replace function chkpesel (anyelement)
returns integer as $$
-- based on:
-- http://tinyurl.com/awsml
-- http://tinyurl.com/a3mqk
declare
-- PESEL of freely chosen data type
pesel alias for $1;
-- cheksum
cs integer default 0;
-- PESEL of varchar type
p varchar;
-- multiplying factor
m integer default 0;
i integer;
begin
p := cast(pesel as varchar);
-- a litle bit wird check if p matches \D{11}
if char_length(substring(p from '^[0-9]{11}$')) = 11 then
for i in 1..10 loop
if i in (1,5,9) then
m := 1;
elsif i in (2,6,10) then
m := 3;
elsif i in (3,7) then
m := 7;
elsif i in (4,8) then
m := 9;
end if;
cs := cs + m*cast(substr(p,i,1)
as integer);
end loop;
-- modulo 10 (reminder from dividing by 10)
cs := cs % 10;
-- if cheksum 1-9 return 10-checksum,
-- if cheksum 0 return 0
if cs > 0 then
cs := 10 - cs;
end if;
if cs = cast(substr(p,11,1) as integer) then
-- ok
return 1;
else
-- cheksum error
return 0;
end if;
else
-- given string doesn't contain 11 digits
return -1;
end if;
end;
$$ LANGUAGE plpgsql;
Additionally, I created convenient wrapper function ispesel, that I use instead of chkpesel to determine if valid PESEL has been passed in SQL query.
create or replace function ispesel (anyelement)
returns boolean as $$
-- just wrapper, look for chkpesel()
declare
-- PESEL of freely chosen data type
pesel alias for $1;
begin
if chkpesel(pesel) = 1 then
return true;
else
return false;
end if;
end;
$$ LANGUAGE plpgsql;
Examples of use
Let’s assume you’ve got table students with column pesel.
Now, let’s try with simple SELECT…
db=> select (
(select count(*) from students where ispesel(pesel))
/ (select count(*) from students)::real*100)::numeric(4,2)
||'%' as "valid pesel [%]";
valid pesel [%]
-----------------
95.14%
(1 row)
…or nice CHECK assuring that INSERT or UPDATE with invalid PESEL will fail.
db=> alter table students
add constraint valid_pesel check(ispesel(pesel));
ALTER TABLE
Enjoy.
