去O （oracle转postgresql）---function改造

oralce与postgresql在语法上虽大部分相同，但仍存在语法差异

本文记录一次15位身份证转18位方法由oracle转换至pgsql

先上原Oracle方法

CREATE OR REPLACE Function IDCARD15TO18(CARD Varchar2) Return Varchar2 Is
    Type TIARRAY Is Table Of Integer;
    Type TCARRAY Is Table Of Char(1);
    RESULTS Varchar2(18);
    W       TIARRAY; --数字数组
    A       TCARRAY; --字符数组
    S       Integer;
Begin
    If CARD Is Null Then
        Return '';
    End If;
    If LENGTH(CARD) <> 15 Then
        Return CARD;
    End If;
    W       := TIARRAY(7, 9, 10, 5, 8, 4, 2, 1, 6, 3, 7, 9, 10, 5, 8, 4, 2, 1);
    A       := TCARRAY('1', '0', 'X', '9', '8', '7', '6', '5', '4', '3', '2');
    RESULTS := SUBSTR(CARD, 1, 6) || '19' || SUBSTR(CARD, 7, 9);
    S       := 0;
    Begin
        For I In 1 .. 17 Loop
            S := S + To_Number(SUBSTR(RESULTS, I, 1)) * W(I);
        End Loop;
    Exception
        When Others Then
            Return '';
    End;
    S       := S Mod 11;
    RESULTS := RESULTS || A(S + 1);
    Return(RESULTS);
End IDCARD15TO18;

转换后：

CREATE OR REPLACE FUNCTION idcard15to18 (CARD text) RETURNS varchar AS $body$
DECLARE
    RESULTS varchar(18);
    W       int[]; --数字数组
    A       text[]; --字符数组
    S       numeric;
BEGIN
    If coalesce(CARD::text, '') = '' Then
        Return '';
    End If;
    If LENGTH(CARD) <> 15 Then
        Return CARD;
    End If;
    W       := '{7, 9, 10, 5, 8, 4, 2, 1, 6, 3, 7, 9, 10, 5, 8, 4, 2, 1}';
    A       := '{"1", "0", "X", "9", "8", "7", "6", "5", "4", "3", "2"}';
    RESULTS := SUBSTR(CARD, 1, 6) || '19' || SUBSTR(CARD, 7, 9);
    S       := 0;
    Begin
        for I In 1 .. 17 Loop
            S := S + (SUBSTR(RESULTS, I, 1))::numeric  * W[I];
        End Loop;
    Exception
        When Others Then
            Return '';
    End;
    S       := S % 11;
    RESULTS := RESULTS || A[S + 1];
    Return(RESULTS);
End;
$body$
LANGUAGE PLPGSQL
;

涉及以下几点

1.pgsql支持数组：
如int[]，text[]等
2.对数组赋值方式
‘{7, 9, 10, 5, 8, 4, 2, 1, 6, 3, 7, 9, 10, 5, 8, 4, 2, 1}’
3.支持%取余
4.数组取值
由int(i) --> int[i]