При изменении типа колонки, которая участвует в view, выскакивает ошибка типа
cannot alter type of a column used by a view or rule
DETAIL: rule _RETURN on view v1 depends on column "name"
Справиться с проблемой можно так:
#!sql
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;
Источник: postgresql — Changing the type of a column used in other views — Stack Overflow.
Leave a Reply
You must be logged in to post a comment.