Functions and Cursors in Postgres

Question

I'm designing a function that utilizes a couple cursors and I am new to Postgres so I am unaware of any small syntax issues I'm making. I'm using SQLFIDDLE to implement this and the error I am receiving is:

Schema Creation Failed: ERROR: syntax error at or near "+":

And the function looks as follows,

CREATE OR REPLACE FUNCTION updateSalary() RETURNS VOID AS
$BODY$
DECLARE
  DECLARE sum INT DEFAULT 0;
  emp CURSOR FOR SELECT Dno, Salary FROM Employee;
  emp_row RECORD;
  dep CURSOR FOR SELECT Dno FROM Department;
  dep_row RECORD;
BEGIN
  open dep;
  LOOP
    FETCH dep into dep_row;
      exit when dep_row = null;
    open emp;
    LOOP
      FETCH emp into emp_row;
        exit when emp_row = null;
      if emp_row.Dno =dep_row.Dno then
        SET sum = sum + emp_row.salary;
      end if;

    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;

I am sure there are a ton of other errors in here so if any jump out at you please let me know and save me from the wonderful joys of debugging postgresql.

Here is the Fiddle http://sqlfiddle.com/#!15/a1a45/3

For some reason, the function isn't saved into that Fiddle. I think in order for a Fiddle to save it must be able to be built.

Post the fiddle you are using.

Rahul
– Rahul

2014-04-30 17:42:28 +00:00
Commented Apr 30, 2014 at 17:42 — Rahul
– Rahul, Commented Apr 30, 2014 at 17:42
@Rahul I've included a link to the fiddle at the bottom.

Talen Kylon
– Talen Kylon

2014-04-30 17:43:40 +00:00
Commented Apr 30, 2014 at 17:43 — Talen Kylon
– Talen Kylon, Commented Apr 30, 2014 at 17:43

Rahul · Accepted Answer · 2014-05-01 01:11:37Z

3

There are few points to mention as below

No need of cursors ... you can simply do a join between the table employee and department.

With that below is what you should try

CREATE OR REPLACE FUNCTION updateSalary() RETURNS VOID 
AS
$BODY$
  DECLARE sum INT DEFAULT 0;

select sum = sum(emp.salary) 
FROM Employee emp
join Department dept
on emp.Dno = dept.Dno;

    UPDATE department as d SET total_sal = sum 
    FROM employee as e    
    WHERE d.dno = e.dno;
$BODY$
  LANGUAGE plpgsql;

EDIT:

Modified your cursor definition a bit .. give it a try

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 <-- change here
    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;

You got that error cause data type was not mentioned. what I have used is known as Parameterized Cursors.

edited May 1, 2014 at 1:11

answered Apr 30, 2014 at 17:56

Rahul

78.1k14 gold badges80 silver badges132 bronze badges

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

5 Comments

Talen Kylon Over a year ago

I appreciate your input. I unfortunately do not have a choice but to use cursors as this is an assignment to learn about Stored Procedures and Cursors.

Rahul Over a year ago

@FatalProphet, ohh!!! you should have mentioned it earlier. need to recheck again.

Talen Kylon Over a year ago

My apologies, I should've known better. It seems to be a collective agreement that cursors are the devil and should be avoided at all costs. It makes me wonder why even both teaching it in the classroom.

Rahul Over a year ago

@FatalProphet, it's still tought in classroom; so that you at least know the concept but in practial cursor should be avoided and replaced by simple join for performance reason. Anyways, see edited answer and give it a try.

Talen Kylon Over a year ago

When I try to build the schema with your updated function, I get the following error Schema Creation Failed: ERROR: missing data type declaration at or near ")":

Collectives™ on Stack Overflow

Functions and Cursors in Postgres

1 Answer 1

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related