Re: Views, views, views! (long) - Mailing list pgsql-hackers

From Andreas Pflug
Subject Re: Views, views, views! (long)
Date
Msg-id 427BA1CE.4060605@pse-consulting.de
Whole thread Raw
In response to Re: Views, views, views! (long)  (Tom Lane <tgl@sss.pgh.pa.us>)
List pgsql-hackers
Tom Lane wrote:
> "Jim C. Nasby" <decibel@decibel.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.oidIS 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_classcl 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
LEFTJOIN pg_type ty on dep.objid=ty.oid   LEFT JOIN pg_namespace nst ON typnamespace=nst.oid   LEFT JOIN pg_constraint
coon 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
nsrwON 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



pgsql-hackers by date:

Previous
From: Tom Lane
Date:
Subject: Re: Views, views, views! (long)
Next
From: "Joshua D. Drake"
Date:
Subject: Re: pgFoundry