On 5/6/05, Greg Sabino Mullane <[EMAIL PROTECTED]> wrote: > As long as they are in a separate schema (like information_schema, > but hopefully not as long). pg_views? pg_info? information_skema? :) > > > But if you think that nobody needs these views, it's because you > > haven't had much contact with end users lately. > > Well, who really *does* need these? After all, end users should be > using an interface of some sort. (DBD::Pg, phpPgAdmin, psql, etc). It's > the job of the people writing those interfaces to know the system > catalogs well and present them to the users in a pretty fashion. If > people want an "easy" way to look up the information, they use an > interface. If not, they should learn the system catalogs. > /devilsadvocate
Wellll... Lets assume that young DBA needs to get a list of primary keys for each table. If she's smart she'll probably run psql -E and get queries like: SELECT c.relname FROM pg_catalog.pg_class c, pg_catalog.pg_inherits i WHERE c.oid=i.inhparent AND i.inhrelid = '6338115' ORDER BY inhseqno ASC SELECT a.attname, pg_catalog.format_type(a.atttypid, a.atttypmod), (SELECT substring(d.adsrc for 128) FROM pg_catalog.pg_attrdef d WHERE d.adrelid = a.attrelid AND d.adnum = a.attnum AND a.atthasdef), a.attnotnull, a.attnum FROM pg_catalog.pg_attribute a, pg_catalog.pg_index i WHERE a.attrelid = '6338117' AND a.attnum > 0 AND NOT a.attisdropped AND a.attrelid = i.indexrelid ORDER BY a.attnum SELECT i.indisunique, i.indisprimary, i.indisclustered, a.amname, c2.relname, pg_catalog.pg_get_expr(i.indpred, i.indrelid, true) FROM pg_catalog.pg_index i, pg_catalog.pg_class c, pg_catalog.pg_class c2, pg_catalog.pg_am a WHERE i.indexrelid = c.oid AND c.oid = '6261315' AND c.relam = a.oid AND i.indrelid = c2.oid ...and so on. Then refashion them to do the needed query. Then again she may look inside information_schema.* (columns?), but it is not as natural as one would like. And then again, as most people are lazy, she would probably use: select schemaname,tablename,attname from pg_stats where n_distinct = -1 and schemaname='public'; Which is simply the stupidest way, and of course the wrong one. Yet it gives an illusion of returning "quite right" data the easy way... Sometimes it may be terribly tempting... I would certainly like to see these views in PostgreSQL. Maybe as a contrib package (just as there are tsearch2 or intarray). I think such views would not be of much use for, say pgAdmin. Yet again for querying from perl/php or over "human carrier" it would be benefitial, I guess. My 0.03 PLN. ;) Regards, Dawid ---------------------------(end of broadcast)--------------------------- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly