0

I am new to Postgresql/coding in general, so please forgive me for this question. Yes, I have searched SO for the answer, as this is seemingly a common error message, but I keep coming up empty-handed.

I am having issues trying to create a table and procedure to track university students and the number of courses they have taken to verify minimum graduation requirements. I know a function would work better for this, but I have specifically been asked to create a procedure, so here we are.

First, I created a table (student_course_nums), then wrote a procedure that accepts a student ID as input (stuCourseNumsProc).

The procedure is supposed to calculate the total number of course sections attended by that student, and adds a tuple to the student_course_nums table consisting of the student’s ID number, name, and total courses attended - attributes named: ID, name, and tot_courses.

If the student already has an entry in the table, then the procedure would make sure the total number of courses attended in the student_course_nums table was up-to-date. Finally I need to call the procedure. This is really where I'm having issues.

This is my database schema:

course (course_id, title, dept_name, credits)
student (id, name, dept_name, tot_cred)
takes (id, course_id, sec_id, semester, year, grade)

Here is my code:

CREATE TABLE student_course_nums (ID VARCHAR(5), name VARCHAR(20), tot_courses INTEGER);

    CREATE OR REPLACE PROCEDURE stuCourseNumsProc (IN student_id VARCHAR(5), INOUT ID VARCHAR(5), 
                                                            name VARCHAR(20), tot_courses INTEGER)  
    LANGUAGE plpgsql
    AS
    $$
        DECLARE
            name VARCHAR(20) := '';
            course_count INTEGER := 0;
        BEGIN
        -- determine total courses - course_count
            SELECT COUNT(t.course_id) INTO course_count
            FROM takes AS t INNER JOIN student AS s ON t.ID = s.ID
            WHERE t.ID = stuCourseNumsProc.s_ID;

        -- determine student name - into name
            SELECT s.name INTO name
            FROM student AS s
            WHERE s.ID = stuCourseNumsProc.s_ID;

            IF EXISTS (SELECT ID
                    FROM student_course_nums
                    WHERE ID = stuCourseNumsProc.s_ID) 
            THEN -- update record
                UPDATE student_course_nums
                SET tot_courses = course_count
                WHERE ID = stuCourseNumsProc.s_ID;
            ELSE -- insert new tuple
                INSERT INTO student_course_nums (ID, name, tot_courses)
                VALUES (stuCourseNumsProc.s_ID, name, course_count);
            END IF;
        END;
    $$

CALL stuCourseNumsProc('89765');

I run the procedure first, and then I get the following error when trying to call my procedure:

ERROR:  procedure stucoursenumsproc(unknown) does not exist
LINE 1: CALL stuCourseNumsProc('89765');
             ^
HINT:  No procedure matches the given name and argument types. You might need to add explicit type casts.
SQL state: 42883
Character: 6

I see the 'stuCourseNumsProc' under procedures in my database schema, so I am struggling to figure out why I am getting this error. I'm not sure if my code is correct for the rest of my problem, but I was hoping to troubleshoot that after getting the code to work.

Any suggestions/tips would be very greatly appreciated. Thanks in advance!

CleverCacti
  • 1
  • 1
  • 1
    Your procedure requires four parameters, you are only passing one. –  Oct 15 '22 at 15:05
  • @a_horse_with_no_name My input is one parameter (student_id) and I have 3 IN/OUT parameters. I was under the impression to "Use the IN mode if you want to pass a value to the function. Use the OUT mode if you want to return a value from a function. Use the INOUT mode when you want to pass in an initial value, update the value in the function, and return it updated value back." I also just tried this " CALL stuCourseNumsProc('89765', 0, 0, 0);" and I got the same error. – CleverCacti Oct 15 '22 at 15:15
  • The procedure signature is `stuCourseNumsProc(varchar, varchar, varchar, int)` and this `stuCourseNumsProc('89765', 0, 0, 0)` is `varchar, int, int, int` they don't match. Functions/procedures can be overloaded, have same name but different signatures, in Postgres. This means you need to be attentive to how you supply the arguments. – Adrian Klaver Oct 15 '22 at 15:32
  • @AdrianKlaver Thank you for the detailed explanation. `stuCourseNumsProc('89765', '0', '0', 0)` worked for me. – CleverCacti Oct 15 '22 at 17:28
  • Further inspection revealed the following: 1) Not sure why you have the `name` argument in any case as you set it to `''` in the `DECLARE` section. FYI, you don't need to do the `:= ''`. 2) The `ID` argument is not double quoted anywhere so it will be folded to `id` in use. 3) `tot_courses` is being used in two contexts as a field name in the `UPDATE` abd `INSERT` and as a value. That is going cause conflict. Also as a value it is redundant and not needed as it is equal to the calculated `course_count`. – Adrian Klaver Oct 15 '22 at 17:45
  • @AdrianKlaver Thank you so much for taking a deeper look at this for me! As a newbie to postgresql, I really appreciate your detailed feedback. – CleverCacti Oct 16 '22 at 12:23
  • You have another misconception in the procedure definition. Your statement "I have 3 IN/OUT parameters" is incorrect; you have 1 in/out and 3 in parameters. This is because the *argmode* (IN/OUT/INOUT) applies to the following parameter only not to all following parameters, and if not specified defaults to IN. Thus the parameters `name` and `tot_courses` are IN parameters by default. (See [Create Procedure](https://www.postgresql.org/docs/14/sql-createprocedure.html) documentation and [demo](https://dbfiddle.uk/2X45Nkv3)). – Belayer Oct 16 '22 at 20:15

0 Answers0