In this post, I’ll explain you how to write a postgresql function that iterates over a set of rows. Sometimes there is a need in my projects where I have to loop over some rows in a table. Select certain columns and take decision on them.
Let’s begin with it. I will strongly recommend you to play with sample tables and data. Don’t try this directly on your production database :).
Create two tables
- employee (id INTEGER, name TEXT, department TEXT)
- reward (id serial PRIMARY KEY, employee_id INTEGER REFERENCES employee(id))
CREATE OR REPLACE FUNCTION loop_over_rows() RETURNS BOOLEAN AS $$ DECLARE rec RECORD; BEGIN FOR rec IN (SELECT id, name, department FROM employee) LOOP RAISE INFO '%', rec; INSERT INTO reward VALUES(DEFAULT, rec.id) WHERE rec.name = 'viju'; RAISE INFO 'DONE'; END LOOP; RETURN TRUE; END; $$ LANGUAGE PLPGSQL;
Above function iterates over records of employee table. It inserts employee_id in reward table where the name of employee is viju. So, you can see that based on value of one table, we are updating another table.
I have kept this very simple to explain in easy way. On the basis of this, you can design your function to do more complex tasks.
I will leave you try this out. Any suggestion, question is mot welcome.
Have a great day!