Syntax Issue with Postgres Function

Question

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 ")":

Please link to the SQLFiddle in question.

Craig Ringer
– Craig Ringer

2014-05-01 00:51:56 +00:00
Commented May 1, 2014 at 0:51 — Craig Ringer
– Craig Ringer, Commented May 1, 2014 at 0:51
@CraigRinger I've included the link

Talen Kylon
– Talen Kylon

2014-05-01 00:54:13 +00:00
Commented May 1, 2014 at 0:54 — Talen Kylon
– Talen Kylon, Commented May 1, 2014 at 0:54

klin · Accepted Answer · 2014-05-01 00:55:27Z

2

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;

answered May 1, 2014 at 0:55

klin

123k15 gold badges240 silver badges262 bronze badges

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

6 Comments

Talen Kylon Over a year ago

I've included your changes and am now getting this error Schema Creation Failed: ERROR: syntax error at or near ":=":

klin Over a year ago

Probably you did not remove set in the second assignment: 'sum := 0;'

Rahul Over a year ago

@FatalProphet, I have already edited the other post (related) ... give it a try now. should work fine.

Talen Kylon Over a year ago

Thanks folks, one more question. How do I call this procedure now ?

klin Over a year ago

'select updateSalary();' in plain SQL; or 'perform updateSalary();' inside a function.

|

Collectives™ on Stack Overflow

Syntax Issue with Postgres Function

1 Answer 1

6 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

6 Comments

Your Answer

Sign up or log in

Post as a guest

Related