encryptsql/mask_funcs.sql

/* =========================================================
   通用脱敏函数集合
   created by: lyl
   date: 2025-07-24
   说明：所有函数统一返回 text，NULL 输入返回 NULL
========================================================= */

-- 1) 缺省规则：保留前 a 位和后 b 位(默认脱敏方式)
CREATE OR REPLACE FUNCTION mask(val text, a int, b int)
RETURNS text
LANGUAGE sql IMMUTABLE STRICT AS
$$
    SELECT CASE
             WHEN val IS NULL THEN NULL
             WHEN length(val) <= a + b THEN val
             ELSE substr(val, 1, a) ||
                  rpad('*', length(val) - a - b, '*') ||
                  substr(val, length(val) - b + 1, b)
           END;
$$;

-- 2) 姓名：中文保留最后一个字，英文仅保留首字母
CREATE OR REPLACE FUNCTION mask_name(full_name text)
RETURNS text
LANGUAGE plpgsql IMMUTABLE STRICT AS
$$
DECLARE
    len int;
    last_char text;
BEGIN
    IF full_name IS NULL THEN RETURN NULL; END IF;
    len := length(full_name);
    IF len = 0 THEN RETURN ''; END IF;

    -- 判断是否为纯 ASCII，简单认为只要有一个非 ASCII 就是中文
    IF full_name ~ '[^\x00-\x7F]' THEN
        -- 中文规则：保留最后一个字
        last_char := substr(full_name, len, 1);
        RETURN rpad('*', len-1, '*') || last_char;
    ELSE
        -- 英文规则：保留首字母
        RETURN substr(full_name, 1, 1) || rpad('*', len-1, '*');
    END IF;
END;
$$;

-- 3) 昵称：保留第一个字和最后一个字
CREATE OR REPLACE FUNCTION mask_nick(nick text)
RETURNS text
LANGUAGE sql IMMUTABLE STRICT AS
$$
    SELECT CASE
             WHEN nick IS NULL THEN NULL
             WHEN length(nick) <= 2 THEN nick
             ELSE substr(nick, 1, 1) ||
                  rpad('*', length(nick)-2, '*') ||
                  substr(nick, length(nick), 1)
           END;
$$;

-- 4) 邮箱：仅保留 @ 及之后内容
CREATE OR REPLACE FUNCTION mask_email(mail text)
RETURNS text
LANGUAGE sql IMMUTABLE STRICT AS
$$
    SELECT CASE
             WHEN mail IS NULL THEN NULL
             WHEN position('@' in mail) = 0 THEN rpad('*', length(mail), '*')
             ELSE rpad('*', position('@' in mail) - 1, '*') ||
                  substr(mail, position('@' in mail))
           END;
$$;

-- 5) 手机号：保留前三位 + 后两位
CREATE OR REPLACE FUNCTION mask_mobile(phone text)
RETURNS text
LANGUAGE sql IMMUTABLE STRICT AS
$$
    SELECT CASE
             WHEN phone IS NULL THEN NULL
             WHEN length(phone) < 6 THEN phone
             ELSE substr(phone, 1, 3) ||
                  rpad('*', length(phone)-5, '*') ||
                  substr(phone, length(phone)-1, 2)
           END;
$$;

-- 6) 固定电话：仅保留后两位
CREATE OR REPLACE FUNCTION mask_tel(tel text)
RETURNS text
LANGUAGE sql IMMUTABLE STRICT AS
$$
    SELECT CASE
             WHEN tel IS NULL THEN NULL
             WHEN length(tel) < 2 THEN tel
             ELSE rpad('*', length(tel)-2, '*') || substr(tel, length(tel)-1, 2)
           END;
$$;

-- 7) 证件号码：保留第一位和最后一位
CREATE OR REPLACE FUNCTION mask_idcard(id text)
RETURNS text
LANGUAGE sql IMMUTABLE STRICT AS
$$
    SELECT CASE
             WHEN id IS NULL THEN NULL
             WHEN length(id) <= 2 THEN id
             ELSE substr(id, 1, 1) ||
                  rpad('*', length(id)-2, '*') ||
                  substr(id, length(id), 1)
           END;
$$;

-- 8) 银行卡：保留前四位和后四位
CREATE OR REPLACE FUNCTION mask_bankcard(card text)
RETURNS text
LANGUAGE sql IMMUTABLE STRICT AS
$$
    SELECT CASE
             WHEN card IS NULL THEN NULL
             WHEN length(card) <= 8 THEN card
             ELSE substr(card, 1, 4) ||
                  rpad('*', length(card)-8, '*') ||
                  substr(card, length(card)-3, 4)
           END;
$$;

-- 9) 车牌：保留前两位和最后两位，中间用 * 显示
CREATE OR REPLACE FUNCTION mask_plate(plate text)
RETURNS text
LANGUAGE sql IMMUTABLE STRICT AS
$$
    SELECT CASE
             WHEN plate IS NULL THEN NULL
             WHEN length(plate) <= 4 THEN plate
             ELSE substr(plate, 1, 2) ||
                  rpad('*', length(plate)-4, '*') ||
                  substr(plate, length(plate)-1, 2)
           END;
$$;

-- 10) IP 地址：仅保留第一字段，其余替换为 *
CREATE OR REPLACE FUNCTION mask_ip(ip text)
RETURNS text
LANGUAGE sql IMMUTABLE STRICT AS
$$
    SELECT CASE
             WHEN ip IS NULL THEN NULL
             WHEN ip !~ '^([0-9]{1,3}\.){3}[0-9]{1,3}$' THEN rpad('*', length(ip), '*')
             ELSE split_part(ip, '.', 1) || '.*.*.*'
           END;
$$;

-- 11) 数值取整（四舍五入到整数）
CREATE OR REPLACE FUNCTION mask_numeric(num numeric)
RETURNS numeric
LANGUAGE sql IMMUTABLE STRICT AS
$$
    SELECT round(num);
$$;

-- 12) 置空：统一替换为 '-'
CREATE OR REPLACE FUNCTION mask_blank(val text)
RETURNS text
LANGUAGE sql IMMUTABLE STRICT AS
$$
    SELECT '-';
$$;

-- 13) 全遮掩：所有字符替换为 *
CREATE OR REPLACE FUNCTION mask_full(val text)
RETURNS text
LANGUAGE sql IMMUTABLE STRICT AS
$$
    SELECT CASE
             WHEN val IS NULL THEN NULL
             ELSE rpad('*', length(val), '*')
           END;
$$;