PostgreSQL 9.4：批量修正飞行路线字符串中的Route后缀

在PostgreSQL 9.4环境中，某表的detail文本字段用于存储飞机飞行路线，字段遵循以下规则：

• 路线由定位点（Fixes）和航线（Routes）组成，两者名称长度为3或5字符，名称可能重复；
• 字段中可包含0、1或2条Routes，Routes后需跟随非零数字或#；
• Routes和Fixes前后可带有+或*符号；
• 字段内的换行、多空格需完整保留。

数据规模：单模式下表含6-20000条记录，全局共有近1800个Route名称，单模式内通常为40-80个。

示例数据 #

"KIND ROCKY1 STL BUM OATHE CLASH5 KDEN"
"+MEARZ7 OKK+
KIND OKK FWA MIZAR3 KDTW"
"KIND OOM OOM5 WEGEE PXV J131 LIT BYP5 KDFW"
"KIND MEARZ# OKK ECK YEE YXI N171B VALIEE***EGSS"

需求 #

修正Route后缀的不规范使用：将#替换为正确数字，同时更新错误的版本号，例如：

• MEARZ7/MEARZ# → MEARZ9
• OOM5 → OOM6
• 单独的OOM（后跟空格）保持不变

当前方案的问题 #

现有嵌套CASE语句的SQL存在两个核心缺陷：

1. 一次仅能处理一个Route，同字段内多个Route无法全部修正；
2. 单模式下Route数量可达80+，CASE语句会极度冗长，维护成本极高。

解决方案：自定义函数+Route映射表 #

1. 创建Route修正规则表 #

先建立一个可维护的映射表，存储每个Route对应的正确后缀：

CREATE TABLE route_corrections (
    route_name TEXT PRIMARY KEY,
    correct_suffix TEXT NOT NULL CHECK (correct_suffix ~ '^[1-9]$')
);

-- 插入示例修正规则
INSERT INTO route_corrections (route_name, correct_suffix)
VALUES 
    ('CLASH', '5'),
    ('MEARZ', '9'),
    ('OOM', '6'),
    ('ROCKY', '1');

2. 编写自定义替换函数 #

通过PL/pgSQL函数遍历所有修正规则，使用正则批量替换：

CREATE OR REPLACE FUNCTION correct_route_suffixes(input_text TEXT)
RETURNS TEXT AS $$
DECLARE
    rec RECORD;
    pattern TEXT;
BEGIN
    FOR rec IN SELECT route_name, correct_suffix FROM route_corrections LOOP
        -- 构造正则：匹配带可选前缀(+/*)的Route，后缀为#或数字
        pattern := '\m([+*]?)(' || rec.route_name || ')([#0-9])\M';
        -- 全局替换为前缀+正确Route+后缀
        input_text := REGEXP_REPLACE(input_text, pattern, '\1' || rec.route_name || rec.correct_suffix, 'g');
    END LOOP;
    RETURN input_text;
END;
$$ LANGUAGE plpgsql STABLE;

3. 执行批量更新 #

调用函数完成更新，可选添加WHERE条件过滤需修改的记录以提升性能：

UPDATE target 
SET detail = correct_route_suffixes(detail)
WHERE detail ~ ANY(SELECT '\m[+*]?' || route_name || '[#0-9]\M' FROM route_corrections);

方案优势 #

• 易维护：新增或修改修正规则只需更新route_corrections表，无需改动函数或更新语句；
• 全量处理：一次完成字段内所有符合规则的Route修正；
• 格式保留：完整保留原字段的换行、多空格及Route前后的+/*符号。

内容的提问来源于stack exchange，提问作者Stuart K. Smith