The core part of the code was posted by Tom Lane. It would have taken me a
lot longer without that push.
One of the most frustrating things, IMHO, about PostGreSQL is the fact that
when I wanted to change a view, even slightly by adding a new column, I
would have to first drop it along with all the dependent views and then
rebuild them.
This is not a huge problem, because I have the code easily accessible, but
it is still a pain, especially when there are 20+ dependencies. This is
besides functions which stop working if a view/table that it uses is dropped
and recreated, but don't show up in the dependency list. That means you
don't know which functions are going to stop working.
The following 2 functions are
rebuildfunction(funcname text) which will pull up the code for the function
passed in and return the create or replace function statement.
viewchanger(viewname text, viewdefinition text) which puts all the dependent
views and functions into an array, does a drop...Cascade on the view passed
in and then recreates all the views and functions in the correct order.
Post a response or shoot me an email if the code helps you or if you find
problems with it. I'd love to hear that it wasn't only me that was
frustrated by this.
sim ( at ) compulab ( dt ) co (dt) il
create or replace function rebuildfunction(funcname text) returns text as
$Body$
declare
funcstr text;
row record;
i int;
tname text;
begin
funcstr:='Create or Replace Function ';
for row in select
proname,proargtypes,proargnames,prosrc,prorettype,b.typname,c.lanname,proret
set from pg_proc a join pg_type b on a.prorettype=b.oid join pg_language c
on a.prolang=c.oid where proname=funcname Loop
funcstr:=funcstr || row.proname || '(';
if row.proargnames is not null then
for i in array_lower(row.proargnames, 1)..array_upper(row.proargnames, 1)
loop --lower is always 1 in this case
if i<>1 then
funcstr=funcstr || ',';
end if;
funcstr:=funcstr || ' ' || row.proargnames[i];
select typname into tname from pg_type where
oid=row.proargtypes[i-1]; --oidvector indices start at 0
funcstr:=funcstr || ' ' || coalesce(tname,'No Type Found');
end loop;
end if;
funcstr:=funcstr || ') returns ' || case when row.proretset then 'setof '
else '' end || row.typname || ' As $$ ' || row.prosrc || ' $$ Language '''
|| row.lanname || ''';';
end loop;
return funcstr;
end;
$Body$ language 'plpgsql';
create or replace function viewchanger(viewname text, viewdefinition text)
returns setof text as
$$
declare
viewnames text[];
viewdefs text[];
i int;
row record;
rowproc record;
begin
viewnames=ARRAY[viewname];
viewdefs=ARRAY[viewdefinition];
i=array_lower(viewnames, 1);
Loop
for row in select distinct c.relname,e.definition from pg_class d,
pg_depend a join pg_rewrite b on a.objid=b.oid
join pg_class c on ev_class=c.oid
join pg_views e on e.viewname=c.relname
where refclassid = 'pg_class'::regclass and refobjid = d.oid
and ev_class<>d.oid and d.relname=viewnames[i] Loop
if row.relname is not null then
viewnames[array_upper(viewnames, 1)+1]=row.relname;
viewdefs[array_upper(viewdefs, 1)+1]='Create or Replace View ' ||
row.relname || ' as ' || row.definition;
end if;
end loop;
i:=i+1;
if viewnames[i] is null then
exit;
end if;
end loop;
execute 'drop view ' || viewname || ' cascade';
for i in array_lower(viewdefs, 1)..array_upper(viewdefs, 1) loop
for rowproc in select proname from pg_proc where prosrc like '%' ||
viewnames[i] || '%' loop
viewnames[array_upper(viewnames, 1)+1]=rowproc.proname;
viewdefs[array_upper(viewdefs, 1)+1]=rebuildfunction(rowproc.proname);
end loop;
end loop;
for i in array_lower(viewdefs, 1)..array_upper(viewdefs, 1) loop
execute viewdefs[i];
return next viewnames[i];
end loop;
return;
end;
$$ language 'plpgsql';
---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match
