Logic Issues with Procedure/Cursor in Postgres

Question

I am using SQLFiddle for an assignment to learn about procedures and cursors. I know that cursors are not the best thing to use and there are better ways to accomplish what I am trying to do but I am required to use procedures and cursors.

I want the procedure to ultimately update the Total_sal of each department based on the employees that belong to that department.

Right now it is not doing that, and I don't really have enough knowledge or experience with Postgres to properly debug it.

The Total_sal's of the departments after the updateSalary() procedure should look like:

dept_no    total_sal

10          7000
20          7000
30          6000

Here is the Fiddle I am working with: http://sqlfiddle.com/#!15/140d5/3

And the updatSalary procedure looks like:

CREATE OR REPLACE FUNCTION updateSalary() RETURNS VOID AS
$BODY$
  DECLARE 
  sum INTEGER := 0;
  dep CURSOR FOR SELECT Dno FROM Department;
  dep_row Department%ROWTYPE;
  emp CURSOR(dept_Dno INTEGER) FOR
    SELECT Dno, Salary FROM Employee WHERE Dno = dept_Dno;
  emp_row Employee%ROWTYPE;
BEGIN
  open dep;
  LOOP
    FETCH dep into dep_row;
      exit when NOT FOUND;
    open emp( dep_row.Dno );
    LOOP
      FETCH emp into emp_row;
        exit when NOT FOUND;
         sum := sum + emp_row.salary;
     END LOOP;
    UPDATE department SET total_sal = sum WHERE department.dno = emp_row.dno;
    close emp;
    sum := 0;
  END LOOP;
  close dep;
END;
$BODY$
  LANGUAGE plpgsql;

klin · Accepted Answer · 2014-05-01 02:22:43Z

1

You should use 'FOR ... IN ... LOOP' statement (Cursors in documentation, see 40.7.4.)

CREATE OR REPLACE FUNCTION updateSalary() RETURNS VOID AS
$BODY$
  DECLARE 
  total INTEGER := 0;
  dep CURSOR FOR SELECT Dno FROM Department;
  dep_row Department%ROWTYPE;
  emp CURSOR(dept_Dno INTEGER) FOR
    SELECT Dno, Salary FROM Employee WHERE Dno = dept_Dno;
  emp_row Employee%ROWTYPE;
BEGIN
    FOR dep_row IN dep
    LOOP
        FOR emp_row IN emp( dep_row.Dno )
        LOOP
            total := total + emp_row.salary;
        END LOOP;
        UPDATE department SET total_sal = total WHERE department.dno = dep_row.dno;
        total := 0;
    END LOOP;
END;
$BODY$ LANGUAGE plpgsql;

(sum is a name of aggregate function; better not to use as variable name)

answered May 1, 2014 at 2:22

klin

123k15 gold badges240 silver badges262 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Talen Kylon Over a year ago

Hello again, I have tried your solution and it does indeed work. I was wondering if you could perhaps spot what was wrong with my implementation, could it be that using the sum variable name was messing things up?

klin Over a year ago

No, variable can (but shouldn't) have a name 'sum'. Problem was in WHERE department.dno = emp_row.dno; should be WHERE department.dno = dep_row.dno; I'm not sure if that was the only error because I had rewrote the code a little before I found that.

Collectives™ on Stack Overflow

Logic Issues with Procedure/Cursor in Postgres

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related