PostgreSQL Question posted in
The official documentation can be found here.

Postgresql – 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.

Tags:

2

Answers


  1. 0

    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';
    Login or Signup to reply.
  2. 0

    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>)

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search