I have used CTEs with dynamic query stitching to solve this problem (specifically my business operates over two very similar but distinct domains which we keep in separate buckets). If you build the majority of your logic into a CTE that processes named columns coming out of a prior chunk you can swap out what actual columns in the DB are mapped into the columns coming out of that earlier CTE with its definition. It may be possible to make this more magical using pl/pgsql but I've found that dynamic query stitching at the CTE resolution is a level of fiddly-ness I'm comfortable building into resilient products.

I work with complex data models and keeping all that structure in my brain takes enough effort that I want to keep my queries as simple as possible because when it's time to debug one there's no way I'm carrying over _any_ memory from when I originally wrote it.