Re: Views, views, views! (long)

On 5/6/05, Greg Sabino Mullane <greg(at)turnstep(dot)com> 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