Обсуждение: 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

"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.)

            regards, tom lane

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