如何在PostgreSQL中用动态正则匹配product_id并更新对应family_name字段

安全阿岳

2026-10-5

需求背景

现有两张数据表：family_values (family_name, item_regex) 和 product_ids (product_id)，需要通过匹配结果更新第三张表的family_name字段。规划是从family_values表取数，用其中的item_regex值对product_ids每一行的product_id做匹配校验。

场景说明

我需要将CSV静态数据导入orders表，核算商品成本和市场价值时，需要通过family_values的前缀正则和product_id匹配确定所属产品系列。之前在客户端实现的逻辑如下：

const families = { 
FOOBAR: 'Big Ogre',
FOOBA: 'Wood Elf',
FOO: 'Valkyrie'
};
// 查找产品所属系列，后续用于核算COGs和市场价值
const findFamily = product_id => Object.keys(families).find(f => new RegExp('^' + f).test(product_id));

但客户端执行性能损耗过大，我在PostgreSQL中新建了family_values表存储family_name、item_regex、cogs、market_value字段；product_ids表存储业务关注的所有产品ID（总产品规模达数百万），还设置了前置插入触发器过滤不在product_ids视图中的CSV数据。后续product_ids视图可以不用，因为导入只读数据后的orders表已有匹配的product_id，但它没有family_name字段，仍需解决产品系列归属判定问题。

问题描述

我预想了两种可行方案：

通过family_values正则匹配orders.product_id更新orders表的family字段
给product_ids表新增family字段，配合现有前置插入触发器（当前用于补零左对齐和数据标准化）使用

但我是PostgreSQL新手，不熟悉PG中的正则用法，无法实现类似JS中Array Find的匹配逻辑。我已经按item_regex对family_values排序，最严格的匹配项排在最前保证优先命中，排序示例如下：

family_values_array = [
{"family_name": "Big Ogre", "item_regex": "FOOBAR"}, 
{"family_name": "Wood Elf", "item_regex": "FOOBA"}, 
{"family_name": "Valkyrie", "item_regex": "FOO"}]

比如product_id匹配^FOOBA时，应返回"Wood Elf"系列。

最终实现方案

我找到的解决方案是用concat拼接前缀匹配表达式，核心语句如下：

select * into family_value_row from iol.family_values 
where lvl3_id = product_row.lvl3_id and product_row.product_id 
like concat(item_regex, '%') limit 1;

完整函数实现如下：

create or replace function iol.populate_families () returns void as $$
declare
    product_row record;
    family_value_row record;
begin
for product_row in
    select product_id, lvl3_id from iol.products
loop
    -- 找到product_id匹配item_regex的最优结果，获取对应的family_name
    select * into family_value_row from iol.family_values 
      where lvl3_id = product_row.lvl3_id and product_row.product_id like concat(item_regex, '%') limit 1;
    -- 更新family_name和对应价值字段
    update iol.products set 
      family_name = family_value_row.family_name, 
      cog_cents = family_value_row.cog_cents, 
      market_value_cents = family_value_row.market_value_cents
        where product_id = product_row.product_id;
end loop;
end;
$$
LANGUAGE plpgsql;

内容的提问来源于stack exchange，提问作者Neil Gaetano Lindberg