PosgreSQL函数验证身份证

我司一个员工需要一个验证身份证的函数，他自己写了一个MySQL版本的函数验证，因为他对PosgreSQL函数不熟悉，所以就让我这边给他写一个一样功能的PosgreSQL版本给他，其实大部分函数是类似的，仿写一样也是简单的。

MySQL版本

CREATE  FUNCTION `checksfz`(yhsfz VARCHAR(40)) RETURNS varchar(30) CHARSET utf8
    DETERMINISTIC
BEGIN
    
    DECLARE
        result VARCHAR(30);

    set result =
            case
        -- 判断身份证号码是否为18位
        when (length(yhsfz)) <>18 then '身份证号码必须为18位'
        -- 判断身份证号码前17位是否含除数字外的字符
        when (substring(yhsfz,1,17) REGEXP '[^0-9]')  = 1 then '身份证号码前17位格式不正确'
        -- 判断身份证的年份是否在合理范围内
        when substring(yhsfz,7,4) not between '1900' and YEAR(NOW()) then '身份证年份错误'
        -- 判断身份证的月份是否在合理范围内
        when substring(yhsfz,11,2) not between '01' and '12' then '身份证月份错误'
        -- 判断身份证日期是否在合理范围内
        when substring(yhsfz,13,2) not between 1 and day(DATE_SUB(date_add(str_to_date(CONCAT(substring(yhsfz,7,4),'-',substring(yhsfz,11,2),'-01'),'%Y-%m-%d'), interval 1 MONTH) ,INTERVAL 1 DAY  ))   then '身份证日期错误'
        -- 判断身份证号码的第18位是否符合验证规则
        when
          mod(( 
            substring(yhsfz,1,1)*7+ 
            substring(yhsfz,2,1)*9+ 
            substring(yhsfz,3,1)*10+ 
            substring(yhsfz,4,1)*5+ 
            substring(yhsfz,5,1)*8+ 
            substring(yhsfz,6,1)*4+ 
            substring(yhsfz,7,1)*2+ 
            substring(yhsfz,8,1)*1+ 
            substring(yhsfz,9,1)*6+ 
            substring(yhsfz,10,1)*3+ 
            substring(yhsfz,11,1)*7+ 
            substring(yhsfz,12,1)*9+ 
            substring(yhsfz,13,1)*10+ 
            substring(yhsfz,14,1)*5+ 
            substring(yhsfz,15,1)*8+ 
            substring(yhsfz,16,1)*4+ 
            substring(yhsfz,17,1)*2 
          ),11) 
          <> 
          ( 
          case
            when substring(yhsfz,18,1)='1' then '0'
            when substring(yhsfz,18,1)='0' then '1'
            when substring(yhsfz,18,1) in ('X','x') then '2'
            when substring(yhsfz,18,1)='9' then '3'
            when substring(yhsfz,18,1)='8' then '4'
            when substring(yhsfz,18,1)='7' then '5'
            when substring(yhsfz,18,1)='6' then '6'
            when substring(yhsfz,18,1)='5' then '7'
            when substring(yhsfz,18,1)='4' then '8'
            when substring(yhsfz,18,1)='3' then '9'
            when substring(yhsfz,18,1)='2' then '10'
          end
          ) 
        then '身份证验证错误'
        else 'ok'
      end;
    return result;
end
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61

通过仿写实现一样功能的版本

PosgreSQL版本

CREATE OR REPLACE FUNCTION CHECK_ID_CARD("sfzh" varchar)
  RETURNS varchar AS $body$  
DECLARE
    RESULTS varchar(30);
BEGIN
	RESULTS := CASE
	WHEN sfzh IS NULL THEN
		'身份证号不能为空'
	WHEN LENGTH(sfzh) <> 18 THEN
		'身份证号码必须为18位'
	WHEN regexp_match(substring(sfzh,1,17), '[^0-9]') IS NOT NULL THEN
	  '身份证号码前17位格式不正确'
	WHEN substring(sfzh,7,4) NOT BETWEEN '1900' AND to_char(now(),'YYYY') THEN
		'身份证年份错误'
	WHEN substring(sfzh,11,2) NOT BETWEEN '01' AND '12' THEN 
		'身份证月份错误'
	WHEN substring(sfzh,13,2) NOT BETWEEN '01' AND to_char((date_trunc('month',to_date
	(CONCAT(substring(sfzh,7,4),'-',substring(sfzh,11,2),'-01'),'YYYY-MM-DD')) + interval '1 month' - interval '1 day'),'dd') THEN 
		'身份证日期错误'
	WHEN 
		mod(( 
            to_number(substring(sfzh,1,1),'9')*7+ 
            to_number(substring(sfzh,2,1),'9')*9+ 
            to_number(substring(sfzh,3,1),'9')*10+ 
            to_number(substring(sfzh,4,1),'9')*5+ 
            to_number(substring(sfzh,5,1),'9')*8+ 
            to_number(substring(sfzh,6,1),'9')*4+ 
            to_number(substring(sfzh,7,1),'9')*2+ 
            to_number(substring(sfzh,8,1),'9')*1+ 
            to_number(substring(sfzh,9,1),'9')*6+ 
            to_number(substring(sfzh,10,1),'9')*3+ 
            to_number(substring(sfzh,11,1),'9')*7+ 
            to_number(substring(sfzh,12,1),'9')*9+ 
            to_number(substring(sfzh,13,1),'9')*10+ 
            to_number(substring(sfzh,14,1),'9')*5+ 
            to_number(substring(sfzh,15,1),'9')*8+ 
            to_number(substring(sfzh,16,1),'9')*4+ 
            to_number(substring(sfzh,17,1),'9')*2 
          ),11) 
			<> to_number(
          ( 
          case
            when substring(sfzh,18,1)='1' then '0'
            when substring(sfzh,18,1)='0' then '1'
            when substring(sfzh,18,1) in ('X','x') then '2'
            when substring(sfzh,18,1)='9' then '3'
            when substring(sfzh,18,1)='8' then '4'
            when substring(sfzh,18,1)='7' then '5'
            when substring(sfzh,18,1)='6' then '6'
            when substring(sfzh,18,1)='5' then '7'
            when substring(sfzh,18,1)='4' then '8'
            when substring(sfzh,18,1)='3' then '9'
            when substring(sfzh,18,1)='2' then '10'
          end
          ),'99')
	THEN
		'身份证验证错误'
	ELSE
		'ok'
	END;
	Return(RESULTS);
END
$body$ LANGUAGE plpgsql;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63

后续我想在Greenplum使用该函数，发现regexp_match这个函数竟然找不到，只能稍微修改一下

WHEN (substring(sfzh,1,17) ~ '[^0-9]' ) = 't' THEN
	  '身份证号码前17位格式不正确'
1
2

基本能实现一样的功能了。