CODEWITHSUNDEEP
sqlpostgresqlstored-procedures
M. Barbieri
M. Barbieri

Reputation: 542

stored procedure in postgresql returns duplicate columns

I am trying to make a stored procedure to make a schedule of students' schedules depending on what classes they are taking (obviously). However, I believe the joins are causing the stored procedure to return double the rows.


    CREATE OR REPLACE FUNCTION generateStudentSchedule(TEXT, REFCURSOR) RETURNS refcursor AS
    $$
    DECLARE
      tfname TEXT       := $1;
      ref    REFCURSOR  := $2;
    BEGIN
      OPEN ref FOR
        SELECT c.courseName, l.day, l.startTime, l.endTime 
        FROM ClassEvent l 
        JOIN Sections s ON l.courseID = s.courseID
        JOIN Courses c ON l.courseID = c.courseID
        JOIN Enrollment e ON e.courseID = l.courseID
        WHERE (e.studentID IN (SELECT studentID 
                  FROM Students 
                  WHERE studentID IN (SELECT pid 
                              FROM People 
                              WHERE fname = tfname))
        AND l.sectionNumber = e.sectionNumber)
        ORDER BY 
        CASE
          WHEN l.day = 'Monday' THEN 1
          WHEN l.day = 'Tuesday' THEN 2
          WHEN l.day = 'Wednesday' THEN 3
          WHEN l.day = 'Thursday' THEN 4
          WHEN l.day = 'Friday' THEN 5
        END ASC, l.starttime;
      RETURN ref;
    END
    $$ 
    LANGUAGE plpgsql;

This is the output: output

Any suggestions on how to fix this?

Here is a snapshot of my database's tables 


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

Upvotes: 1

Views: 429

Answers (1)

jwatts1980
jwatts1980

Reputation: 7346

I know of 2 options that can help you. You can use DISTINCT or GROUP BY.

DISTINCT would look like:

SELECT DISTINCT c.courseName, l.day, l.startTime, l.endTime

This makes sure that only unique rows are returned.

The GROUP BY option would look like:

AND l.sectionNumber = e.sectionNumber)
GROUP BY c.courseName, l.day, l.startTime, l.endTime 
ORDER BY

This also would make sure that only 1 row of each like group was returned.

With DISTINCT you can easily add more select columns without making other changes. DISTINCT will make sure the entire row of columns is distinct.

With GROUP BY, each time you change the select columns, you would also need to update the GROUP BY clause.

From a performance standpoint, GROUP BY tends to be preferred when working over multiple columns. DISTINCT seems to be more common when getting all distinct values of a single column.

Upvotes: 2

Related Questions