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(at)joelburton(dot)com | joelburton.com | aim: wjoelburton
Knowledge Management & Technology Consultant