Walking a view to find all source tables

During some debugging of a database tonight, I wrote this function that
calls a view, examines its source, and recursively tracks down all the
source tables.

It's a plpgsql function, so you don't need any other languages installed. It
would probably be safer and certainly faster to make it a real C function,
but this works for me. If anyone has any feedback, I'd be happy to hear.

To call it:

# SELECT walker(view_name)

It will output a list like:

pg_user
  pg_shadow
pg_views
  pg_class
  pg_namespace
v3
  v2
    v1
      foo
      bar
      baz
      v0
        foo
        bar
        baz
    v0
      foo
      bar
      baz

(showing that this query relies on pg_user, pg_views, and v3. pg_user relies
on pg_shadow. pg_views relies on pg_class. v3 relies on v2, which in turn
relies on v1 ...)


create or replace function walker(name, int) returns text as '
declare
  out text := '''';
  def text;
  loc int;
  tbl text;
  rec record;
  spaces alias for $2;

begin
  select oid, relkind into rec from pg_Class where relname = $1;
  if rec.relkind = ''r'' then return '''';
  end if;

  def := ev_action from pg_rewrite where ev_Class=rec.oid;

  loop
    loc := position ('':relid '' in def); -- :relid preceeds all table refs
in view def
    if loc = 0 then
      exit;
      end if;

    def := substring(def from loc+7);
    loc := position ('' '' in def);
    if loc = 0 then
      exit;
    end if;

    tbl := substring(def from 1 for loc-1);

    if tbl <> rec.oid::text then
       tbl := relname from pg_Class where oid = tbl::oid;
       out := out || repeat('' '',spaces) || tbl || ''\n'' || walker(tbl,
spaces + 2);
    end if;

  end loop;

  return out;

end' language 'plpgsql';

create or replace function walker(name) returns text as 'begin return
walker($1, 0); end' language plpgsql;




Joel BURTON | joel@joelburton.com | joelburton.com | aim: wjoelburton
Knowledge Management & Technology Consultant