Re: Views, views, views! (long)
От | Andreas Pflug |
---|---|
Тема | Re: Views, views, views! (long) |
Дата | |
Msg-id | 427BA1CE.4060605@pse-consulting.de обсуждение исходный текст |
Ответ на | Re: Views, views, views! (long) (Tom Lane <tgl@sss.pgh.pa.us>) |
Список | 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 по дате отправления: