For now, the following works for us:

- For plpgsql functions that are required by app queries, we just roll them out when we write them / when they change (via ansible).

- For plpgsql functions used in jobs, the job just reloads the relevant plpgsql functions on the relevant DBs before they start doing anything. (It's a little wasteful, but not in a way that matters for now.)

- The UDFs we've written in C don't change too often, but we deploy them manually when they do.

What are some of the headaches you've had in managing stored procs? How often is your app code changing / requiring new ones?