[PostgreSQL] 문자열 마스킹 쿼리

-- ======================================================================
-- SP 이름: 문자열 마스킹(F_MaskingText)
-- ======================================================================
DROP FUNCTION IF EXISTS F_MaskingText(text, text, text, text) CASCADE;

CREATE OR REPLACE FUNCTION F_MaskingText(
    i_pattern  text
  , i_txt      text
  , i_conv_txt text
  , i_option   text
) RETURNS character varying AS
$BODY$
DECLARE
    v_pattern         text := UPPER(i_pattern);
    v_conv_txt        text := COALESCE(NULLIF(i_conv_txt, ''), '*');
    v_result_conv_txt text;
    v_result_txt      text;
    v_reg_exp         text;
    v_ip_a_class      text := null;
    v_ip_b_class      text := null;
    v_ip_c_class      text := null;
    v_ip_d_class      text := null;
BEGIN
    --첫 번째 문자 이후 나머지 모든 문자열 마스킹
    IF 'FROM_THE_SECOND' = v_pattern THEN
        v_reg_exp := '(?<=.{1}).';
        v_result_conv_txt := v_conv_txt;
    --i_option 번째 문자 이후 나머지 모든 문자열 마스킹
    ELSIF 'FROM_THE_COUNT' = v_pattern THEN
        v_reg_exp := '(?<=.{'|| i_option || '}).';
        v_result_conv_txt := v_conv_txt;
    --휴대전화 마스킹
    ELSIF 'PHONE_NUMBER' = v_pattern THEN
        v_reg_exp := '^(\d{3})-?(\d{1,2})\d{2}-?\d(\d{3})$';
        v_result_conv_txt := '\1-\2' || v_conv_txt || v_conv_txt || '-' || v_conv_txt || '\3';
        --v_result_conv_txt := '\1-\2**-*\3';
    --전화번호 마스킹
    ELSIF 'TEL_NUMBER' = v_pattern THEN
        v_reg_exp := '^(\d{2,3})-?(\d{1,2})\d{2}-?\d(\d{3})$';
        v_result_conv_txt := '\1-\2' || v_conv_txt || v_conv_txt || '-' || v_conv_txt || '\3';
        --v_result_conv_txt := '\1-\2**-*\3';
    --이메일 마스킹
    ELSIF 'EMAIL' = v_pattern THEN
        v_reg_exp := '^(.)(.+)@(.+)$';
        v_result_conv_txt := '\1' || v_conv_txt || v_conv_txt || v_conv_txt || '@\3';
        --v_result_conv_txt := '\1***@\3';
    --주민등록번호 마스킹
    ELSIF 'JUMIN_NUM' = v_pattern THEN
        v_reg_exp := '^(\d{6})\-([1-4])(\d{6})$';
        v_result_conv_txt :='\1-\2' || v_conv_txt || v_conv_txt || v_conv_txt || v_conv_txt || v_conv_txt || v_conv_txt;
        --v_result_conv_txt :='\1-\2******';
    --IP 마스킹(기본 C클래스 마스킹, i_option에 포함된 문자(A,B,C,D) 에 따라 해당 클래스 마스킹 가능)
    --A : A클래스
    --AC : A, C클래스
    --CA : A, C클래스
    --ABC : A, B, C클래스
    --DCAB : A, B, C, D클래스
    ELSIF 'IP' = v_pattern THEN
        v_reg_exp := '^([0-9]{1,3})\.([0-9]{1,3})\.([0-9]{1,3})\.([0-9]{1,3})$';
        IF 0 = POSITION('A' IN i_option)
        AND 0 = POSITION('B' IN i_option)
        AND 0 = POSITION('C' IN i_option)
        AND 0 = POSITION('D' IN i_option) THEN
            v_result_conv_txt := '\1.\2.'|| v_conv_txt || v_conv_txt || v_conv_txt || '.\4';
            --v_result_conv_txt := '\1.\2.***.\4';
        ELSE
            IF 0 < POSITION('A' IN i_option) THEN
                v_ip_a_class := v_conv_txt || v_conv_txt || v_conv_txt;
            END IF;
            IF 0 < POSITION('B' IN i_option) THEN
                v_ip_b_class := v_conv_txt || v_conv_txt || v_conv_txt;
            END IF;
            IF 0 < POSITION('C' IN i_option) THEN
                v_ip_c_class := v_conv_txt || v_conv_txt || v_conv_txt;
            END IF;
            IF 0 < POSITION('D' IN i_option) THEN
                v_ip_d_class := v_conv_txt || v_conv_txt || v_conv_txt;
            END IF;

            v_result_conv_txt := COALESCE(v_ip_a_class, '\1') || '.' ||
                                 COALESCE(v_ip_b_class, '\2') || '.' ||
                                 COALESCE(v_ip_c_class, '\3') || '.' ||
                                 COALESCE(v_ip_d_class, '\4');
        END IF;

    ELSE
        v_result_txt := 'Invalid pattern : ' || i_pattern;
        RETURN v_result_txt;
    END IF;

    SELECT REGEXP_REPLACE(i_txt, v_reg_exp, v_result_conv_txt, 'g')
      INTO v_result_txt
         ;

    RETURN v_result_txt;

END;

$BODY$
LANGUAGE plpgsql;
----------------------------------------------------------------------------------------------------
-- TEST
--SELECT F_MaskingText('FROM_THE_SECOND', '마스킹테스트문자열', '', '');       -- "마********"
--SELECT F_MaskingText('FROM_THE_SECOND', '마스킹테스트문자열', '@', '');      -- "마@@@@@@@@"
--SELECT F_MaskingText('FROM_THE_COUNT', '마스킹테스트문자열', '', '3');       -- "마스킹******"
--SELECT F_MaskingText('FROM_THE_COUNT', '마스킹테스트문자열', '@', '4');      -- "마스킹테@@@@@"
--SELECT F_MaskingText('PHONE_NUMBER', '010-1234-5678', '', '');            -- "010-12**-*678"
--SELECT F_MaskingText('PHONE_NUMBER', '010-1234-5678', '@', '');           -- "010-12@@-@678"
--SELECT F_MaskingText('TEL_NUMBER', '02-234-5678', '', '');                -- "02-2**-*678"
--SELECT F_MaskingText('TEL_NUMBER', '02-234-5678', '@', '');               -- "02-2@@-@678"
--SELECT F_MaskingText('TEL_NUMBER', '031-234-5678', '', '');               -- "031-2**-*678"
--SELECT F_MaskingText('TEL_NUMBER', '031-234-5678', '@', '');              -- "031-2@@-@678"
--SELECT F_MaskingText('EMAIL', 'admin@test.co.kr', '', '');                -- "a***@test.co.kr"
--SELECT F_MaskingText('EMAIL', 'admin@test.co.kr', '&', '');               -- "a&&&@test.co.kr"
--SELECT F_MaskingText('JUMIN_NUM', '010101-3456789', '', '');              -- "010101-3******"
--SELECT F_MaskingText('JUMIN_NUM', '010101-4567890', '@', '');             -- "010101-4@@@@@@"
--SELECT F_MaskingText('IP', '1.1.1.1', '', '');                            -- "1.1.***.1"
--SELECT F_MaskingText('IP', '1.1.1.1', '@', 'A');                          -- "@@@.1.1.1"
--SELECT F_MaskingText('IP', '1.1.1.1', '^', 'AC');                         -- "^^^.1.^^^.1"
--SELECT F_MaskingText('IP', '1.1.1.1', '^', 'DA');                         -- "^^^.1.1.^^^"