![]() Short of having a rebuild script to DROP CASCADE and then recreate ALL of my VIEWs this is a work around. I cannot just drop my VIEW because it is a master VIEW that has many dependent VIEWs built on top of it. I ran into this problem today and found a work around to avoid dropping and recreating the VIEW. Technically you can change the size of the table column without changing the size of the view column, but no guarantees on what side effects that will have it's probably best to change them both at once. Now \d foo shows: id | integer | not nullīonus: that was waaay faster than doing: ALTER TABLE foo ALTER COLUMN names TYPE varchar(20) (note: the 20 4 is some crazy postgresql legacy thing, the 4 is compulsory.) WHERE attrelid IN ('foo'::regclass, 'voo'::regclass) Now change the lengths to 20 in the pg_attribute table: UPDATE pg_attribute SET atttypmod = 20 4 \d foo and \d voo both show the length as 10: id | integer | not null Starting with these tables: CREATE TABLE foo (id integer primary key, names varchar(10)) ĬREATE VIEW voo AS (SELECT id, names FROM foo) If you don't need to change the type of the field, but just the size of it, this approach should work: Where deps_view_schema = p_view_schema and deps_view_name = p_view_name Restore: CREATE OR REPLACE FUNCTION ps_restore_dependencies( When v_curr.obj_type = 'm' then 'MATERIALIZED VIEW' Where schemaname = v_curr.obj_schema and matviewname = v_curr.obj_name Select p_view_schema, p_view_name, 'CREATE MATERIALIZED VIEW ' || v_curr.obj_schema || '.' || v_curr.obj_name || ' AS ' || definition Select p_view_schema, p_view_name, 'CREATE VIEW ' || v_curr.obj_schema || '.' || v_curr.obj_name || ' AS ' || view_definition Where table_schema = v_curr.obj_schema and table_name = v_curr.obj_name Select p_view_schema, p_view_name, 'GRANT ' || privilege_type || ' ON ' || table_schema || '.' || table_name || ' TO ' || granteeįrom information_schema.role_table_grants Join pg_description d on d.objoid = c.oid and d.objsubid = a.attnum Join pg_attribute a on c.oid = a.attrelid Where n.nspname = v_curr.obj_schema and c.relname = v_curr.obj_name and d.description is not null Join pg_description d on d.objoid = c.oid and d.objsubid = 0 Join pg_namespace n on n.oid = c.relnamespace When c.relkind = 'm' then 'MATERIALIZED VIEW' Select p_view_schema, p_view_name, 'COMMENT ON ' || Insert into ps_saved_ddl(deps_view_schema, deps_view_name, deps_ddl_to_run) Select obj_schema, obj_name, obj_type, depth Where (deps.ref_schema != p_schema or deps.ref_name != p_name) Join recursive_deps on deps.ref_schema = recursive_deps.obj_schema and deps.ref_name = recursive_deps.obj_name Join pg_namespace rwr_nsp on rwr_cl.relnamespace = rwr_nsp.oid Join pg_class rwr_cl on rwr.ev_class = rwr_cl.oid Join pg_rewrite rwr on dep.objid = rwr.oid Join pg_namespace ref_nsp on ref_cl.relnamespace = ref_nsp.oid Join pg_class ref_cl on dep.refobjid = ref_cl.oid Select ref_nsp.nspname ref_schema, ref_cl.relname ref_name, Select dep_schema::varchar, dep_name::varchar, dep_type::varchar, recursive_pth 1 from Select p_view_schema, p_view_name, null::varchar, 0 With recursive recursive_deps(obj_schema, obj_name, obj_type, depth) as Select obj_schema, obj_name, obj_type from ![]() The fix is below to change the parameters of p_view_schema and p_view_name from varchar to name: CREATE OR REPLACE FUNCTION ps_save_and_drop_dependencies( Table that stores DDL: CREATE TABLE ps_saved_ddlĬONSTRAINT deps_saved_ddl_pkey PRIMARY KEY (deps_id) Select ps_restore_dependencies('mdm', 'global_item_master_swap') Rule _RETURN on view toolbox_reporting."Average_setcost" depends onĮRROR: cannot alter type of a column used by a view or ruleĪnd now for the PostgreSQL ninja's magic: select ps_save_and_drop_dependencies('mdm', 'global_item_master_swap') Implementation example: alter table mdm.global_item_master_swapĮRROR: cannot alter type of a column used by a view or rule DETAIL: So, read this article and copy and paste the table and two functions listed: I just tested this on an object that is referenced (on the first level) in 136 separate views, and each of those views is referenced in other views. ![]() I'm a little late to the party, but years after this question was posted, a brilliant solution was posted via an article referenced below (not mine - I'm simply a thankful beneficiary of his brilliance).
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |