My 'dilemma' is that this functionality is packaged and the database it is bundled into could be running on a pre-12 version or 12+. Is there any way I can rewrite my view to achieve the same outcome (i.e. only creating 0 or 1 advisory locks) regardless of the server version? I realise I could have two installation scripts but if it is installed into a pre-12 DB and that DB is subsequently upgraded to 12+, my behaviour is broken.
Pretty sure you will need to choose a location in which to make the installation behave version-dependently. Within PostgreSQL itself that would be most easily done by writing a pl/pgsql function that conditionally adds the MATERIALIZED indicator on the textual representation of the query before executing it. You can hide that function call within a view if desired.
