How can you stop worrying about table and view dependencies in PostgreSQL?

Default author image
Mateusz Wenus
  • July 14, 2015

Contents

Very often when you try to ALTER TABLE or REPLACE VIEW it tells you that you cannot do it, because there’s another object (typically a view or materialized view), which depends on the one you want to modify. It seems that the only solution is to DROP dependent objects, make desired changes to the target object and then recreate dropped objects.

It is tedious and cumbersome, because those dependent objects can have further dependencies, which also may have other dependencies and so on. I created utility functions which can help in such situations.

The usage is very simple – you just have to call:

You have to pass two arguments: the name of the schema and the name of the object in that schema. This object can be a table, a view or a materialized view. The function will drop all views and materialized views dependent on p_schema_name.p_object_name and save DDL which restores them in a helper table.

When you want to restore those dropped objects (for example when you are done modifying p_schema_name.p_object_name), you just need to make another simple call:

and the dropped objects will be recreated.

These functions take care of:

  • dependencies hierarchy
  • proper order of dropping and creating views/materialized views across hierarchy
  • restoring comments and grants on views/materialized views

Click here for a working sqlfiddle example or check the gist below for a complete source code.

This post was published originally at my blog, which you can find here http://mwenus.blogspot.com.

Looking for a software development company?

Work with a team that already helped dozens of market leaders. Book a discovery call to see:

  • How our products work
  • How you can save time & costs
  • How we’re different from another solutions

footer-contact-steps

We keep your data safe: ISO certified

We operate in accordance with the ISO 27001 standard, ensuring the highest level of security for your data.
certified dekra 27001
© 2025 Pretius. All right reserved.