주민등록번호 정합성 체크 FUNCTION

REATE OR REPLACE FUNCTION public.jumin_valid_chk(in_jumin_no character varying)
 RETURNS character varying
 LANGUAGE plpgsql
AS $function$
DECLARE      
    WK_JUMIN_NO VARCHAR(13); 
    WK_CHECK_BIT VARCHAR(12) := '234567892345'; --검증값  
    WK_TOTAL INTEGER := 0;
    WK_RESULT INTEGER := 0;
    WK_RETURN VARCHAR(1) := 'N'; 
BEGIN
    WK_JUMIN_NO := REPLACE(TRIM(IN_JUMIN_NO), '-', '');
     
    FOR i IN 1..12
    LOOP
       WK_TOTAL := WK_TOTAL 
                 + CAST(SUBSTRING(WK_JUMIN_NO FROM i FOR 1) AS INTEGER)
                 * CAST(SUBSTRING(WK_CHECK_BIT FROM i FOR 1) AS INTEGER);
    END LOOP;
     
    IF CAST(SUBSTRING(WK_JUMIN_NO FROM 7 FOR 1) AS INTEGER) IN (5, 6, 7, 8) THEN
        --외국인
        WK_RESULT := MOD(13 - (MOD(WK_TOTAL, 11)), 10);
    ELSE
        --내국인
        WK_RESULT := MOD(11 - (MOD(WK_TOTAL, 11)), 10);
    END IF;
     
    IF CAST(SUBSTRING(WK_JUMIN_NO FROM 13 FOR 1) AS INTEGER) = WK_RESULT THEN       
        WK_RETURN := 'Y';
    ELSE
        WK_RETURN := 'N';
    END IF;   
  
    RETURN WK_RETURN;
END;
$function$
;