From 596652d6eb35411781dcac07809375f83d501cf1 Mon Sep 17 00:00:00 2001 From: Peter Eisentraut Date: Tue, 17 Jun 2003 18:00:48 +0000 Subject: More information schema views. --- doc/src/sgml/information_schema.sgml | 779 +++++++++++++++++++++++++++++++++-- 1 file changed, 752 insertions(+), 27 deletions(-) (limited to 'doc/src') diff --git a/doc/src/sgml/information_schema.sgml b/doc/src/sgml/information_schema.sgml index 82760ecdc44..2d862623858 100644 --- a/doc/src/sgml/information_schema.sgml +++ b/doc/src/sgml/information_schema.sgml @@ -1,4 +1,4 @@ - + The Information Schema @@ -261,6 +261,176 @@ + + <literal>column_privileges</literal> + + + The view column_privileges identifies all + privileges granted on columns to the current user or by the current + user. There is one row for each combination of column, grantor, + and grantee. + + + + In PostgreSQL, you can only grant privileges on entire tables, not + individual columns. Therefore, this view contains the same + information as table_privileges, just + represented through one row for each column in each appropriate + table. But if you want to make your applications fit for possible + future developements, it is generally the right choice to use this + view instead of table_privileges. + + + + <literal>column_privileges</literal> Columns + + + + + Name + Data Type + Description + + + + + + grantor + sql_identifier + Name of the user that granted the privilege + + + + grantee + sql_identifier + Name of the user that the privilege was granted to + + + + table_catalog + sql_identifier + Name of the database that contains the table that contains the column (always the current database) + + + + table_schema + sql_identifier + Name of the schema that contains the table that contains the column + + + + table_name + sql_identifier + Name of the table that contains the column + + + + column_name + sql_identifier + Name of the column + + + + privilege_type + character_data + + Type of the privilege: SELECT, + DELETE, INSERT, + UPDATE, REFERENCES, or + TRIGGER + + + + + is_grantable + character_data + YES if the privilege is grantable, NO if not + + + +
+ + + + <literal>column_udt_usage</literal> + + + The view column_udt_usage identifies all columns + that use data types owned by the current user. Note that in + PostgreSQL, built-in data types behave like user-defined types, so + they are included here as well. See also for details. + + + + <literal>column_udt_usage</literal> Columns + + + + + Name + Data Type + Description + + + + + + udt_catalog + sql_identifier + + Name of the database that the column data type (the underlying + type of the domain, if applicable) is defined in (always the + current database) + + + + + udt_schema + sql_identifier + + Name of the schema that the column data type (the underlying + type of the domain, if applicable) is defined in + + + + + udt_name + sql_identifier + + Name of the column data type (the underlying type of the + domain, if applicable) + + + + + table_catalog + sql_identifier + Name of the database containing the table (always the current database) + + + + table_schema + sql_identifier + Name of the schema containing the table + + + + table_name + sql_identifier + Name of the table + + + + column_name + sql_identifier + Name of the column + + + +
+ + <literal>columns</literal> @@ -595,6 +765,91 @@ + + <literal>constraint_column_usage</literal> + + + The view constraint_column_usage identifies all + columns in the current database that are used by some constraint. + Only those columns are shown that are contained in a table owned + the current user. For a check constraint, this view identifies the + columns that are used in the check expression. For a foreign key + constraint, this view identifies the columns that the foreign key + references. For a unique or primary key constraint, this view + identifies the constrained columns. + + + + <literal>constraint_column_usage</literal> Columns + + + + + Name + Data Type + Description + + + + + + table_catalog + sql_identifier + + Name of the database that contains the table that contains the + column that is used by some constraint (always the current + database) + + + + + table_schema + sql_identifier + + Name of the schema that contains the table that contains the + column that is used by some constraint + + + + + table_name + sql_identifier + + Name of the table that contains the column that is used by some + constraint + + + + + column_name + sql_identifier + + Name of the column that is used by some constraint + + + + + constraint_catalog + sql_identifier + Name of the database that contains the constraint (always the current database) + + + + constraint_schema + sql_identifier + Name of the schema that contains the constraint + + + + constraint_name + sql_identifier + Name of the constraint + + + +
+ + <literal>constraint_table_usage</literal> @@ -605,9 +860,10 @@ table_constraints, which identifies all table constraints along with the table they are defined on.) For a foreign key constraint, this view identifies the table that the - foreign key references. Unique and primary key constraints simply - identify the table they belong to. Check constraints and not-null - constraints are not included in this view. + foreign key references. For a unique or primary key constraint, + this view simply identifies the table the constraint belongs to. + Check constraints and not-null constraints are not included in this + view. @@ -742,6 +998,69 @@
+ + <literal>domain_udt_usage</literal> + + + The view domain_udt_usage identifies all columns + that use data types owned by the current user. Note that in + PostgreSQL, built-in data types behave like user-defined types, so + they are included here as well. + + + + <literal>domain_udt_usage</literal> Columns + + + + + Name + Data Type + Description + + + + + + udt_catalog + sql_identifier + Name of the database that the domain data type is defined in (always the current database) + + + + udt_schema + sql_identifier + Name of the schema that the domain data type is defined in + + + + udt_name + sql_identifier + Name of the domain data type + + + + domain_catalog + sql_identifier + Name of the database that contains the domain (always the current database) + + + + domain_schema + sql_identifier + Name of the schema that contains the domain + + + + domain_name + sql_identifier + Name of the domain + + + +
+ + <literal>domains</literal> @@ -911,55 +1230,146 @@ Default expression of the domain - - udt_catalog + + udt_catalog + sql_identifier + Name of the database that the domain data type is defined in (always the current database) + + + + udt_schema + sql_identifier + Name of the schema that the domain data type is defined in + + + + udt_name + sql_identifier + Name of the domain data type + + + + scope_catalog + sql_identifier + Applies to a feature not available in PostgreSQL + + + + scope_schema + sql_identifier + Applies to a feature not available in PostgreSQL + + + + scope_name + sql_identifier + Applies to a feature not available in PostgreSQL + + + + maximum_cardinality + cardinal_number + Applies to a feature not available in PostgreSQL + + + + dtd_identifier + sql_identifier + + A unique identifier of the data type of the domain (The + specific format of the identifier is not defined and not + guaranteed to remain the same in future versions.) + + + + + + + + + <literal>key_column_usage</literal> + + + The view key_column_usage identifies all columns + in the current database that are restricted by some unique, primary + key, or foreign key constraint. Check constraints are not included + in this view. Only those columns are shown that are contained in a + table owned the current user. + + + + <literal>key_column_usage</literal> Columns + + + + + Name + Data Type + Description + + + + + + constraint_catalog sql_identifier - Name of the database that the domain data type is defined in (always the current database) + Name of the database that contains the constraint (always the current database) - udt_schema + constraint_schema sql_identifier - Name of the schema that the domain data type is defined in + Name of the schema that contains the constraint - udt_name + constraint_name sql_identifier - Name of the domain data type + Name of the constraint - scope_catalog + table_catalog sql_identifier - Applies to a feature not available in PostgreSQL + + Name of the database that contains the table that contains the + column that is restricted by some constraint (always the + current database) + - scope_schema + table_schema sql_identifier - Applies to a feature not available in PostgreSQL + + Name of the schema that contains the table that contains the + column that is restricted by some constraint + - scope_name + table_name sql_identifier - Applies to a feature not available in PostgreSQL + + Name of the table that contains the column that is restricted + by some constraint + - maximum_cardinality - cardinal_number - Applies to a feature not available in PostgreSQL + column_name + sql_identifier + + Name of the column that is restricted by some constraint + - dtd_identifier - sql_identifier + ordinal_position + cardinal_number - A unique identifier of the data type of the domain (The - specific format of the identifier is not defined and not - guaranteed to remain the same in future versions.) + Ordinal position of the column within the constraint key (count + starts at 1) @@ -1331,7 +1741,7 @@ grantor sql_identifier - Name of the user that granted the privileges + Name of the user that granted the privilege @@ -2397,7 +2807,7 @@ grantor sql_identifier - Name of the user that granted the privileges + Name of the user that granted the privilege @@ -2537,6 +2947,321 @@
+ + <literal>triggers</literal> + + + The view triggers contains all triggers defined + in the current database that are owned by the current user. (The + owner of the table is the owner of the trigger.) + + + + <literal>triggers</literal> Columns + + + + + Name + Data Type + Description + + + + + + trigger_catalog + sql_identifier + Name of the database that contains the trigger (always the current database) + + + + trigger_schema + sql_identifier + Name of the schema that contains the trigger + + + + trigger_name + sql_identifier + Name of the trigger + + + + event_manipulation + character_data + + Event that fires the trigger (INSERT, + UPDATE, or DELETE) + + + + + event_object_catalog + sql_identifier + + Name of the database that contains the table that the trigger + is defined on (always the current database) + + + + + event_object_schema + sql_identifier + Name of the schema that contains the table that the trigger is defined on + + + + event_object_name + sql_identifier + Name of the table that the trigger is defined on + + + + action_order + cardinal_number + Not yet implemented + + + + action_condition + character_data + Applies to a feature not available in PostgreSQL + + + + action_statement + character_data + + Statement that is executed by the trigger (currently always + EXECUTE PROCEDURE + function(...)) + + + + + action_orientation + character_data + + Identifies whether the trigger fires once for each processed + row or once for each statement (ROW or + STATEMENT) + + + + + condition_timing + character_data + + Time at which the trigger fires (BEFORE or + AFTER) + + + + + condition_reference_old_table + sql_identifier + Applies to a feature not available in PostgreSQL + + + + condition_reference_new_table + sql_identifier + Applies to a feature not available in PostgreSQL + + + +
+ + + Triggers in PostgreSQL have two incompatibilities with the SQL + standard that affect the representation in the information schema. + First, trigger names are local to the table in PostgreSQL, rather + than independent schema objects. Therefore there may be duplicate + trigger names defined in one schema, as long as they belong to + different tables. (trigger_catalog and + trigger_schema are really the values pertaining + to the table that the trigger is defined on.) Second, triggers can + be defined to fire on multiple events in PostgreSQL (e.g., + ON INSERT OR UPDATE), whereas the SQL standard + only allows one. If a trigger is defined to fire on multiple + events, it is represented as multiple rows in the information + schema, one for each type of event. As a consequence of these two + issues, the primary key of the view triggers is + really (trigger_catalog, trigger_schema, trigger_name, + event_object_name, event_manipulation) instead of + (trigger_catalog, trigger_schema, trigger_name), + which is what the SQL standard specifies. Nonetheless, if you + define your triggers in a manner that conforms with the SQL + standard (trigger names unique in the schema and only one event + type per trigger), this will not affect you. + + + + + <literal>view_column_usage</literal> + + + The view view_column_usage identifies all + columns that are used in the query expression of a view (the + SELECT statement that defines the view). A + column is only included if the current user is the owner of the + table that contains the column. + + + + + Columns of system tables are not included. This should be fixed + sometime. + + + + + <literal>view_column_usage</literal> Columns + + + + + Name + Data Type + Description + + + + + + view_catalog + sql_identifier + Name of the database that contains the view (always the current database) + + + + view_schema + sql_identifier + Name of the schema that contains the view + + + + view_name + sql_identifier + Name of the view + + + + table_catalog + sql_identifier + + Name of the database that contains the table that contains the + column that is used by the view (always the current database) + + + + + table_schema + sql_identifier + + Name of the schema that contains the table that contains the + column that is used by the view + + + + + table_name + sql_identifier + + Name of the table that contains the column that is used by the + view + + + + + column_name + sql_identifier + Name of the column that is used by the view + + + +
+ + + + <literal>view_table_usage</literal> + + + The view view_table_usage identifies all tables + that are used in the query expression of a view (the + SELECT statement that defines the view). A + table is only included if the current user is the owner of that + table. + + + + + System tables are not included. This should be fixed sometime. + + + + + <literal>view_table_usage</literal> Columns + + + + + Name + Data Type + Description + + + + + + view_catalog + sql_identifier + Name of the database that contains the view (always the current database) + + + + view_schema + sql_identifier + Name of the schema that contains the view + + + + view_name + sql_identifier + Name of the view + + + + table_catalog + sql_identifier + + Name of the database that contains the table the table that is + used by the view (always the current database) + + + + + table_schema + sql_identifier + + Name of the schema that contains the table that is used by the + view + + + + + table_name + sql_identifier + + Name of the table that is used by the view + + + + +
+ + <literal>views</literal> -- cgit v1.2.3