greenplum数据库写了个自定义函数 请问我该如果把异常加入里面？
CREATE OR REPLACE FUNCTION S_FUNC_IDNUM(IN_IDNUM VARCHAR(18),IN_fifteenflag varchar(1))
RETURNS varchar(18)
LANGUAGE plpgsql
VOLATILE
AS $$
------------------------------------------------------------------------
-- 参数：IN_IDNUM(输入18位身份证号码) INfifteenflag（输入Y或N）
-- 功能：输入18位身份证号码，1位Y或别的单个字符，进行验证，如果验证失败则返回N
-- 例如：S_FUNC_IDNUM('412832197901086418','Y')
------------------------------------------------------------------------
DECLARE
--定义中间参量
sqlcode INTEGER default 0;
i18 varchar(1) default '';--
n int default 0;--
l int default 0;
v_return varchar(18) default 'N';--
v_idnum varchar(18) default null;
v_i varchar(1) default 'Y';
BEGIN
v_idnum:=rtrim(IN_IDNUM);
--算法
l:=length(IN_IDNUM);--
if (IN_fifteenflag is null or IN_fifteenflag ='') then
v_i := 'Y';
else
v_i := IN_fifteenflag;
end if ;
if l=18 then
--验证前17位是否全部为数字
if ltrim(rtrim(translate(SUBSTRING(IN_IDNUM,1,17),'0123456789',''))) <> '' then
return 'N';--
end if;--
--验证年份格式
if SUBSTRING(IN_IDNUM,7,4) NOT BETWEEN '1001' AND '9999' then
return 'N';--
end if;--
--验证月份格式
if(SUBSTRING(IN_IDNUM,11,2) NOT BETWEEN '01' AND '12') then
return 'N';--
end if;--
--验证日期格式
if cast(SUBSTRING(IN_IDNUM,13,2) as integer) not BETWEEN 1
AND date_part('day',(substr(IN_IDNUM,7,4)||'-'||substr(IN_IDNUM,11,2)||'-'||'01') :: date +interval '1 mon' + interval '-1 day')
then
return 'N';--
end if;
--验证省份代码是否正确
if(position('.'||left(IN_IDNUM,2)||'.' in '.11.12.13.14.15.21.22.23.31.32.33.34.35.36.37.41.42.43.44.45.46.50.51.52.53.54.61.62.63.64.65.71.81.82.91.')=0) then
return 'N';--
end if;--
--验证校验位是否正确
n:=cast(substr(IN_IDNUM,1,1) as integer)*7+cast(substr(IN_IDNUM,2,1)as integer)*9+cast(substr(IN_IDNUM,3,1)as integer)*10+cast(substr(IN_IDNUM,4,1) as integer)*5+cast(substr(IN_IDNUM,5,1)as integer)*8+ cast(substr(IN_IDNUM,6,1)as integer)*4;--
n:=n+cast(substr(IN_IDNUM,7,1)as integer)*2+cast(substr(IN_IDNUM,8,1)as integer)*1+cast(substr(IN_IDNUM,9,1)as integer)*6+cast(substr(IN_IDNUM,10,1)as integer)*3+cast(substr(IN_IDNUM,11,1)as integer)*7+ cast(substr(IN_IDNUM,12,1)as integer)*9+cast(substr(IN_IDNUM,13,1)as integer)*10+cast(substr(IN_IDNUM,14,1)as integer)*5;--
n:=n+cast(substr(IN_IDNUM,15,1)as integer)*8+cast(substr(IN_IDNUM,16,1)as integer)*4+cast(substr(IN_IDNUM,17,1)as integer)*2;--
n:=mod(n,11);--
if n=0 then i18:='1';--
elseif n=1 then i18:='0';--
elseif n=2 then i18:='X';--
elseif n=3 then i18:='9';--
elseif n=4 then i18:='8';--
elseif n=5 then i18:='7';--
elseif n=6 then i18:='6';--
elseif n=7 then i18:='5';--
elseif n=8 then i18:='4';--
elseif n=9 then i18:='3';--
elseif n=10 then i18:='2';--
end if;--
if i18 = upper(substr(IN_IDNUM,18,1)) then
v_return := v_idnum;
else --
v_return := 'N';
end if;
elseif l=15 then
--验证前15位是否为数字
if ltrim(rtrim(translate(SUBSTRING(IN_IDNUM,1,15),'0123456789',''))) <> '' then
return 'N';--
end if;--
--验证月份格式
if(SUBSTRING(IN_IDNUM,9,2) NOT BETWEEN '01' AND '12') then
return 'N';--
end if;--
--验证日期格式
if cast(SUBSTRING(IN_IDNUM,11,2) as integer) not BETWEEN 1
AND date_part('day',(substr(IN_IDNUM,7,4)||'-'||substr(IN_IDNUM,11,2)||'-'||'01') :: date +interval '1 mon' + interval '-1 day')
then
return 'N';--
end if;--
--验证省份代码是否正确
if(position('.'||left(IN_IDNUM,2)||'.' in '.11.12.13.14.15.21.22.23.31.32.33.34.35.36.37.41.42.43.44.45.46.50.51.52.53.54.61.62.63.64.65.71.81.82.91.')=0) then
return 'N';--
end if;--
--获取验证码
n:=cast(substr(IN_IDNUM,1,1) as integer)*7+cast(substr(IN_IDNUM,2,1)as integer)*9+cast(substr(IN_IDNUM,3,1)as integer)*10+cast(substr(IN_IDNUM,4,1)as integer)*5+cast(substr(IN_IDNUM,5,1)as integer)*8+cast(substr(IN_IDNUM,6,1)as integer)*4;--
n:=n+1*2+9*1+cast(substr(IN_IDNUM,7,1)as integer)*6+cast(substr(IN_IDNUM,8,1)as integer)*3+cast(substr(IN_IDNUM,9,1)as integer)*7+cast(substr(IN_IDNUM,10,1)as integer)*9+cast(substr(IN_IDNUM,11,1)as integer)*10+cast(substr(IN_IDNUM,12,1)as integer)*5;--
n:=n+cast(substr(IN_IDNUM,13,1)as integer)*8+cast(substr(IN_IDNUM,14,1)as integer)*4+cast(substr(IN_IDNUM,15,1)as integer)*2;--
n:=mod(n,11);--
--生成18位证件号码
if n=0 then return substr(IN_IDNUM,1,6)||'19'||substr(IN_IDNUM,7)||'1';--
elseif n=1 then return substr(IN_IDNUM,1,6)||'19'||substr(IN_IDNUM,7)||'0';--
elseif n=2 then return substr(IN_IDNUM,1,6)||'19'||substr(IN_IDNUM,7)||'X';--
elseif n=3 then return substr(IN_IDNUM,1,6)||'19'||substr(IN_IDNUM,7)||'9';--
elseif n=4 then return substr(IN_IDNUM,1,6)||'19'||substr(IN_IDNUM,7)||'8';--
elseif n=5 then return substr(IN_IDNUM,1,6)||'19'||substr(IN_IDNUM,7)||'7';--
elseif n=6 then return substr(IN_IDNUM,1,6)||'19'||substr(IN_IDNUM,7)||'6';--
elseif n=7 then return substr(IN_IDNUM,1,6)||'19'||substr(IN_IDNUM,7)||'5';--
elseif n=8 then return substr(IN_IDNUM,1,6)||'19'||substr(IN_IDNUM,7)||'4';--
elseif n=9 then return substr(IN_IDNUM,1,6)||'19'||substr(IN_IDNUM,7)||'3';--
elseif n=10 then return substr(IN_IDNUM,1,6)||'19'||substr(IN_IDNUM,7)||'2';--
end if;
return IN_IDNUM;
else
v_return:= 'N';
end if;--
return v_return;
end;
$$