PostgreSQL触发器中SPLIT_PART报子查询返回多行错误求助

• 临时表生成逻辑错误：创建dms_temp时你从全表well查询数据，只要well表已有多条记录，临时表就会生成多行数据，后续子查询读取临时表时自然返回多行触发报错。
• INSERT查询无过滤条件：后续向well表插入uwi的SELECT语句未加WHERE条件，直接查询全表well数据，返回行数和表内总条数一致，必然触发多行返回错误。
• 触发器逻辑架构错误：你使用AFTER INSERT触发器，还在触发器内再次执行INSERT INTO well操作，会导致无限循环插入；且修改当前新插入行的字段值完全不需要额外执行INSERT语句，用BEFORE INSERT触发器直接修改NEW对象即可。

直接改用BEFORE INSERT触发器，不需要临时表、不需要额外写INSERT语句，直接在内存中完成uwi的计算赋值，性能高也不会有逻辑错误，完整代码如下：

1. 创建触发器函数 #

CREATE OR REPLACE FUNCTION calc_well_uwi()
RETURNS TRIGGER AS $BODY$
DECLARE
    v_dms text; -- 存储转换后的DMS字符串
BEGIN
    IF TG_OP = 'INSERT' THEN
        -- 直接用新插入行的经纬度计算DMS，不需要查表
        v_dms := ST_AsLatLonText(
            'POINT (' || NEW.surface_longitude::text || ' ' || NEW.surface_latitude::text || ')',
            'D M S.SSS C'
        );
        -- 直接计算uwi赋值给NEW对象
        NEW.uwi := '062.'
            || CASE WHEN NEW.primary_source = 'PEP' THEN '0001' ELSE '0002' END
            || '.00.'
            || SPLIT_PART(v_dms, ' ', 4)
            || SPLIT_PART(v_dms, ' ', 1)
            || SPLIT_PART(v_dms, ' ', 2)
            || regexp_replace(SPLIT_PART(v_dms, ' ', 3), '(\d)\.(\d)', '\1\2')
            || '.'
            || SPLIT_PART(v_dms, ' ', 8)
            || SPLIT_PART(v_dms, ' ', 5)
            || SPLIT_PART(v_dms, ' ', 6)
            || regexp_replace(SPLIT_PART(v_dms, ' ', 7), '(\d)\.(\d)', '\1\2');
    END IF;
    RETURN NEW;
END;
$BODY$ LANGUAGE plpgsql VOLATILE COST 100;

2. 绑定触发器到well表 #

CREATE TRIGGER trg_well_before_insert
BEFORE INSERT ON public.well
FOR EACH ROW EXECUTE FUNCTION calc_well_uwi();

该触发器会在每一行插入well表之前自动计算uwi值并写入新行，不需要额外的插入操作，也不会触发循环，完全规避多行返回的问题。

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