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@gregsmith.com http://www.gregsmith.com Baltimore, MD
>
> --
> Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-hackers
-- Bruce Momjian <bruce@momjian.us> http://momjian.us EnterpriseDB
http://enterprisedb.comPG East: http://www.enterprisedb.com/community/nav-pg-east-2010.do + If your life is a hard
drive,Christ can be your backup. +
