CODEWITHSUNDEEP
postgresqlpostgresql-9.3materialized-views
Nils Pitapatting
Nils Pitapatting

Reputation: 75

Editing a Materialized View in PostgreSQL 9.3 if other Views depend on it

I want to change the value-construction in one field of a materialized view. (e. g. use UPPER() instead of LOWER() for a text field) Neither field-types nor any properties of the view are affected. The problem ist that this materialized view ist the master view. Some Views depend on it and views depend on these views and so on. So dropping this view is not possible because of more than 30 dependent views. One ugly solution is the dropping of 30 views in the correct order and (re-)creation of 30 views.

A nice solution would be the encapsulation of dropping and creating the master view in one transaction and suspend the dependency check for this period. But it seems that this is not possible in PostgreSQL 9.3. Am I right?

Upvotes: 0

Views: 1220

Answers (2)

pozs
pozs

Reputation: 36244

Unfortunately, you cannot "suspend the dependency check" for views. (You could do something similar for stored procedures though).

If only a few view depends on this materialized view directly, but a lot more depends on these (further the dependency chain), there is neat trick to just replace the immediate dependencies (if those are not materialized themselves) with dummy values:

CREATE OR REPLACE VIEW immediate_dependecy_1 AS
  SELECT NULL::uuid col_alias_1,
         NULL::int  col_alias_2,
         NULL::text col_alias_3,
         ...

Note: This will only work, when column names & types do not change using this CREATE OR REPLACE VIEW. From a technical standpoint, you could add more columns after the already existing ones.

After replacing the immediate dependencies, you can drop & re-create the materialized view, then restore the original logic for these immediate dependencies too.

Upvotes: 0

Laurenz Albe
Laurenz Albe

Reputation: 246578

The only solution is to drop and re-create all the views that depend on the materialized view.

Upvotes: 1

Related Questions