Problem with update query - I don't get the results I want

So I database for a university and I need to update several columns of a table named register going with the name of exam_grade, lab_grade, and final grade. The final grade is calculated based on the results of exam_grade and lab_grade, but whenever the result of lab_grade is NULL then the result of final_grade is not becoming directly the exam_grade, but also stays NULL. Here is the code for the function:

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 = (SELECT
CASE WHEN pointer.lab_hours IS NOT NULL 
THEN (floor(((lab_i*(100-percentage)) + exam_i * percentage)/100))
ELSE (exam_i)
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';

-- 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;```
```-- Table: public.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;```
```-- Table: public.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"

Any help will be appreciated, and if anything needs to be added so that you get a better perspective please say so.

I tried many different ways of writing the query and the results are every time the same. I cannot get the final_grade = exam_ grade when the lab_grade is NULL. I don't get any error message so there is a problem with the logic.

You can either use COALESCE or check in your CASE:

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 = (SELECT
                                               CASE WHEN pointer.lab_hours IS NOT NULL 
                                               THEN (floor(((COALESCE(lab_i, 0)*(100-percentage)) + exam_i * percentage)/100))
                                               ELSE (exam_i)
                                               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';

or

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 = (SELECT
                                               CASE WHEN pointer.lab_hours IS NOT NULL AND lab_i IS NOT NULL
                                               THEN (floor(((lab_i*(100-percentage)) + exam_i * percentage)/100))
                                               ELSE (exam_i)
                                               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';

Your subquery is the place where we need to check what the problem is, because, since you complained on the wrong value resulting in the update and not a lack of value change. Hence, let's see this subquery:

SELECT
CASE WHEN pointer.lab_hours IS NOT NULL 
THEN (floor(((lab_i*(100-percentage)) + exam_i * percentage)/100))
ELSE (exam_i)
END

NULL is an absorbing element, so, whatever operation you perform with it, the result will be NULL. The reason is simple: NULL is the lack of value, or, in other words, it is the unknown. So, what's the result of

unkown + 2

for example? Naturally, it's unknown.

So, without knowing too much about the data you are testing, these are the fields that could have a NULL value:

• lab_i
• percentage
• exam_i

So, you will likely need to figure out what the default value should be and use such a default value as a parameter to a call for COALESCE, of the form of COALESCE(<your field or expression>, <the preferred default>)