Andreas Pflug <pgadmin@pse-consulting.de> writes:
> Tom Lane wrote:
>> This is an unsupported operation. You should perhaps complain to the
>> pgadmin guys that they are not correctly updating the system catalogs.
> UPDATE pg_attribute
> SET atttypmod=2504
> WHERE attrelid=25574::oid AND attnum=2;
> This is what pgAdmin3 will generate to change a varchar to 2500 bytes.
> Please let me know what's wrong with that.
It doesn't fix views that contain references to the column. The new
typmod would need to be propagated into the view's rule parsetree, and
perhaps to the type of the view's result column if the view directly
exposes the changed column (whereupon you need to recursively look at
the views that depend on this one, etc).
What you could probably do is find the referencing views via pg_depend.
For each one, try to do CREATE OR REPLACE VIEW using the view definition
string from pg_get_viewdef. If it succeeds you're done (the variable
must not be propagated to any output column). If it fails, adjust the
indicated output column's typmod. Lather, rinse, repeat in case there
is more than one dependent output column. Recurse once you've
successfully altered the view.
It'd probably also be a smart idea to error out if pg_depend shows any
dependencies on the column from objects that you don't know what to do
with (aren't views).
I recall there was some discussion of this stuff on pgsql-hackers the
last time it was proposed to support "ALTER COLUMN type". We may have
thought of some additional considerations besides views. I'd suggest
trawling the list archives to see...
regards, tom lane