Tom Lane wrote:
"Jim C. Nasby" <[EMAIL PROTECTED]> writes:
Aside from that, it's currently rather silly that every admin tool has
to code up a very complex set of queries to get info from the system
catalog. It makes much more sense to put that complexity into a set of
system views that are maintained as part of the backend, instead of
pushing that effort out to everyone who writes tools.
So instead, they should code up complex queries to get info from the
system views? Your claim only makes sense if you know exactly what
"every admin tool" is going to need, what format they are going to want
it in, and other things that I doubt you are really prescient enough
to get 100% right.
Well I think you're wrong. We really should have a view like this, I'll
provide more to include them in pgsql8.1:
CREATE VIEW pg_dependent_objects_for_pga3 AS
SELECT DISTINCT deptype, classid, cl.relkind,
CASE WHEN cl.relkind IS NOT NULL THEN cl.relkind
WHEN tg.oid IS NOT NULL THEN 'T'::text
WHEN ty.oid IS NOT NULL THEN 'y'::text
WHEN ns.oid IS NOT NULL THEN 'n'::text
WHEN pr.oid IS NOT NULL THEN 'p'::text
WHEN la.oid IS NOT NULL THEN 'l'::text
WHEN rw.oid IS NOT NULL THEN 'R'::text
WHEN co.oid IS NOT NULL THEN 'C'::text || contype
ELSE '' END AS type,
COALESCE(coc.relname, clrw.relname) AS ownertable,
COALESCE(cl.relname, conname, proname, tgname, typname,
lanname, rulename, ns.nspname) AS refname,
COALESCE(nsc.nspname, nso.nspname, nsp.nspname,
nst.nspname, nsrw.nspname) AS nspname
FROM pg_depend dep
LEFT JOIN pg_class cl ON dep.objid=cl.oid
LEFT JOIN pg_namespace nsc ON cl.relnamespace=nsc.oid
LEFT JOIN pg_proc pr on dep.objid=pr.oid
LEFT JOIN pg_namespace nsp ON pronamespace=nsp.oid
LEFT JOIN pg_trigger tg ON dep.objid=tg.oid
LEFT JOIN pg_type ty on dep.objid=ty.oid
LEFT JOIN pg_namespace nst ON typnamespace=nst.oid
LEFT JOIN pg_constraint co on dep.objid=co.oid
LEFT JOIN pg_class coc ON conrelid=coc.oid
LEFT JOIN pg_namespace nso ON connamespace=nso.oid
LEFT JOIN pg_rewrite rw ON dep.objid=rw.oid
LEFT JOIN pg_class clrw ON clrw.oid=rw.ev_class
LEFT JOIN pg_namespace nsrw ON cl.relnamespace=nsrw.oid
LEFT JOIN pg_language la ON dep.refobjid=la.oid
LEFT JOIN pg_namespace ns ON dep.objid=ns.oid
Isn't it a shame that this widely usable query isn't included in pgsql
since 7.0? ;-)
Regards,
Andreas
---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend
