CODEWITHSUNDEEP
postgresqlstored-procedures
Nutan
Nutan

Reputation: 1375

Call stored procedure in loop for different values of the argument POSTGRESQL

I have a stored procedure which accepts single varchar argument, I want to call this for each distinct id present in my table.

CREATE OR REPLACE FUNCTION updateData(i varchar(12)) RETURNS VOID AS $$
    BEGIN
      update employee set employee_number = nextval('d_employeeNumber_seq') where employee.age > 25 and employee.deptId = i;
     ALTER SEQUENCE d_employeeNumber_seq RESTART WITH 1;   
    END;
    $$ LANGUAGE plpgsql;

Now I want to call this updateData() for each department

something like select updateData() in (select distinct deptId from employee) [This isnt the correct syntax but i want to use something of this sort]

Upvotes: 0

Views: 1473

Answers (1)

JGH
JGH

Reputation: 17906

You would need a subquery and to pass the desired parameter:

select updateData(deptID)
from ( select distinct deptId  from employee) a

Upvotes: 2

Related Questions