PostgreSQL触发器函数报错：子查询返回多行作为表达式

原始触发器函数 #

CREATE OR REPLACE FUNCTION update_user_grade()
 RETURNS TRIGGER AS $$
 BEGIN
  IF TG_OP = 'INSERT' THEN
    IF EXISTS (
            SELECT 1
            FROM courses c
            WHERE c.user_id = NEW.user_id
            AND c.subject = NEW.subject
            AND c.course_code = NEW.course_code
            AND c.credits = NEW.credits) then
            IF (SELECT c.grade FROM courses c WHERE c.user_id = NEW.user_id AND c.subject = NEW.subject AND c.course_code = NEW.course_code
                AND c.credits = NEW.credits) < NEW.grade THEN
                UPDATE users
                SET grade = grade + (NEW.grade * NEW.credits) - (SELECT c.grade * c.credits FROM courses c
                                                            WHERE c.user_id = NEW.user_id AND c.subject = NEW.subject AND c.course_code = NEW.course_code AND c.credits = NEW.credits AND c.grade < NEW.grade)
                WHERE id = NEW.user_id;
            ELSE
               RETURN NULL;
            END IF;
    ELSE        
        IF NEW.graded IS TRUE AND NEW.grade IS NOT NULL THEN
            UPDATE users
            SET grade = COALESCE(grade + (NEW.grade * new.credits),0),
                credits = COALESCE(credits + new.credits, 0),
                counted_credits = COALESCE(counted_credits + new.credits, 0)
            WHERE id = NEW.user_id;
        ELSIF NEW.graded IS FALSE AND (NEW.grade = 1 OR NEW.grade = 0) 
THEN
            UPDATE users
            SET credits = COALESCE(credits + new.credits, 0)
            WHERE id = NEW.user_id;
        END IF;
    END IF;
  END IF;
RETURN NEW;

END;
$$ LANGUAGE plpgsql;

报错信息 #

more than one row returned by a subquery used as an expression

错误原因 #

报错核心是作为表达式使用的子查询返回了多行结果，涉及两处关键子查询：

1. 判断旧成绩是否小于新成绩的子查询：(SELECT c.grade FROM courses c WHERE ...)
2. 更新用户成绩时计算差值的子查询：(SELECT c.grade * c.credits FROM courses c WHERE ...)

这说明courses表中，user_id+subject+course_code+credits的组合无唯一约束，存在重复记录。当子查询匹配到多行时，PostgreSQL无法将多行结果作为单个表达式的值使用，因此抛出错误。

修复方案 #

1. 核心修复逻辑 #

• 将重复查询的旧数据存入变量，减少表扫描次数
• 强制子查询仅返回单行（用LIMIT 1+排序逻辑，确保取到符合业务需求的旧记录）
• 优化条件判断逻辑，避免冗余查询

2. 修复后的代码 #

CREATE OR REPLACE FUNCTION update_user_grade()
 RETURNS TRIGGER AS $$
DECLARE
    v_old_grade NUMERIC;
    v_old_weighted_grade NUMERIC;
BEGIN
    IF TG_OP = 'INSERT' THEN
        -- 一次性查询旧数据存入变量，避免重复扫描表
        SELECT c.grade, c.grade * c.credits
        INTO v_old_grade, v_old_weighted_grade
        FROM courses c
        WHERE c.user_id = NEW.user_id
          AND c.subject = NEW.subject
          AND c.course_code = NEW.course_code
          AND c.credits = NEW.credits
        ORDER BY c.grade DESC -- 取该课程组合下最高的旧成绩，可根据业务调整排序规则
        LIMIT 1; -- 强制仅返回一行

        IF v_old_grade IS NOT NULL THEN
            -- 存在旧记录时，对比成绩并更新
            IF v_old_grade < NEW.grade THEN
                UPDATE users
                SET grade = grade + (NEW.grade * NEW.credits) - v_old_weighted_grade
                WHERE id = NEW.user_id;
            ELSE
                RETURN NULL;
            END IF;
        ELSE
            -- 无旧记录时，按规则更新用户数据
            IF NEW.graded IS TRUE AND NEW.grade IS NOT NULL THEN
                UPDATE users
                SET grade = COALESCE(grade + (NEW.grade * new.credits), 0),
                    credits = COALESCE(credits + new.credits, 0),
                    counted_credits = COALESCE(counted_credits + new.credits, 0)
                WHERE id = NEW.user_id;
            ELSIF NEW.graded IS FALSE AND (NEW.grade = 1 OR NEW.grade = 0) THEN
                UPDATE users
                SET credits = COALESCE(credits + new.credits, 0)
                WHERE id = NEW.user_id;
            END IF;
        END IF;
    END IF;
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

3. 额外建议 #

如果业务上user_id+subject+course_code+credits应该是唯一的，建议给courses表添加唯一约束，从根源避免重复数据：

ALTER TABLE courses
ADD CONSTRAINT unique_user_course UNIQUE (user_id, subject, course_code, credits);

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