Enhance "CreateDatabaseView_management" with the option to create MATERIALIZED views

MarcoBoeringa — Wed, 05 Oct 2022 23:59:13 GMT

It would be really helpful if the CreateDatabaseView_management tool was enhanced with the option to create MATERIALIZED views instead of ordinary ones.

In many cases, ArcGIS Query Layers may reference a view that has poor performance due to complex SQL. The only real solution for that is to materialize the view.

However, once a view is created and referenced by a certain view name, it is no longer possible to modify the view by inserting the MATERIALIZE keyword in e.g. pgadmin in case of PostgreSQL, and save the view with the same name. PostgreSQL does not allow you to simply overwrite an existing view (you must DELETE first), even if you are the owner and exclusive user of the view or table.

Thus, instead, a new view must be created with a different name. The consequence of this is that any Query Layers referencing the original view needs updating of its data source.

For automation workflows using arcpy, it would therefor be very useful to have the ability to create a materialized view upon calling the CreateDatabaseView_management tool, e.g. via a new optional parameter setting, instead of having to reset data sources, or do a complex replacement including multiple DELETE and CREATE statements on the database side.

idea Enhance "CreateDatabaseView_management" with the option to create MATERIALIZED views in Data Management Ideas

Enhance "CreateDatabaseView_management" with the option to create MATERIALIZED views