Need help improving effienciency in a function relative to deletion-CodePudding

create or replace function "dropCourse" (studentId integer, sectionId integer)
    returns void
as
$$
declare studentGrade varchar;
BEGIN
    studentGrade = (select grade from "StudentSelection" where "studentId" = studentId and "sectionId" = sectionId);
    if (studentGrade is not null) then raise exception 'Can''t drop a course with grade';
    else
        delete from "StudentSelection" where "studentId" = studentId and "sectionId" = sectionId;
        update "CourseSection" set "leftCapacity" = "leftCapacity"   1 where "sectionId" = sectionId;
    end if;
END
$$ LANGUAGE 'plpgsql';

Here is my original code. The whole process is for a student to drop a course section without a grade on it, and add number one to the left capacity of the course section.

where "studentId" = studentId and "sectionId" = sectionId

But the where statement above is actually executed twice including the first time to query the student grade (to find out whether it is null) and the second time to delete.

Is it possible to actually query once in other more efficient ways?

CodePudding user response：

You can simply execute the DELETE command and then check what happened so you don't have to ascertain first that a record is present:

CREATE FUNCTION dropCourse (studId integer, sectId integer) RETURNS void AS
$$
BEGIN
    DELETE FROM "StudentSelection"
    WHERE "studentId" = studId
      AND "sectionId" = sectId
      AND grade IS NULL;

    IF NOT FOUND THEN -- No record was deleted so grade was probably NOT NULL
        RAISE EXCEPTION 'Can''t drop a course with grade';
    END IF;

    -- If the function reaches here, update the capacity
    UPDATE "CourseSection" SET "leftCapacity" = "leftCapacity"   1
    WHERE "sectionId" = sectId;
END;
$$ LANGUAGE 'plpgsql';

In general, you should REALLY avoid having functions parameters that are identical to table column names.

CodePudding user response：

I was writing this as Patrick posted. We came up with pretty much the same solution. I highly recommend losing the camel case for your identifiers. Such a nightmare for anyone maintaining your DB later to have to quote all identifiers. Use all lowercase or snake case and noooo spaces. You'll thank me later.

CREATE OR REPLACE FUNCTION dropcourse(studentID INTEGER, sectionID INTEGER)
RETURNS VOID
AS $$
DECLARE
    studentGrade VARCHAR;
BEGIN
    DELETE FROM "StudentSelection"
     WHERE ss."studentId" = studentID
       AND ss."sectionId" = sectionID
       AND ss.grade IS NOT NULL
    ;
    IF FOUND
    THEN
        UPDATE "CourseSection"
           SET "leftCapacity" = "leftCapacity"   1
         WHERE "sectionId" = sectionID
        ;
    ELSE
        RAISE EXCEPTION 'Can''t drop a course with a grade';
    END IF;
    RETURN;
END;
$$ LANGUAGE PLPGSQL;