Re: Walking a view to find all source tables

> -----Original Message-----
> From: Tom Lane [mailto:tgl(at)sss(dot)pgh(dot)pa(dot)us]
> Sent: Sunday, May 19, 2002 1:31 PM
> To: Joel Burton
> Cc: Pgsql-General(at)Postgresql(dot) Org
> Subject: Re: [GENERAL] Walking a view to find all source tables
>
>
> "Joel Burton" <joel(at)joelburton(dot)com> writes:
> > 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.
>
> A few comments:
>
> > if rec.relkind = ''r'' then return '''';
>
> Probably be better to punt whenever relkind <> 'v', instead.
>
> > def := ev_action from pg_rewrite where ev_Class=rec.oid;
>
> Will tend to fail if view has rules other than select (insert, delete,
> update). You'd better restrict the ev_type field too.
>
>
> Also, I'd suggest making the internal recursion pass table oid not name;
> will be a lot easier to adapt to 7.3, wherein relname is not unique.
> (Although there may be better ways to do the whole thing in 7.3,
> anyway --- need to review Rod Taylor's pg_depend patch again, but
> probably some form of that will get in there.)

Thanks, Tom, for the feedback.

If it's useful for others, here's the improved version. Switch the comments
at the bottom to enable the 7.3devel-specific (schema-aware) code.

drop function walker(oid, int);

create function walker(oid, int) returns text as '
declare
out text := '''';
def text;
loc int;
tbl text;
spaces alias for $2;
begin
def := ev_action from pg_rewrite where ev_class=$1 and ev_type=1;

if def is null then
return '''';
end if;

loop
loc := position ('':relid '' in 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::oid <> $1 then
out := out || repeat('' '',spaces) || tbl_fullname(tbl) || ''\n'' ||
walker(tbl::oid, spaces + 2);
end if;

end loop;

return out;

end;' language 'plpgsql';

-- 7.3 only (uses schemas)
--create or replace function tbl_fullname(text) returns text as 'begin
return $1::oid::regclass; end' language plpgsql;
--create or replace function walker(regclass) returns text as 'begin return
walker($1::regclass::oid, 0); end' language 'plpgsql';

-- previous to 7.1 (tested back to 7.1)
drop function tbl_fullname(text);
create function tbl_fullname(text) returns text as 'begin return relname
from pg_class where oid= $1::oid; end;' language 'plpgsql';

drop function walker(text);
create function walker(text) returns text as 'begin return walker(oid,0)
from pg_class where relname= $1; end;' language 'plpgsql';

Joel BURTON | joel(at)joelburton(dot)com | joelburton.com | aim: wjoelburton
Knowledge Management & Technology Consultant