Re: UPDATE pg_catalog.pg_proc.prosrc OK?

While I understand some of the SQL commands affecting pg_catalog have side effects, such as CREATE DATABASE, others seems to lack side effects.

To use CREATE OR REPLACE FUNCTION, I would have to assemble SQL from the data in pg_proc.* for each function, which is quite a complex task (e.g., pg_dump does this, and it's far from trivial, due to differences between different versions etc).

I understand it's not a good idea to read/write pg_proc between different databases, but this is not my case.

My plan:

1. Take snapshot of pg_catalog.pg_proc.*

2. Update existing/install new source code of functions

3. Monitor how the live system behaves (might take 30 minutes or something like that)

4. If problems occurr, revent to the old state by removing the new pg_proc entries and restoring the modified existing ones.

Problems are not expected since the new code has been tested locally in a database with identical schema, but I've learned you can never be one hundred percent sure everything always works.

Until now, I've been creating a "revent .sql-file" manually, which drops the new functions and restores the replaced functions with their old source code.

This is quite time consuming and of course prone to human errors.

Alternative approach: It would be good if pg_dump could split a plaintext schema dump into separate files. That would allow you to only restore the functions, which would solve part of my problem, but would still cause problems for functions where you alter the arguments, in which case the existing function with the same name needs to be dropped first, before creating the new function with different arguments. For such scenarios, I would need to drop the new functions first, before restoring the old functions from the dump.