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 ---------------------------(end of broadcast)--------------------------- TIP 8: explain analyze is your friend
