Troels, Others,
> Generally: Nice. But have you considered if the INFORMATION_SCHEMA could
> be used? Unfortunately, the INFORMATION_SCHEMA currently has a major
> problem in its usefulness in PostgreSQL:
> http://troels.arvin.dk/db/rdbms/#cli-list_of_tables-postgresql-gotchas
Actually, I did. However, the format and columns of INFORMATION_SCHEMA are
defined by the SQL Standard, which will not cover a lot of PostgreSQL objects
(such as custom types or operators) and covers a lot of others in rather
awkward form. For that matter, your own editorial points out that we should
really be UPPERCASEing all of the object names in information_schema, which
would be SQL-spec but not generally useful.
> This reminds me: It would be nice if it were somehow possible to determine
> when (if ever) statistics have been gathered for a given schema object.
> This needs changes to more than VIEWs, though.
Well, you can always query pg_stats.
> Do you propose that typemodifiers be one column? - If would prefer if it
> were several columns. And it would be useful if it were easy to determine
> if a column is
> - solely - or part of - a uniqueness constraint
> - solely - or part of - a foreign key (pointing where?)
> - if it is subject to a (set of) CHECK constraints
Yeah, I gave this some thought. The problem as I see it is that in the
future we may have additional types of typemodifiers which aren't covered,
and I don't want to get in the habit of adding more and more columns to the
view. However, that's not really an excuse; it might be better to:
pg_columns --> new viewschemanametablenamecolumnnamedatatypenotnullreferences (name which links pg_foreignkeys, or
boolean?)defaultconstraints(array, references pg_constraints)othermodifiers (string of other column modifiers, for when
suchexist)comment
In a way, though, it might be better for "references" to be a boolean column,
and users can query pg_foriegnkeys to find the exact reference.
====
BTW, People, I really don't see the point in prodiving a dual list -- that is,
a list of OIDs in addition to the list of names provided in the columns of
each view. The idea of these views is to keep the users *away* from
technical details like OIDs, which can and will change with the advancing
versions of PostgreSQL.
--
Josh Berkus
Aglio Database Solutions
San Francisco