PostgreSQL中批量替换非Unicode字符串为Unicode字符的触发器实现问题

这个问题我之前也碰到过！PostgreSQL的U&语法确实没法和正则捕获组直接配合，因为前者是在查询解析阶段就处理转义，而正则替换是运行时动态生成内容的，解析器根本没法提前知道\1对应什么值。不过我们可以用自定义函数来解决这个批量替换的问题，不用逐个列举字符。

第一步：创建批量修复Unicode转义的函数 #

我们可以写一个PL/pgSQL函数，自动查找字符串中所有uXXXX格式的转义（XXXX是四位十六进制数），把它们转换成对应的Unicode字符：

CREATE OR REPLACE FUNCTION fix_unicode_escapes(input_str text) RETURNS text AS $$
DECLARE
  result text := input_str;
  match text;
BEGIN
  -- 循环匹配所有uXXXX模式，直到没有匹配项为止
  WHILE result ~ 'u([0-9a-fA-F]{4})' LOOP
    -- 提取匹配到的十六进制部分（比如从u00e9中拿到00e9）
    match := substring(result FROM 'u([0-9a-fA-F]{4})');
    -- 把十六进制字符串转成Unicode码点，再转换成对应字符，替换原字符串
    result := replace(result, 'u' || match, chr(to_number(match, 'FMXXXX')));
  END LOOP;
  RETURN result;
END;
$$ LANGUAGE plpgsql IMMUTABLE;

测试一下这个函数：

SELECT fix_unicode_escapes('Cafu00e9 chatu00e8 u00A1Hola!');

返回结果应该是：Café chatè ¡Hola!，完全符合预期。

第二步：创建触发器自动处理字段 #

接下来我们把这个函数绑定到触发器上，这样每次插入或更新数据时，都会自动修复目标字段的Unicode转义问题。

假设你的表是your_table，需要修复的字段是content，先创建触发器函数：

CREATE OR REPLACE FUNCTION fix_unicode_trigger_func() RETURNS trigger AS $$
BEGIN
  -- 对新插入/更新的content字段执行修复
  NEW.content := fix_unicode_escapes(NEW.content);
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

然后创建触发器：

CREATE TRIGGER fix_unicode_before_insert_update
BEFORE INSERT OR UPDATE ON your_table
FOR EACH ROW EXECUTE FUNCTION fix_unicode_trigger_func();

现在再插入包含u00e9这类转义的字符串，就会自动转换成对应的Unicode字符啦！

为什么之前的方法行不通？ #

你之前尝试的U&'\\\1'报错，本质是因为PostgreSQL的U&语法是在查询解析阶段就处理Unicode转义的，而正则的\1捕获组是运行时才确定的值。解析器看到U&'\\\1'时，会先把它解析成U&'\1'，但\1不是合法的四位Unicode转义，所以直接报错。而单纯用regexp_replace返回Caf\00e9，只是把u换成了反斜杠，并没有真正转换成Unicode字符。

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