How do you write tests? With what data are you testing?
How do you handle backwards compatibility? (For the code? For the data?)
Do you do this upgrade during deployment? Can you do blue/green deployments? What about canary deployments?
Is this transaction where you are dropping the old and creating the new part of the code or part of the database? (the actual code that does this)
How do you profile the performance of the said code?
You can use pgtap to write automated unit tests. For profiling, there are explain, autoexplain, and plprofiler.
Blue/Green and Canary deployments are not currently possible with Postgres. On the other hand, Postres has transactional DDL, which means there is no downtime during code deployment and automatic rollback if something goes wrong.
The database is only for data and for stored procedures or functions, which are stored in separate schemas. Your deployment scripts, migration scripts, test scripts and everything else is not stored in the database, but in your source control system, e.g. Git.
For everything else, just use conventional software engineering practices. There is no reason to treat SQL code differently than Ruby or Java code.
seriously, you have the actual data, you have the store procedures and you have the code. Each has their own version. I have never seen this work in a production environment. It's possible that things evolved and there is better tooling since I last tried performing this stunt.
If it works for you that's great and maybe you should write some sort of blog post (do people still blog?) describing the setup and allowing others to either 1) replicate and use it 2) poke holes in it.
How do you handle this in other languages?
How does this nonsense add anything to the discussion?