PostgreSQL 11 Administration Cookbook

How to do it…

We're going to write a Procedure in PL/pgSQL. A Procedure is similar to a Function, except that it doesn't return any value or object. We use a Procedure because it allows you to run multiple server-side transactions. By using procedures in this way, we are able to break the problem down into a set of smaller transactions that cause less of a problem with database bloat and long running transactions.

As an example, let's consider a case where we need to update all employees with the A2 job grade, giving each person a 2% pay rise:

CREATE PROCEDURE annual_pay_rise ()LANGUAGE plpgsql AS $$DECLAREc CURSOR FORSELECT * FROM employee    WHERE job_code = 'A2';BEGINFOR r IN c LOOPUPDATE employeeSET salary = salary * 1.02WHERE empid = r.empid; ...