Re: Extending System Views: proposal for 8.1/8.2

On Fri, Jan 21, 2005 at 12:17:08PM -0800, Josh Berkus wrote:
> 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

I don't know how this fits in, but it would be *very* nice to have
SQLSTATE meta-information available via SQL.  I've sent in a patch for
this.

Cheers,
D
-- 
David Fetter david@fetter.org http://fetter.org/
phone: +1 510 893 6100   mobile: +1 415 235 3778

Remember to vote!