CREATE OR REPLACE FUNCTION ...
what whatever it was, it now is what came out of version control
We want it to regard the database as the source of truth, and understand that while you can blow away a development database, you can't do that to prod.
We want it to be able to track which clients are using which stored procedures so it can prohibit dropping stored procedures that are in use. Ideally, when those clients upgrade to newer versions, it's then able to drop them automatically.
Stored procedures can bind to tables, views, types, etc. so it needs to be able to recommend migration plans.
What's the problem?
Well, you might complain that some schema changes are hard to make this way, and that's true, but for stored procedures it's straightforward, and even for other things you can manage and make the update atomic (though it'd be nicer to have better SQL language support for schema changes).
As with everything, you need to mind backwards compatibility. This is true for C, C++, Rust, Java, and SQL/PlPgSQL. There's nothing special about this case.
We have to take down a production database to do this?
Or do you mean the app server is going to do it, in which case how do you handle different app servers with different versions of the stored procedure? Especially if we're doing phased deployments?