Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

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;                                                       

Gives this error:

cannot alter type of a column used by a view or rule
DETAIL:  rule _RETURN on view v1 depends on column "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');

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

Is something like this possible? Are there utilities out there that do something similar?

like image 201
Joe Van Dyk Avatar asked Mar 14 '12 22:03

Joe Van Dyk


1 Answers

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;
like image 56
dbenhur Avatar answered Nov 15 '22 09:11

dbenhur