Extending System Views: proposal for 8.1/8.2 - Mailing list pgsql-hackers

Folks,

This is for 8.1, or for 8.2 if we have a no-initdb cycle for 8.1.  

I'm proposing to expand both the coverage and number of "system views".  Our
system views are an extremely useful way to get data about the system if
you're not on PSQL.   They are a better idea than using the underlying system
tables, both becuase the system table output can be kind of cryptic, and
because the system tables may change but it will be easy to maintain the
views the same.

Therefore, I want to run my proposed design past the team, because I'd like to
build system views we can live with for the next 3-4 versions, which will
allow GUI and library builders to have a reliable, static interface onto the
system objects.  Suggestions & adjustments, please!   It shouldn't take me
long to write these with a clear spec.

(oh, and information_schema really doesn't cover this because the SQL spec is
rather limited in what objects it describes)

pg_tables
        ADD comment

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

pg_user
        ADD groups (array)

pg_functions --> create new view
        schemaname
        functionname
        functionowner
        parameters (array)
        returntype
        functionsettings  (things like STABLE)
        functionsource
        comment

pg_views
        ADD comment

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

pg_aggregates --> new view **
        schemaname
        aggregatename
        aggregateowner
        datatype
        initvalue
        transfunction
        finalfunction
        comment
        
pg_operators --> new view **
        schemaname
        operatorname
        operatorowner
        operatortype
        datatypes (array)
        operatorfunction
        comment

pg_schemas --> new view
        schemaname
        schemaowner
        defaulttablespace
        comment

pg_triggers --> new view ***
        schemaname
        tablename
        triggername
        triggerowner
        triggerfunction
        conditions (update, insert, etc.)
        modifiers (deferrable, etc.)
        enabled
        comment

pg_foriegnkeys --> new view ****
        parentschema
        parenttable
        parentcolumns (array)
        childschema
        childtable
        childcolumns (array)

Views I think will be wanted by I've not really figured out how to define yet:
pg_types
pg_domains
pg_constraints
pg_groups

NOTES & QUESTIONS:

** = for these three views, there are an enourmous number of system
aggregates, operators, etc.   I'm wondering if I should hide the system ones,
or simply trust the user to filter by schema?

*** = since there will be a seperate FK view, pg_triggers will omit FK
constrainttriggers.

**** = I've used the non-canon terms "parent" and "child" here.   The problem
is that the standard terms are completely confusing and unintuitive, such as
"referring" and "referenced".     Other suggestions are welcome.

So, feedback before I start writing SQL?        

Oh, also what file are the system views defined in?


--
--Josh

Josh Berkus
Aglio Database Solutions
San Francisco