--返回0 正确; -1 长度、格式等错误; -2 校验码错误;
CREATE or replace FUNCTION "f_check_sfz"(a_sfz varchar) RETURNS integer
LANGUAGE plpgsql
AS $DECLARE
v_sfz varchar;
v_i integer;
v_sum integer;
v_array1 integer[];
v_array2 varchar[];
v_s varchar;
BEGIN
v_sfz:=upper(trim(a_sfz));
if length(v_sfz)=15 then
if v_sfz ~ '^[123456789]\d{5}\d{2}(0\d|10|11|12)(0\d|1\d|2\d|30|31)\d{3}' then
return 0;
else
return -1;
end if;
end if;
if length(v_sfz)<>18 then
return -1;
end if;
if v_sfz !~ '^[123456789]\d{5}(19|20)\d{2}(0\d|10|11|12)(0\d|1\d|2\d|30|31)\d{3}[\dX]' then
return -1;
end if;
v_array1:=array[7,9,10,5,8,4,2,1,6,3,7,9,10,5,8,4,2];
v_array2:=array['1','0','X','9','8','7','6','5','4','3','2'];
v_i:=1;
v_sum:=0;
loop
v_s:=substr(v_sfz,v_i,1);
if isnumeric(v_s) then
v_sum:=v_sum + cast(v_s as integer)*v_array1[v_i];
else
return -1;
end if;
v_i:=v_i + 1;
if v_i>17 then
exit;
end if;
end loop;
v_sum:=mod(v_sum,11) + 1;
v_s:=v_array2[v_sum];
if v_s=substr(v_sfz,18,1) then
return 0;
else
return -2;
end if;
END
$;
CREATE FUNCTION isnumeric(text) RETURNS boolean
LANGUAGE sql
AS $_$
SELECT $1 ~ '^[0-9]+
$_$;