CODEWITHSUNDEEP
postgresqlplpgsql
Talen Kylon
Talen Kylon

Reputation: 1958

Syntax Issue with Postgres Function

I am having trouble debugging some syntax issues with my postgresql function. The error doesn't indicate a line that the issue is on and I am too new to Postgres to recognize what's wrong. I am using SQLFiddle for testing. SQLFiddle link is http://sqlfiddle.com/#!15/266ef

Function I am using

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) CURSOR 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;
         SET sum := sum + emp_row.salary;
     END LOOP;
    UPDATE department SET total_sal = sum WHERE department.dno = emp_row.dno;
    close emp;
    SET sum := 0;
  END LOOP;
  close dep;
END;
$BODY$
  LANGUAGE plpgsql;

Error I am receiving

Schema Creation Failed: ERROR: missing data type declaration at or near ")":

Upvotes: 0

Views: 508

Answers (1)

klin
klin

Reputation: 121504

Cursor argument must have a type declared (and remove second word 'cursor'):

...
emp CURSOR(dept_Dno integer) FOR 
    SELECT Dno, Salary FROM Employee WHERE Dno = dept_Dno;
...

Assignments without keyword 'set':

 sum := sum + emp_row.salary;

Upvotes: 2

Related Questions