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

Question

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]

what you have is a function and not a stored procedure. Syntax to invoke stored procedure is different. — Sahil Chimnani
– Sahil Chimnani, Commented Aug 8, 2022 at 10:01

JGH · Accepted Answer · 2018-02-14 12:47:25Z

2

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

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

answered Feb 14, 2018 at 12:47

JGH

18.2k5 gold badges40 silver badges57 bronze badges

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

1 Comment

Nutan Over a year ago

Thanks, that was pretty straight forward.

Collectives™ on Stack Overflow

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

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related