PostgreSQL触发器报错：用作表达式的子查询返回多行

错误原因分析 #

报错ERROR: more than one row returned by a subquery used as an expression的核心问题：

• 原触发器函数中执行UPDATE dati_raccolta时，子查询FROM dati_raccolta会返回表中所有行，但SET num_accessione = (...)要求子查询仅返回单个值，当表存在多条记录时必然触发错误。
• 触发器设计逻辑冗余且不合理：使用AFTER INSERT FOR EACH STATEMENT会在插入语句执行后更新整个表，完全没必要且效率极低；UPDATE触发器仅监听geom字段变化，但num_accessione的生成依赖多个其他字段，触发条件不完整。

修正后的触发器函数与触发器定义 #

1. 重构触发器函数 #

CREATE OR REPLACE FUNCTION dati_raccolta_pop_accessione()
    RETURNS TRIGGER AS 
    $datiraccoltapopaccessione$
    BEGIN
        -- 直接使用当前行的字段值生成num_accessione，无需查询整个表
        NEW.num_accessione := CASE
            WHEN NEW.interesse_agricolo = 'si' THEN
                CONCAT(
                    NEW.banca_germoplasma, '_A_', 
                    REGEXP_REPLACE(NEW.data_raccolta::text, '-', '', 'g'), '_', NEW.gid, '_',
                    UPPER(
                        REGEXP_REPLACE(
                            REGEXP_REPLACE(NEW.nome_raccoglitore, '\y(\w)\w*', '\1', 'g'), 
                            '\s+', '', 'g'
                        )
                    )
                )
            WHEN NEW.interesse_agricolo = 'no' THEN
                CONCAT(
                    NEW.banca_germoplasma, '_N_', 
                    REGEXP_REPLACE(NEW.data_raccolta::text, '-', '', 'g'), '_', NEW.gid, '_',
                    UPPER(
                        REGEXP_REPLACE(
                            REGEXP_REPLACE(NEW.nome_raccoglitore, '\y(\w)\w*', '\1', 'g'), 
                            '\s+', '', 'g'
                        )
                    )
                )
        END;
        
        RETURN NEW;
    END;
    $datiraccoltapopaccessione$ 
    LANGUAGE plpgsql;

2. 创建正确的触发器 #

-- 插入前触发，为新行赋值
CREATE TRIGGER dati_raccolta_pop_num_accessione_insert
BEFORE INSERT ON dati_raccolta
FOR EACH ROW
EXECUTE PROCEDURE dati_raccolta_pop_accessione();

-- 更新前触发，当影响num_accessione的字段变化时重新生成值
CREATE TRIGGER dati_raccolta_pop_num_accessione_update
BEFORE UPDATE ON dati_raccolta
FOR EACH ROW
WHEN (
    OLD.interesse_agricolo IS DISTINCT FROM NEW.interesse_agricolo
    OR OLD.banca_germoplasma IS DISTINCT FROM NEW.banca_germoplasma
    OR OLD.data_raccolta IS DISTINCT FROM NEW.data_raccolta
    OR OLD.gid IS DISTINCT FROM NEW.gid
    OR OLD.nome_raccoglitore IS DISTINCT FROM NEW.nome_raccoglitore
)
EXECUTE PROCEDURE dati_raccolta_pop_accessione();

关键改动说明 #

• 触发器类型改为BEFORE FOR EACH ROW：在插入/更新操作生效前直接修改NEW记录的num_accessione字段，避免更新整个表的低效操作，彻底解决子查询返回多行的问题。
• 直接引用NEW对象字段：不再通过子查询遍历全表，而是直接使用当前触发行的字段值拼接生成目标值，逻辑简洁高效。
• 完善UPDATE触发条件：将触发范围扩展为所有影响num_accessione生成的字段，确保任何相关字段变更时都会重新计算该值，而不仅限于geom字段。

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