PostgreSQL更新函数问题：lab_grade为NULL时final_grade未正确赋值

安全阿岳

2026-7-23

问题排查：lab_grade为NULL时final_grade无法取exam_grade值

维护大学数据库时，需要更新Register表中的exam_grade、lab_grade和final_grade字段。final_grade基于exam_grade和lab_grade计算，但当lab_grade为NULL时，final_grade仍保持NULL，未按预期取exam_grade的值，函数无报错，推测逻辑存在问题。

函数代码

CREATE OR REPLACE FUNCTION public.fn_question_2_2(num integer)
RETURNS void
AS
$$
DECLARE
       pointer record;
       percentage numeric;
       exam_i numeric;
       lab_i numeric;
BEGIN
     FOR pointer IN
                   (SELECT rg.amka, rg.lab_grade, rg.exam_grade,
                           rg.serial_number, rg.register_status, 
                           cr.course_code, cr.lab_hours
                    FROM "Register" rg
                    JOIN (SELECT course_code, lab_hours FROM "Course") cr USING (course_code)
                    WHERE rg.register_status = 'approved' AND rg.serial_number = num)
                    LOOP
                        IF (pointer.exam_grade IS NULL) THEN
                            exam_i = floor((random()*(10-1)+1));
                        ELSE
                            exam_i = pointer.exam_grade;
                        END IF;
                        IF (pointer.lab_grade IS NULL AND  pointer.lab_hours > 0) THEN
                            lab_i = floor((random()*(10-1)+1));
                        ELSE
                            lab_i = pointer.lab_grade;
                        END IF;
                        percentage = (SELECT exam_percentage FROM "CourseRun" 
                                      WHERE course_code = pointer.course_code AND serial_number = pointer.serial_number);
                        UPDATE "Register" r
                        SET lab_grade = lab_i ,exam_grade = exam_i,
                            final_grade = (
                                CASE 
                                    WHEN lab_i IS NULL THEN exam_i
                                    ELSE floor(((lab_i*(100-COALESCE(percentage, 100))) + exam_i * COALESCE(percentage, 100))/100)
                                END
                            )
                        WHERE (final_grade IS NULL)
                        AND r.amka = pointer.amka  
                        AND r.course_code = pointer.course_code
                        AND r.register_status = 'approved';
                        
                    END LOOP;
END;
$$
LANGUAGE plpgsql;

相关表结构

CourseRun表

-- DROP TABLE IF EXISTS public."CourseRun";

CREATE TABLE IF NOT EXISTS public."CourseRun"
(
    course_code character(7) COLLATE pg_catalog."default" NOT NULL,
    serial_number integer NOT NULL,
    exam_min numeric,
    lab_min numeric,
    exam_percentage numeric,
    labuses integer,
    semesterrunsin integer NOT NULL,
    CONSTRAINT "CourseRun_pkey" PRIMARY KEY (course_code, serial_number),
    CONSTRAINT "CourseRun_course_code_fkey" FOREIGN KEY (course_code)
        REFERENCES public."Course" (course_code) MATCH SIMPLE
        ON UPDATE NO ACTION
        ON DELETE NO ACTION,
    CONSTRAINT "CourseRun_labuses_fkey" FOREIGN KEY (labuses)
        REFERENCES public."Lab" (lab_code) MATCH SIMPLE
        ON UPDATE NO ACTION
        ON DELETE NO ACTION,
    CONSTRAINT "CourseRun_semesterrunsin_fkey" FOREIGN KEY (semesterrunsin)
        REFERENCES public."Semester" (semester_id) MATCH SIMPLE
        ON UPDATE NO ACTION
        ON DELETE NO ACTION
)

TABLESPACE pg_default;

ALTER TABLE IF EXISTS public."CourseRun"
    OWNER to postgres;

Course表

-- DROP TABLE IF EXISTS public."Course";

CREATE TABLE IF NOT EXISTS public."Course"
(
    course_code character(7) COLLATE pg_catalog."default" NOT NULL,
    course_title character(100) COLLATE pg_catalog."default" NOT NULL,
    units smallint NOT NULL,
    lecture_hours smallint NOT NULL,
    tutorial_hours smallint NOT NULL,
    lab_hours smallint NOT NULL,
    typical_year smallint NOT NULL,
    typical_season semester_season_type NOT NULL,
    obligatory boolean NOT NULL,
    course_description character varying COLLATE pg_catalog."default",
    CONSTRAINT "Course_pkey" PRIMARY KEY (course_code)
)

TABLESPACE pg_default;

ALTER TABLE IF EXISTS public."Course"
    OWNER to postgres;

Register表

-- DROP TABLE IF EXISTS public."Register";

CREATE TABLE IF NOT EXISTS public."Register"
(
    amka character varying COLLATE pg_catalog."default" NOT NULL,
    serial_number integer NOT NULL,
    course_code character(7) COLLATE pg_catalog."default" NOT NULL,
    exam_grade numeric,
    final_grade numeric,
    lab_grade numeric,
    register_status register_status_type,
    CONSTRAINT "Register_pkey" PRIMARY KEY (course_code, serial_number, amka),
    CONSTRAINT "Register_course_run_fkey" FOREIGN KEY (serial_number, course_code)
        REFERENCES public."CourseRun" (serial_number, course_code) MATCH SIMPLE
        ON UPDATE CASCADE
        ON DELETE CASCADE,
    CONSTRAINT "Register_student_fkey" FOREIGN KEY (amka)
        REFERENCES public."Student" (amka) MATCH SIMPLE
        ON UPDATE CASCADE
        ON DELETE CASCADE
        NOT VALID
)

TABLESPACE pg_default;

ALTER TABLE IF EXISTS public."Register"
    OWNER to postgres;

测试数据

amka|serial_number|course_code|exam_grade|final_grade|lab_grade|semes_status
"01010104188"   12  "ΑΓΓ 201"   6   6   10  "pass"
"01010104188"   12  "ΑΓΓ 202"   2           "approved"
"01010104188"   12  "ΗΡΥ 201"   8   9   10  "pass"
"01010104188"   12  "ΗΡΥ 202"   9   8   7   "pass"
"01010104188"   12  "ΗΡΥ 203"   7   8.40    9   "approved"
"01010104188"   12  "ΗΡΥ 204"   7   5.50    4   "approved"
"01010104188"   12  "ΗΡΥ 211"   9           "approved"
"01010104188"   12  "ΜΑΘ 107"   2   2   6   "fail"
"01010104188"   12  "ΠΛΗ 201"   7   0   2   "fail"
"01010104188"   12  "ΠΛΗ 202"   2   2.70    3   "approved"
"01010104188"   12  "ΠΛΗ 211"   8   7   5   "pass"
"01010104188"   12  "ΤΗΛ 201"   7   7   7   "pass"
"01010104188"   12  "ΤΗΛ 202"   4   3.20    2   "approved"
"01010104188"   12  "ΤΗΛ 211"   5   7.00    9   "approved"

问题原因

lab_i赋值逻辑漏洞：仅当pointer.lab_grade IS NULL且pointer.lab_hours > 0时才生成随机值，否则直接取pointer.lab_grade。如果课程lab_hours=0，即使lab_grade为NULL，lab_i也会保持NULL。
final_grade计算逻辑错误：原CASE判断pointer.lab_hours IS NOT NULL，但Course表中lab_hours是NOT NULL字段，该条件永远为真，始终执行加权计算分支。当lab_i为NULL时，加权计算结果为NULL，导致final_grade为NULL。
未处理percentage为NULL的情况：如果CourseRun表中exam_percentage为NULL，加权计算也会得到NULL结果。

修复方案

修改final_grade的计算逻辑，优先判断lab_i是否为NULL，直接返回exam_i；同时用COALESCE处理percentage为NULL的情况，默认按100%考试占比计算：

final_grade = (
    CASE 
        WHEN lab_i IS NULL THEN exam_i
        ELSE floor(((lab_i*(100-COALESCE(percentage, 100))) + exam_i * COALESCE(percentage, 100))/100)
    END
)

如果需求是只要课程无实验（lab_hours=0），无论lab_grade是否存在，final_grade都取exam_grade，可以进一步调整CASE逻辑：

final_grade = (
    CASE 
        WHEN pointer.lab_hours = 0 THEN exam_i
        WHEN lab_i IS NULL THEN exam_i
        ELSE floor(((lab_i*(100-COALESCE(percentage, 100))) + exam_i * COALESCE(percentage, 100))/100)
    END
)

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

PostgreSQL更新函数问题：lab_grade为NULL时final_grade未正确赋值

函数代码 #

相关表结构 #

CourseRun表 #

Course表 #

Register表 #

测试数据 #

问题原因 #

修复方案 #