postgresql - How to refer to or coalesce a column which may not exist?

Question

I am creating views referring to system catalog views ( eg. pg_constraint ) which are to deployed to various databases with varying versions of postgresql. Later versions of system catalog views include columns that don't exist in prior versions of postgresql. I'd rather have a single version of my view that I can deploy widely instead of maintaining multiple versions so that when we (inevitably) upgrade some of our older databases, the view definition will not need to be updated ( who is going to remember to do that? )...

It's probably not feasible and perhaps I'm grasping at straws but I don't want to give up so easily while it's still possible someone may have a solution.

question from:https://stackoverflow.com/questions/65901998/how-to-refer-to-or-coalesce-a-column-which-may-not-exist

Answer 1

Don't create a view on system catalogs, it will prevent you from upgrading with pg_upgrade.

Use a function that either composes the query with dynamic SQL or executes different static queries based on the server version. You can then define a view using that function.