Changing the type of a column used in other views

Question

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?

select definition from pg_views where viewname ='v1'; gives you the view definition — dbenhur
– dbenhur, Commented Mar 14, 2012 at 23:03

dbenhur · Accepted Answer · 2012-03-15 04:17:46Z

9

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;

edited Mar 15, 2012 at 4:17

answered Mar 14, 2012 at 23:46

dbenhur

20.5k4 gold badges51 silver badges47 bronze badges

Sign up to request clarification or add additional context in comments.

7 Comments

dbenhur Over a year ago

To be more complete, one could figure out how to query which views depend on the table(es) you're modifying and use that query instead f enumerating view names. Gonna need to understand pg_rewrite | pg_rule to do that, I think.

Joe Van Dyk Over a year ago

Interesting, thanks. I also have a couple postgresql functions that need to be drop and recreated as well. I think I could do that with a similar approach.

Joe Van Dyk Over a year ago

I think the views need to be created in the opposite order in which they were dropped (if some of the views depend on other views).

dbenhur Over a year ago

I edited the function to create in reverse order from drop. If the views depend on each other the call needs to specify them in reverse dependency order

Joe Van Dyk Over a year ago

Thanks! I think I edited your answer to include the same code seconds after you did.

|

Mateusz Szczepaniak · Accepted Answer · 2013-05-24 10:28:28Z

an improvment would be to check before trying to drop view if it exists at all, otherwise you will get an error, so do like this :

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; IF def IS NOT NULL THEN EXECUTE 'DROP VIEW ' || schema_name || '.' || views[i]; END IF; end loop; EXECUTE run_me; for i in reverse array_upper(views,1) .. array_lower(views,1) loop IF view_defs[i] IS NOT NULL THEN def = 'CREATE OR REPLACE VIEW ' || schema_name || '.' || views[i] || ' AS ' || view_defs[i]; EXECUTE def; END IF; end loop;

Collectives™ on Stack Overflow

Changing the type of a column used in other views

2 Answers 2

7 Comments

Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

7 Comments

Comments

Linked

Related