Re: Extending System Views: proposal for 8.1/8.2

On Fri, 21 Jan 2005 12:17:08 -0800, Josh Berkus wrote:

> I'm proposing to expand both the coverage and number of "system views".

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

However, that could (and in my opinion: _should_) be fixed be enforcing
schema-wide constraint identifier uniqueness.

> pg_stats
>         ADD statstarget for each column
>         (the SET STATISTICS for each column)

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.

> pg_columns --> new view **
>         schemaname
>         tablename
>         columnname
>         datatype
>         typemodifiers (NOT NULL, default, etc)
>         comment

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

I could use this to more easily build user interfaces (forms).

--
Greetings from Troels Arvin, Copenhagen, Denmark