更改其他视图中使用的列的类型 [英] Changing the type of a column used in other views
问题描述
create table base (name character varying(255));
create view v1 as select *, now() from base;
create view v2 as select * from v1 where name = 'joe';
alter table base alter column name type text;
给出此错误:
cannot alter type of a column used by a view or rule
DETAIL: rule _RETURN on view v1 depends on column "name"
这有点烦人,因为现在我必须重新创建所有引用 base.name
列的视图。 。当我有引用其他视图的视图时,这尤其烦人。
This is sort of annoying, because now I have to recreate all the views that reference the base.name
column. It's especially annoying when I have views that reference other views.
我希望能够执行的操作如下:
What I'd love to be able to do is something like:
select recreate_views('v1', 'v2', 'alter table base alter column name type text');
并让该函数获取v1和v2的视图定义,将其删除,运行指定的代码,然后重新创建v1和v2。如果我可以使用Ruby,则可能会让函数采用一个function / block / lambda,例如
And have the function get the view definitions for v1 and v2, drop them, run the code specified, then recreate v1 and v2. If I could use Ruby, I'd probably have the function take a function/block/lambda, like
recreate_views 'v1', 'v2' do
alter table base alter column name type text
end
这样可能吗?
推荐答案
我认为这可以满足您的要求,尽管我将视图列表移至args的末尾与VARIADIC语义兼容。
I think this does what you want, though I moved the view list to the end of args to be compatible with VARIADIC semantics.
CREATE OR REPLACE FUNCTION recreate_views(run_me text, VARIADIC views text[])
RETURNS void
AS $$
DECLARE
view_defs text[];
i integer;
def text;
BEGIN
for i in array_lower(views,1) .. array_upper(views,1) loop
select definition into def from pg_views where viewname = views[i];
view_defs[i] := def;
EXECUTE 'DROP VIEW ' || views[i];
end loop;
EXECUTE run_me;
for i in reverse array_upper(views,1) .. array_lower(views,1) loop
def = 'CREATE OR REPLACE VIEW ' || quote_ident( views[i] ) || ' AS ' || view_defs[i];
EXECUTE def;
end loop;
END
$$
LANGUAGE plpgsql;
这篇关于更改其他视图中使用的列的类型的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!