How to check if the output of two SELECT statements are equal, greater than, or less than in a stored procedure?

Question

I made a database storing classes students and professors in order to make a registration system. However, I am trying to make a stored procedure that checks if the classSize attribute is greater than or equal to the classSizeCap attribute in the 'Sections' table. However, I am running into a problem. PostgreSQL doesn't recognize, my CASE or IF statements. How should I fix this?

    -- WILL SHOW WHAT STUDENTS NEED OVERRIDES FOR WHICH CLASS --
    CREATE OR REPLACE FUNCTION startOverrides(REFCURSOR) RETURNS refcursor AS
    $$
    DECLARE
        class_size    INT        := (PERFORM Sections.classSize 
                                     FROM Sections 
                                     INNER JOIN Enrollment ON Enrollment.courseID = Sections.courseID 
                                     WHERE Enrollment.sectionNumber = Sections.sectionNumber);
        class_members INT        := (PERFORM students_enrolled.courseName, COUNT(*) 
                                    FROM students_enrolled
                                    GROUP BY students_enrolled.courseName, COUNT(*));
        overrides     REFCURSOR  := $1;
    BEGIN
        OPEN overrides FOR
            CASE WHEN  class_size >= class_members 
            THEN SELECT students_enrolled.fname, students_enrolled.lname, students_enrolled.courseName, SUM(Sections.classSizeCap - Sections.classSize) AS overrides
                 FROM students_enrolled
                 INNER JOIN Sections ON Sections.courseID = students_enrolled.courseID
                                     AND Sections.sectionNumber = students_enrolled.sectionNumber
                 GROUP BY students_enrolled.fname, students_enrolled.lname, students_enrolled.courseName, SUM(Sections.classSizeCap - Sections.classSize)
            END IF;
        RETURN overrids;
    END
    $$ 
    LANGUAGE plpgsql;
    SELECT students_enrolled('overrides');
    FETCH ALL FROM overrides;

My Error:

ERROR:  syntax error at or near "CASE"
LINE 8:   CASE WHEN (SELECT Sections.classSize 
          ^
********** Error **********

ERROR: syntax error at or near "CASE"
SQL state: 42601
Character: 151

This is the view I am primarily using:

    CREATE OR REPLACE VIEW students_enrolled AS
        SELECT People.fname, People.lname, Courses.courseID, Courses.courseName, Students.creditsEarned
        FROM Enrollment 
        INNER JOIN People ON Enrollment.studentID = People.pid
        INNER JOIN Students ON Enrollment.studentID = Students.studentID
        INNER JOIN Courses ON Enrollment.courseID = Courses.courseID
        GROUP BY People.fname, People.lname, Courses.courseID, Courses.courseName, Students.creditsEarned;

This is a snapshot of my database:

    
    -- HOLDS A SPECIFIC COURSE WITHOUT THE INSTANCES OF THE CLASS --
    CREATE TABLE Courses (
        courseID      SERIAL      UNIQUE NOT NULL,
        department    TEXT               NOT NULL,
        courseNumber  VARCHAR(10)        NOT NULL,
        courseName    TEXT        UNIQUE NOT NULL,
        credits       INT                NOT NULL,
        PRIMARY KEY(courseID)
    );
    
    -- HOLDS A SPECIFIC COURSE OFFERINGS --
    CREATE TABLE ClassesAvailable (
        courseID INT  NOT NULL,
        year     INT  NOT NULL,
        term     TEXT NOT NULL,
        CHECK(term = 'Fall' OR term = 'Winter' OR term = 'Spring' OR term = 'Summer'),
        PRIMARY KEY(courseID, year, term)
    );
    
    -- PEOPLE SUPERTYPE --
    CREATE TABLE People (
        pid   SERIAL            UNIQUE NOT NULL,
        fname TEXT                     NOT NULL,
        lname TEXT                     NOT NULL,
        PRIMARY KEY(pid)
    );
    
    -- HOLDS THE DIFFERENT PROFESSORS TEACHING AT THE SCHOOL --
    -- SUBTYPE OF PEOPLE --
    CREATE TABLE Professors (
        professorID  INT  UNIQUE NOT NULL,
        status       TEXT        NOT NULL,
        CHECK(status = 'Full-Time' OR status = 'Part-time'),
        PRIMARY KEY(professorID),
        FOREIGN KEY(professorID) REFERENCES People(pid)
    );
    
    -- HOLDS THE SPECIFIC INSTANCES OF THE CLASS DEPENDING ON THE YEAR AND TERM --
    CREATE TABLE Sections (
        courseID      INT          NOT NULL,
        year          INT          NOT NULL,
        term          TEXT         NOT NULL, 
        sectionNumber INT          NOT NULL,
        startDate     DATE         NOT NULL,
        endDate       DATE         NOT NULL,
        crn           INT          NOT NULL,
        classSize     INT          NOT NULL,
        classSizecap  INT          NOT NULL,
        CHECK(term = 'Fall' OR term = 'Winter' OR term = 'Spring' OR term = 'Summer'),
        PRIMARY KEY(courseID, year, term, sectionNumber),
        FOREIGN KEY(courseID, year, term) REFERENCES ClassesAvailable(courseID, year, term)
    );
    
    -- HOLDS THE EVENT OF THE CLASS --
    -- A CLASS MAY HAVE DIFFERENT DAYS ON WHICH --
    -- THEY MEET ON, SO THIS ALLOWS A CERTAIN --
    -- SECTION TO HAVE SEVERAL DAYS WITHOUT CONFLICT --
    CREATE TABLE ClassEvent (
        professorID   INT          NOT NULL,
        courseID      INT          NOT NULL,
        year          INT          NOT NULL,
        term          TEXT         NOT NULL,
        sectionNumber INT          NOT NULL,
        day           TEXT, 
        startTime     TIME,
        endTime       TIME,
        location      TEXT,
        campus        TEXT,
        CHECK(day = 'Monday' OR day = 'Tuesday' OR day = 'Wednesday' OR day = 'Thursday' OR day = 'Friday' OR day = 'Saturday' OR day = 'Sunday' OR day IS NULL),
        CHECK(term = 'Fall' OR term = 'Winter' OR term = 'Spring' OR term = 'Summer'),
        CHECK(campus = 'Main' OR campus = 'Online' OR campus = 'Italy'),
        PRIMARY KEY(professorID, courseID, year, term, sectionNumber, day, startTime, endTime),
        FOREIGN KEY(professorID) REFERENCES Professors(professorID),
        FOREIGN KEY(courseID, year, term, sectionNumber) REFERENCES Sections(courseID, year, term, sectionNumber)
    );
    
    -- GENERATES THE PREREQUESITES --
    CREATE TABLE Prerequisites (
        courseID      INT        NOT NULL,
        year          INT        NOT NULL,
        term          TEXT       NOT NULL,
        prereqID      INT        NOT NULL,
        CHECK(term = 'Fall' OR term = 'Winter' OR term = 'Spring' OR term = 'Summer'),
        PRIMARY KEY(courseID, year, term, prereqID),
        FOREIGN KEY(courseID, year, term) REFERENCES ClassesAvailable(courseID, year, term),
        FOREIGN KEY(prereqID) REFERENCES Courses(courseID)
    );
    
    
    -- HOLDS THE STUDENTS THAT WILL BE TAKING THE CLASSES --
    -- SUBTYPE OF PEOPLE --
    CREATE TABLE Students (
        studentID     INT  UNIQUE NOT NULL,
        gradYear      INT         NOT NULL,
        creditsEarned INT         NOT NULL,
        PRIMARY KEY(studentID),
        FOREIGN KEY(studentID) REFERENCES People(pid)
    );
    
    -- HOLDS A CLASS RECORD FOR STUDENTS (AND POSSIBLY PROFESSORS) --
    CREATE TABLE Enrollment (
        studentID     INT         NOT NULL,
        courseID      INT         NOT NULL,
        year          INT         NOT NULL,
        term          TEXT        NOT NULL,
        sectionNumber INT         NOT NULL,
        CHECK(term = 'Fall' OR term = 'Winter' OR term = 'Spring' OR term = 'Summer'),
        PRIMARY KEY(studentID, courseID, year, term, sectionNumber),
        FOREIGN KEY(studentID) REFERENCES Students(studentID),
        FOREIGN KEY(courseID, year, term, sectionNumber) REFERENCES Sections(courseID, year, term, sectionNumber)
    );
    
    -- HOLDS THE DIFFERENT DEGREES THAT CAN BE ATTAINED AT THE COLLEGE/UNIVERSITY --
    CREATE TABLE Degrees (
        degreeID     SERIAL       UNIQUE NOT NULL,
        degreeName   TEXT                NOT NULL,
        degreeType   TEXT                NOT NULL,
        degDepartment VARCHAR(4)         NOT NULL,
        CHECK(degreeType = 'Major' OR degreeType = 'Minor' OR degreeType = 'Masters'),
        PRIMARY KEY(degreeID)
    );
    
    -- HOLDS THE CLASSES THAT WILL MAKE UP A DEGREE --
    CREATE TABLE DegreeReq (
        degreeID INT REFERENCES Degrees(degreeID) NOT NULL,
        courseID INT REFERENCES Courses(courseID) NOT NULL,
        PRIMARY KEY(degreeID, courseID)
    );
    
    -- HOLDS THE INSTANCE OF A DEGREE FOR A CERTAIN STUDENT --
    -- FOR EXAMPLE: A STUDENT CAN HAVE A MAJOR AND A MINOR --
    -- SO HE/SHE CAN STORE THEM SEPARATELY --
    CREATE TABLE DegreeInstance (
        degreeID        INT  REFERENCES Degrees(degreeID)   UNIQUE NOT NULL,
        studentID       INT  REFERENCES Students(studentID) UNIQUE NOT NULL,
        startDate       DATE                                       NOT NULL,
        endDate         DATE                                       NOT NULL,
        creditsRequired INT                                        NOT NULL, 
        PRIMARY KEY(degreeID, studentID)
    );
    
    -- HOLDS ALL THE RATE MY PROFESSOR STATS --
    CREATE TABLE Rating (
        professorID      INT        UNIQUE NOT NULL,
        rmpID            BIGINT     UNIQUE NOT NULL,
        avgRating        FLOAT             NOT NULL,
        avgHelpfulness   FLOAT             NOT NULL,
        avgClarity       FLOAT             NOT NULL,
        avgEasiness      FLOAT             NOT NULL,
        PRIMARY KEY(professorID, rmpID),
        FOREIGN KEY(professorID) REFERENCES Professors(professorID)
    );
    
    -- HOLDS CLASS RECORDS FOR STUDENTS --
    CREATE TABLE ClassRecord (
        studentID  INT  NOT NULL,
        courseID   INT  NOT NULL,
        year       INT  NOT NULL,
        term       TEXT NOT NULL,
        grade      TEXT NOT NULL,
        CHECK(grade = 'A' OR grade = 'A-' OR grade = 'B+' OR grade = 'B' OR grade = 'B-' OR grade = 'C+' OR grade = 'C' OR grade = 'C-' OR grade = 'D+' OR grade = 'D' OR grade = 'D-' OR grade = 'F' OR grade = 'P'),
        PRIMARY KEY(studentID, courseID, year, term, grade),
        FOREIGN KEY(courseID, year, term) REFERENCES ClassesAvailable(courseID, year, term),
        FOREIGN KEY(studentID) REFERENCES Students(studentID)
    );

Any suggestions?

Answer 1

Syntax:

OPEN unbound_cursor FOR query;

CASE ... is not a query. Start your query with SELECT and place your predicate into WHERE clause of SELECT.

Answer 2

So instead of comparing the class sizes attributes, I realized it's better to just make a query that states who is enrolled in which class, display those people for a certain class in order of credits, and then do a limit starting after the classSizeCap value.

This can be done after the LIMIT, you can assign a limit amount and then use OFFSET to indicate where you want to start. Then after that you can just call the function using the class and section that you want.

    -- WILL SHOW WHAT STUDENTS NEED OVERRIDES FOR WHICH CLASS --
    CREATE OR REPLACE FUNCTION startOverrides(INT, INT, REFCURSOR) RETURNS refcursor AS
    $$
    DECLARE
        class         INT        := $1;
        section       INT        := $2;
        class_size    INT        := (SELECT classSizeCap FROM Sections WHERE courseID = class AND sectionNumber = section);
        overrides     REFCURSOR  := $3;
    BEGIN
        OPEN overrides FOR
            SELECT * FROM students_enrolled WHERE (courseID = class AND sectionNumber = section) ORDER BY creditsEarned DESC LIMIT 1844674 OFFSET class_size;
        RETURN overrides;
    END
    $$ 
    LANGUAGE plpgsql;

    SELECT startOverrides(222, 112, 'overrides');
    FETCH ALL FROM overrides;

Answer 3

You can use INTO to assign a result to a variable.

That way you may be able to assign both select statement to a variable and compare them in the CASE statement.

-- WILL SHOW WHAT STUDENTS NEED OVERRIDES FOR WHICH CLASS --
CREATE OR REPLACE FUNCTION startOverrides(REFCURSOR) RETURNS refcursor AS
$$
DECLARE
    class_size    INT := 0;
    class_members INT := 0;
    overrides     REFCURSOR  := $1;
BEGIN
    SELECT students_enrolled.courseName, COUNT(*)
    FROM students_enrolled
    GROUP BY students_enrolled.courseName, COUNT(*) INTO class_members;

    SELECT Sections.classSize
    FROM Sections
    INNER JOIN Enrollment ON Enrollment.courseID = Sections.courseID
    WHERE Enrollment.sectionNumber = Sections.sectionNumber
    INTO class_members;

    IF class_size >= class_members THEN
    OPEN overrides FOR
            SELECT students_enrolled.fname, students_enrolled.lname, students_enrolled.courseName, SUM(Sections.classSizeCap - Sections.classSize) AS overrides
             FROM students_enrolled
             INNER JOIN Sections ON Sections.courseID = students_enrolled.courseID
                                 AND Sections.sectionNumber = students_enrolled.sectionNumber
             GROUP BY students_enrolled.fname, students_enrolled.lname, students_enrolled.courseName, SUM(Sections.classSizeCap - Sections.classSize)
             ;
    END IF;
    RETURN overrids;
END
$$
LANGUAGE plpgsql;