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?
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;
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With