Re: Views, views, views! (long)

Tom Lane wrote:
> "Jim C. Nasby" <decibel(at)decibel(dot)org> 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