Re: Elementary dependency look-up

Added to TODO:

|Add function to dump pg_depend information cleanly

---------------------------------------------------------------------------

Greg Smith wrote:
> On Wed, 9 Sep 2009, decibel wrote:
>
> > What might be more useful is a view that takes the guesswork out of using
> > pg_depend. Namely, convert (ref)classid into a catalog table name (or better
> > yet, what type of object it is), (ref)objid into an actual object name, and
> > (ref)objsubid into a real name.
>
> Here's V1 of a depend unraveler I needed recently and that's saved me a
> bunch of time:
>
> SELECT
> c1.oid as relid,
> n1.nspname || '.' || c1.relname as relation,
> c1.relkind,
> CASE
> WHEN c1.relkind='r' THEN 'table'
> WHEN c1.relkind='i' THEN 'index'
> WHEN c1.relkind='S' THEN 'sequence'
> WHEN c1.relkind='v' THEN 'view'
> WHEN c1.relkind='c' THEN 'composite'
> WHEN c1.relkind='t' THEN 'TOAST'
> ELSE '?'
> END as "kind",
> c2.oid as relid,
> n2.nspname || '.' || c2.relname as dependency,
> c2.relkind,
> CASE
> WHEN c2.relkind='r' THEN 'table'
> WHEN c2.relkind='i' THEN 'index'
> WHEN c2.relkind='S' THEN 'sequence'
> WHEN c2.relkind='v' THEN 'view'
> WHEN c2.relkind='c' THEN 'composite'
> WHEN c2.relkind='t' THEN 'TOAST'
> ELSE '?'
> END as "kind"
> FROM
> pg_depend d,
> pg_class c1,
> pg_namespace n1,
> pg_class c2,
> pg_namespace n2
> WHERE
> d.objid = c1.oid AND
> c1.relnamespace = n1.oid AND
> n1.nspname NOT IN('information_schema', 'pg_catalog') AND
> n1.nspname !~ '^pg_toast' AND
>
> d.refobjid = c2.oid AND
> c2.relnamespace = n2.oid AND
> n2.nspname NOT IN('information_schema', 'pg_catalog') AND
> n2.nspname !~ '^pg_toast' AND
>
> c1.oid != c2.oid
>
> GROUP BY n1.nspname,c1.relname,c1.oid,c1.relkind,
> n2.nspname,c2.relname,c2.oid,c2.relkind
> ORDER BY n1.nspname,c1.relname;
>
> I could throw this on the Wiki as a code snippet if anyone else wanted to
> tinker with it.
>
> --
> * Greg Smith gsmith(at)gregsmith(dot)com http://www.gregsmith.com Baltimore, MD
>
> --
> Sent via pgsql-hackers mailing list (pgsql-hackers(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-hackers

--
Bruce Momjian <bruce(at)momjian(dot)us> http://momjian.us
EnterpriseDB http://enterprisedb.com
PG East: http://www.enterprisedb.com/community/nav-pg-east-2010.do
+ If your life is a hard drive, Christ can be your backup. +