如何让PostgreSQL的dmetaphone系列函数返回更长的带备选语音编码？

原生fuzzystrmatch模块提供的dmetaphone()和dmetaphone_alt()函数基于标准Double Metaphone算法实现，该算法默认输出固定长度为4位的语音编码，因此官方实现没有开放自定义输出长度的参数，无法直接通过传参调整输出长度。以下是两种可落地的解决方案：

方案1：自定义可配置长度的双谐音编码函数 #

你可以基于原生Double Metaphone的逻辑封装PL/pgSQL自定义函数，在保留主编码、备选编码输出能力的同时支持自定义输出长度，适配Supabase环境无需额外编译扩展：

-- 自定义带长度参数的dmetaphone函数
CREATE OR REPLACE FUNCTION dmetaphone_with_length(input_text text, output_length int DEFAULT 4)
RETURNS text AS $$
DECLARE
    base_code text := dmetaphone(input_text);
    remaining_text text := lower(input_text);
BEGIN
    -- 输出长度小于等于默认4位时直接截断原生结果
    IF output_length <= 4 THEN
        RETURN left(base_code, output_length);
    END IF;
    -- 补充后续字符的语音编码，延长到指定长度，可根据你的姓名场景调整映射规则
    WHILE length(base_code) < output_length AND remaining_text != '' LOOP
        base_code := base_code || dmetaphone(right(remaining_text, 1));
        remaining_text := right(remaining_text, -1);
    END LOOP;
    RETURN left(base_code, output_length);
END;
$$ LANGUAGE plpgsql IMMUTABLE;

-- 同理实现带长度参数的备选编码函数
CREATE OR REPLACE FUNCTION dmetaphone_alt_with_length(input_text text, output_length int DEFAULT 4)
RETURNS text AS $$
DECLARE
    base_code text := dmetaphone_alt(input_text);
    remaining_text text := lower(input_text);
BEGIN
    IF output_length <= 4 THEN
        RETURN left(base_code, output_length);
    END IF;
    WHILE length(base_code) < output_length AND remaining_text != '' LOOP
        base_code := base_code || dmetaphone_alt(right(remaining_text, 1));
        remaining_text := right(remaining_text, -1);
    END LOOP;
    RETURN left(base_code, output_length);
END;
$$ LANGUAGE plpgsql IMMUTABLE;

使用时直接传入长度参数即可，示例：

SELECT dmetaphone_with_length('Lee', 6), dmetaphone_alt_with_length('Lee', 6);

方案2：双编码组合匹配（无需自定义函数） #

如果不想修改现有编码逻辑，可以组合使用Double Metaphone和指定长度的Metaphone做联合校验，既保留变体匹配能力，又提升短姓名的匹配精度：

1. 对所有入库姓名同时存储3个字段：dmetaphone_code（原生dmetaphone结果）、dmetaphone_alt_code（原生dmetaphone_alt结果）、long_metaphone_code（指定长度的metaphone结果，短姓名场景建议设为6-8位）
2. 匹配时采用加权规则校验，示例查询：

-- 搜索姓名为'John'的近似匹配结果
SELECT * FROM personnel
WHERE 
    (dmetaphone_code = dmetaphone('John') OR dmetaphone_alt_code = dmetaphone('John'))
    AND levenshtein(long_metaphone_code, metaphone('John', 6)) <= 1;

该方案完全基于Supabase原生函数实现，不需要额外开发，上线速度快。

注意事项 #

• 短姓名场景输出长度建议设置为6-8位即可，过长的编码会降低对拼写错误的容错性，反而影响匹配效果
• 如果涉及多语种姓名匹配，可以针对不同语种调整自定义函数里的语音映射规则，进一步提升准确率

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