如何通过PostgreSQL触发器实现run_no与log_header_id编号同步自动填充

已在PostgreSQL中创建触发器函数func_log_header_id，插入public.log_header表时自动生成格式为「well_id_类型编码<两位序号>」的log_header_id。现在需要自动填充run_no字段，使其值与log_header_id中的序号部分一致，尝试使用序列无法实现匹配效果，以下是解决方案：

修改后的触发器函数 #

DECLARE
  v_run_no int := 0;
  v_type_code character varying;
BEGIN
  -- 获取当前well_id+log_type对应的序号
  SELECT COUNT(well_id) + 1 INTO v_run_no FROM log_header 
  WHERE well_id = NEW.well_id AND log_type = NEW.log_type;
  
  -- 处理无记录的边界情况
  v_run_no := COALESCE(v_run_no, 1);
  
  -- 获取类型编码
  SELECT value INTO v_type_code FROM lookup_ref 
  WHERE value_description = NEW.log_type 
    AND table_name = 'log_header' 
    AND column_name = 'log_type';
  
  -- 生成log_header_id
  NEW.log_header_id := NEW.well_id || '_' || v_type_code || '<' || to_char(v_run_no, 'fm00') || '>';
  
  -- 直接将序号同步到run_no字段
  NEW.run_no := v_run_no;
  
RETURN NEW;
END;

关键修改说明 #

1. 复用已计算的序号值：触发器中已经通过COUNT(well_id)+1算出了对应well_id和log_type的序号v_run_no，无需从生成的log_header_id字符串中解析序号，直接将v_run_no赋值给NEW.run_no，保证两者完全一致，既高效又避免字符串解析可能产生的错误。
2. 简化空值处理：使用COALESCE(v_run_no, 1)替代原有的IF判断，因为COUNT()函数返回数值类型，不会返回NULL，仅当查询无结果时可能为NULL，用COALESCE可以更简洁处理边界场景。
3. 优化变量可读性：将原变量v_log_header_id改为v_run_no，贴合其实际含义，提升代码可维护性。

并发场景优化建议 #

原代码用COUNT()+1生成序号，在高并发插入时可能出现重复序号问题（多个事务同时执行COUNT会得到相同值）。可以改用基于MAX()的逻辑优化：

SELECT COALESCE(MAX(run_no), 0) + 1 INTO v_run_no FROM log_header 
WHERE well_id = NEW.well_id AND log_type = NEW.log_type;

同时建议给表添加(well_id, log_type, run_no)唯一约束，进一步避免序号重复。

内容的提问来源于stack exchange，提问作者Muhammad Bahruddin Saputra