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
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 the
system catalogs in order to find all
depend on a given table, too. For that, you have to play with
you have to know that internally, a
view is in fact a
rewrite rule. Then
here's how to produce the two scripts that we need:
=# \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