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(at)fetter(dot)org http://fetter.org/
phone: +1 510 893 6100 mobile: +1 415 235 3778

Remember to vote!