postgresql — Changing the type of a column used in other views — Stack Overflow

При изменении типа колонки, которая участвует в 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