如何在PostgreSQL中创建含REGEXP_REPLACE的文本归一化函数

我来帮你把这个用于地址文本归一化的PostgreSQL函数打磨好——这类标准化需求在地址数据处理里太常见了，咱们一步步把它做完整。

先修正基础问题，补全核心逻辑 #

你给出的示例里有拼写错误（比如address_toke少了个n），而且CASE WHEN结构如果没有ELSE分支，不匹配规则的文本会返回NULL，这肯定不是咱们想要的。先给你一个补全后的基础版本：

CREATE OR REPLACE FUNCTION clean_data(address_token text) 
RETURNS text AS $$
BEGIN
    RETURN CASE
        -- 归一化 alley 的各种变体结尾
        WHEN address_token ~* '(allee|ally|aly)$' THEN regexp_replace(address_token, '(allee|ally|aly)$', 'alley', 'i')
        -- 补全 annex 变体处理（比如 annexe、annx 这类常见缩写）
        WHEN address_token ~* '(annex|annexe|annx)$' THEN regexp_replace(address_token, '(annex|annexe|annx)$', 'annex', 'i')
        -- 添加 street 的常见缩写归一化
        WHEN address_token ~* '(st\.|str\.|strt)$' THEN regexp_replace(address_token, '(st\.|str\.|strt)$', 'street', 'i')
        -- 添加 avenue 的变体归一化
        WHEN address_token ~* '(ave\.|av)$' THEN regexp_replace(address_token, '(ave\.|av)$', 'avenue', 'i')
        -- 默认返回原文本，避免出现NULL
        ELSE address_token
    END;
END;
$$ LANGUAGE plpgsql IMMUTABLE;

几个关键细节说明 #

• 用~*替代多个LIKE OR：这是PostgreSQL里不区分大小写的正则匹配操作符，比写一堆LIKE '%xxx' OR LIKE '%yyy'简洁太多，而且逻辑更统一。
• 正则替换加'i'参数：第四个参数'i'开启不区分大小写模式，不管输入是AlLy还是ALLEE，都能正确替换成alley。
• 标记函数为IMMUTABLE：因为相同输入永远返回相同输出，PostgreSQL会给这类函数做性能优化，处理大量数据时速度更快。
• 必须加ELSE分支：确保不匹配任何规则的文本原样返回，不会出现意外的NULL值。

如果需要处理批量数据（返回setof text） #

如果你的需求是一次性处理多个文本，把函数改成接收数组参数并返回集合版本更合适：

CREATE OR REPLACE FUNCTION clean_data(address_tokens text[]) 
RETURNS setof text AS $$
BEGIN
    FOREACH token IN ARRAY address_tokens LOOP
        RETURN NEXT CASE
            WHEN token ~* '(allee|ally|aly)$' THEN regexp_replace(token, '(allee|ally|aly)$', 'alley', 'i')
            WHEN token ~* '(annex|annexe|annx)$' THEN regexp_replace(token, '(annex|annexe|annx)$', 'annex', 'i')
            ELSE token
        END;
    END LOOP;
END;
$$ LANGUAGE plpgsql IMMUTABLE;

调用示例 #

• 单条文本处理：

SELECT clean_data('Main Ally'); -- 返回 'Main alley'
SELECT clean_data('123 Oak Annexe'); -- 返回 '123 Oak annex'

• 批量处理数组：

SELECT * FROM clean_data(ARRAY['Main Ally', 'Oak Annexe', 'Pine St.']);

实用优化建议 #

• 规则顺序要注意：把更具体的规则放在前面，比如要先匹配st\.再匹配st，不然会把st.误改成street.。
• 处理边缘案例：如果你的地址文本可能带结尾标点，可以把正则改成'(allee|ally|aly)\W?$'，这样能匹配Elm Allee,或者Maple Aly.这类情况，替换后自动去掉标点。
• 测试全覆盖：一定要用各种场景测试，比如全大写、全小写、混合大小写、带特殊字符的输入，确保规则都能正确触发。

内容的提问来源于stack exchange，提问作者David Jacques