修改表字段长度时更新视图字段长度

环境

系统平台：Microsoft Windows (64-bit) 10
版本：5.6.5

文档用途

1.修改表字段长度时，会提示有视图正在使用不允许修改，此时需要删除对应的视图。

2.视图可能还存在嵌套，如果修改的表字段较多，处理起来较繁琐。

3.现通过函数将涉及的视图保存到表中，根据排序的规则删除视图，修改表字段，重建视图。

详细信息

–1.创建测试表和view
create table testv(id serial,address varchar(5));

–根据字段查询

create view vtest1 as select id,address from testv where address=‘1’;

–查询*

create view vtest2 as select * from testv;

–嵌套字段view1

create view vtest1_1 as select address from vtest1;

–嵌套*view2

create view vtest2_1 as select address from vtest2;

INSERT INTO vtest1(address)values(‘asdfg’),(‘1’),(‘12’),(‘123’),(‘1234’);

–2.导出涉及view,暂不考虑function 有使用到view情况,暂不考虑交叉调用情况

CREATE OR REPLACE FUNCTION funvread(db varchar(30),sm varchar(30),textlist text,flag BOOLEAN DEFAULT true)

RETURNS void

LANGUAGE plpgsql

AS $function$

declare cur refcursor;

i int DEFAULT 1;

v_sql varchar;

v_sql1 varchar;

v_name text;

begin

–递归判断 第一次调用

IF flag THEN

–表不存在则创建

CREATE TABLE IF NOT EXISTS t_v_read_20200317(id serial,vname text,def text);

–清空表

DELETE FROM t_v_read_20200317;

END IF;

–递归增view字段

create temp TABLE IF NOT EXISTS tempTable(id serial,vname text);

while i<=array_length(regexp_split_to_array(textlist,‘,’),1)

LOOP

DELETE FROM tempTable;

–根据表名，或者view 查出对应view名，view 建立语句

v_sql=‘INSERT INTO t_v_read_20200317(vname,def) SELECT view_name,view_definition FROM information_schema.view_table_usage a inner JOIN information_schema.views b ON b.table_name=view_name WHERE b.table_catalog=’‘’||db||‘’’ AND b.table_schema= ‘’‘||sm||’‘’ AND a.table_name= ‘’‘||(regexp_split_to_array(textlist,’,‘)::text[])[i]||’‘’';

execute(v_sql);

–增虚拟表，循环调用

v_sql1=‘INSERT INTO tempTable(vname) SELECT view_name FROM information_schema.view_table_usage a inner JOIN information_schema.views b ON b.table_name=view_name WHERE b.table_catalog=’‘’||db||‘’’ AND b.table_schema= ‘’‘||sm||’‘’ AND a.table_name= ‘’‘||(regexp_split_to_array(textlist,’,‘)::text[])[i]||’‘’';

execute(v_sql1);

SELECT string_agg(vname,‘,’) INTO v_name FROM tempTable;

IF v_name!=‘’ THEN

PERFORM funvread(db,sm,v_name,FALSE);

END IF;

i=i+1;

END LOOP;

–raise notice ‘%’, ‘已保存view到表t_v_read_20200317’;

end;

$function$

–测试，不可重复执行，会删除掉已保存view信息

–SELECT funvread(‘test_db’,‘public’,‘testv’,true);

–SELECT * FROM t_v_read_20200317;

–3.删除view

CREATE OR REPLACE FUNCTION funvdrop()

RETURNS void

LANGUAGE plpgsql

AS $function$

declare cur refcursor;

v_name text;

begin

open cur for SELECT vname FROM t_v_read_20200317 ORDER BY id desc;

–循环删除VIEW

fetch cur into v_name;

while (Found)

loop

execute('drop view '||v_name);

fetch cur into v_name;

end loop;

close cur;

end;

$function$

–测试，删除关联view

–SELECT funvdrop();

–4.修改表字段

ALTER TABLE testv ALTER COLUMN address TYPE varchar(15);

–5.重建涉及view

CREATE OR REPLACE FUNCTION funvrecreate()

RETURNS void

LANGUAGE plpgsql

AS $function$

declare cur refcursor;

v_name text;

v_def text;

begin

open cur for SELECT vname,def FROM t_v_read_20200317 ORDER BY id;

–循环删除VIEW

fetch cur into v_name,v_def;

while (Found)

loop

execute('create OR REPLACE view ‘||v_name||’ as '||v_def);

fetch cur into v_name,v_def;

end loop;

close cur;

end;

$function$

–测试，创建关联view

–SELECT funvrecreate();