Change view with dependencies function - Mailing list pgsql-general

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';