Re: Walking a view to find all source tables - Mailing list pgsql-general
From | Joel Burton |
---|---|
Subject | Re: Walking a view to find all source tables |
Date | |
Msg-id | JGEPJNMCKODMDHGOBKDNKEOCCOAA.joel@joelburton.com Whole thread Raw |
In response to | Re: Walking a view to find all source tables (Tom Lane <tgl@sss.pgh.pa.us>) |
List | pgsql-general |
> -----Original Message----- > From: Tom Lane [mailto:tgl@sss.pgh.pa.us] > Sent: Sunday, May 19, 2002 1:31 PM > To: Joel Burton > Cc: Pgsql-General@Postgresql. Org > Subject: Re: [GENERAL] Walking a view to find all source tables > > > "Joel Burton" <joel@joelburton.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@joelburton.com | joelburton.com | aim: wjoelburton Knowledge Management & Technology Consultant
pgsql-general by date: