pgSql验证18位身份证是否合格

 在表添加触发器，校验18位身份证号是否正确

CREATE OR REPLACE FUNCTION "public"."check_idcard"("a_sfz" varchar)
  RETURNS "pg_catalog"."varchar" AS $BODY$DECLARE
v_sfz  varchar;
v_i  integer;
v_sum  integer;
v_array1 integer[];
v_array2 varchar[];
v_s varchar;
v_county_code varchar[];
v_sf varchar;
v_day integer;
BEGIN
    v_sfz:=upper(trim(a_sfz));
    if length(v_sfz)=18 then
    --前2位省份是否正确
    v_county_code:=array['11','22','35','44','53','12','23','36','45','54','13','31','37','46','61','14','32','41','50','62','15','33','42','51','63','21','34','43','52','64','65','71','81','82'];
        v_sf:=left(v_sfz,2);
        v_i:=1;
        loop
          IF v_county_code[v_i]=v_sf THEN
                exit;
            END IF;
            v_i:=v_i + 1;
            if v_i>34 then
                return '身份证不正确，请检查';
            end if;
        end loop;
        ---年份是否正确
        if substr(v_sfz,7,4) not between '1900' and '2014' then 
            return'身份证年份错误';
        END IF;
        ---月份是否正确
        if substr(v_sfz,11,2) not between '01' and '12' then 
            return '身份证月份错误';
        END IF;
        --日期是否正确
        v_day=date_part('days',(to_date((substr(v_sfz,7,4)||'-'||substr(v_sfz,11,2)||'-01'),'yyyy-MM-dd')+ INTERVAL '1 month'-INTERVAL '1 day'));
        if cast(substr(v_sfz,13,2) as int) not between 1 and v_day then
            return '身份证日期错误';
        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);
            v_sum:=v_sum + cast(v_s as integer)*v_array1[v_i];
            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 1;
        else
            return '身份证不正确';
        end if;
    else
            return -1;
    end if;
END
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100