![]() ![]() 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 |