PostgreSQL：如何基于另一列值自动更新列内容

1. 创建触发器函数 #

这个函数会根据新插入的mobile_phone字段前缀，自动给country字段赋值：

CREATE OR REPLACE FUNCTION update_country_func()
RETURNS TRIGGER AS $$
BEGIN
    -- 根据手机号前两位匹配对应国家代码
    NEW.country := CASE
        WHEN LEFT(NEW.mobile_phone, 2) = '51' THEN 'PE'
        WHEN LEFT(NEW.mobile_phone, 2) = '56' THEN 'CL'
        WHEN LEFT(NEW.mobile_phone, 2) = '57' THEN 'CO'
        -- 前缀不匹配规则时，可自定义默认值（这里设为NULL）
        ELSE NULL
    END;
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

2. 创建BEFORE INSERT触发器 #

使用BEFORE INSERT触发器可以在数据插入到表之前完成country字段赋值，避免额外的更新操作，性能更优：

CREATE TRIGGER trigger_update_country
BEFORE INSERT ON info
FOR EACH ROW
EXECUTE FUNCTION update_country_func();

3. 测试效果 #

插入测试数据：

INSERT INTO info (mobile_phone) VALUES 
('51682815'),
('51261728'),
('56682815'),
('56261728'),
('57682815'),
('57261728');

查询表数据：

SELECT mobile_phone, country FROM info;

返回结果将完全符合你的预期：

补充说明 #

• 若需要支持修改手机号后同步更新country，可修改触发器触发时机：

  DROP TRIGGER IF EXISTS trigger_update_country ON info;
  CREATE TRIGGER trigger_update_country
  BEFORE INSERT OR UPDATE ON info
  FOR EACH ROW
  EXECUTE FUNCTION update_country_func();
  

• 若手机号可能为空，函数会自动将country设为NULL，你可根据需求调整ELSE分支的默认值。

内容的提问来源于stack exchange，提问作者Iván Mateo Zimic García