PG车辆VIN码校验

函数使用方法：

select lcipcheckcarvin(‘VIN码’);

直接执行下面脚本，创建函数:

CREATE OR REPLACE FUNCTION lcipcheckcarvin(carvin text)

  RETURNS integer AS

$BODY$

declare n integer;

declare codes char[];

declare code char;

declare kv integer;

declare wv integer;

declare incode integer;

declare nine text;

declare total integer;

begin     

if(carvin is null or length(carvin)=0 or length(carvin)<>17) then

return 0;

end if;

carvin:=lower(carvin);

codes:=array[]::char[];

FOR n IN 1..17 loop

codes:=array_append(codes,substring(carvin,n,1)::character);

end loop;

nine:=substring(carvin,9,1);

if(nine ~ '[0-9]') then

incode:= nine::integer;

elseif('x'=nine) then

incode:= 10;

else

return 0;

end if;

total:=0;

code:='';

FOR n IN 1..array_length(codes,1) loop

code:=codes[n];

if(code='0' or code='1' or code='2' or code='3' or code='4' or code='5' or code='6' or code='7' or code='8' or code='9' or code='a' or code='b' or code='c' or code='d' or code='e' or code='f' or code='g' or code='h' or code='j' or code='k' or code='l' or code='m' or code='n' or code='p' or code='q' or code='r' or code='s' or code='t' or code='u' or code='v' or code='w' or code='x' or code='y' or code='z' ) then

if(n <> 9) then

if(code = '0') then

kv:=0;

elseif(code = '1') then

kv:=1;

elseif(code = '2') then

kv:=2;

elseif(code = '3') then

kv:=3;

elseif(code = '4') then

kv:=4;

elseif(code = '5') then

kv:=5;

elseif(code = '6') then

kv:=6;

elseif(code = '7') then

kv:=7;

elseif(code = '8') then

kv:=8;

elseif(code = '9') then

kv:=9;

elseif(code = 'a') then

kv:=1;

elseif(code = 'b') then

kv:=2;

elseif(code = 'c') then

kv:=3;

elseif(code = 'd') then

kv:=4;

elseif(code = 'e') then

kv:=5;

elseif(code = 'f') then

kv:=6;

elseif(code = 'g') then

kv:=7;

elseif(code = 'h') then

kv:=8;

elseif(code = 'j') then

kv:=1;

elseif(code = 'k') then

kv:=2;

elseif(code = 'l') then

kv:=3;

elseif(code = 'm') then

kv:=4;

elseif(code = 'n') then

kv:=5;

elseif(code = 'p') then

kv:=7;

elseif(code = 'q') then

kv:=8;

elseif(code = 'r') then

kv:=9;

elseif(code = 's') then

kv:=2;

elseif(code = 't') then

kv:=3;

elseif(code = 'u') then

kv:=4;

elseif(code = 'v') then

kv:=5;

elseif(code = 'w') then

kv:=6;

elseif(code = 'x') then

kv:=7;

elseif(code = 'y') then

kv:=8;  

elseif(code = 'z') then

kv:=9;    

end if;

if(n = 1) then

wv:=8;

elseif(n = 2) then

wv:=7;

elseif(n = 3) then

wv:=6;

elseif(n = 4) then

wv:=5;

elseif(n = 5) then

wv:=4;

elseif(n = 6) then

wv:=3;

elseif(n = 7) then

wv:=2;

elseif(n = 8) then

wv:=10;

elseif(n = 10) then

wv:=9;

elseif(n = 11) then

wv:=8;

elseif(n = 12) then

wv:=7;

elseif(n = 13) then

wv:=6;

elseif(n = 14) then

wv:=5;

elseif(n = 15) then

wv:=4;

elseif(n = 16) then

wv:=3;

elseif(n = 17) then

wv:=2;

end if;

total:= total + (kv * wv);

end if;

end if;

end loop;

if(incode = total%11) then

return 1;

else

return 0;

end if;

end;

$BODY$

  LANGUAGE plpgsql VOLATILE;