Making bulk data changes using server-side procedures with transactions
In some cases, you’ll need to make bulk changes to your data. In many cases, you need to scroll through the data making changes according to a complex set of rules. You have a few choices in that case:
- Write a single SQL statement that can do everything
- Open a cursor and read the rows out, then make changes with a client-side program
- Write a procedure that uses a cursor to read the rows and make changes using server-side SQL
Writing a single SQL statement that does everything is sometimes possible, but, if you need to do more than just UPDATE then it becomes difficult very quickly. The main difficulty is that the SQL statement isn't restartable, so, if you need to interrupt it then you lose all of your work.
Reading all the rows back to a client-side program can be very slow – if you need to write this kind of program, it is better to do it all on the database server.
How to do it…
We're going to write a Procedure in PL/pgSQL...
