Let’s say you need to
ALTER TABLE foo ALTER COLUMN bar TYPE bigint;,
and PostgreSQL is helpfully telling you that no you
can’t because such and such views depend on the column. The basic way to
deal with that is to copy paste from the error message the names of the
views involved, then prepare a script wherein you first DROP VIEW then
ALTER TABLE and finally CREATE VIEW again, all in the same transaction.
So you have to copy paste also the view definitions. With large view definitions, it quickly gets cumbersome to do so. Well when you’re working on operations, you have to bear in mind that cumbersome is a synonym for error prone, in fact — so you want another solution if possible.
Oh, and the other drawback of this solution is that
ALTER TABLE will first
LOCK on the table, locking out any activity. And more than that, the
lock acquisition will queue behind current activity on the table, which
means waiting for a fairly long time and damaging the service quality on a
moderately loaded server.
It’s possible to abuse
system catalogs in
order to find all views that depend on a given table, too. For that, you
have to play with
pg_depend and you have to know that internally, a view
is in fact a rewrite rule. Then here’s how to produce the two scripts that
=# \t Showing only tuples. =# \o /tmp/drop.sql =# select 'DROP VIEW ' || views || ';' from (select distinct(r.ev_class::regclass) as views from pg_depend d join pg_rewrite r on r.oid = d.objid where refclassid = 'pg_class'::regclass and refobjid = 'SCHEMA.TABLENAME'::regclass and classid = 'pg_rewrite'::regclass and pg_get_viewdef(r.ev_class, true) ~ 'COLUMN_NAME') as x; =# \o /tmp/create.sql =# select 'CREATE VIEW ' || views || E' AS \n' || pg_get_viewdef(views, true) || ';' from (select distinct(r.ev_class::regclass) as views from pg_depend d join pg_rewrite r on r.oid = d.objid where refclassid = 'pg_class'::regclass and refobjid = 'SCHEMA.TABLENAME'::regclass and classid = 'pg_rewrite'::regclass and pg_get_viewdef(r.ev_class, true) ~ 'COLUMN_NAME') as x; =# \o
COLUMN_NAME with your targets here and the
first query should give you one row per candidate view. Well if you’re not
\o trick, that is — if you do, check out the generated file
\! cat /tmp/drop.sql for example.
Please note that this catalog query is not accurate, as it will select as a
candidate any view that will by chance both depend on the target table and
column_name in its text definition. So either filter out the
candidates properly (by proper proof reading then another
WHERE clause), or
just accept that you might
CREATE again more
views than need be.
If you need some more details about the
\t \o sequence you might be
interested in this older article
about resetting sequences.