diff options
Diffstat (limited to 'doc/src/sgml/information_schema.sgml')
| -rw-r--r-- | doc/src/sgml/information_schema.sgml | 739 |
1 files changed, 732 insertions, 7 deletions
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 @@ -<!-- $Header: /cvsroot/pgsql/doc/src/sgml/information_schema.sgml,v 1.3 2003/06/05 16:08:47 petere Exp $ --> +<!-- $Header: /cvsroot/pgsql/doc/src/sgml/information_schema.sgml,v 1.4 2003/06/17 18:00:48 petere Exp $ --> <chapter id="information-schema"> <title>The Information Schema</title> @@ -261,6 +261,176 @@ </table> </sect1> + <sect1 id="infoschema-column-privileges"> + <title><literal>column_privileges</literal></title> + + <para> + The view <literal>column_privileges</literal> 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. + </para> + + <para> + In PostgreSQL, you can only grant privileges on entire tables, not + individual columns. Therefore, this view contains the same + information as <literal>table_privileges</literal>, 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 <literal>table_privileges</literal>. + </para> + + <table> + <title><literal>column_privileges</literal> Columns</title> + + <tgroup cols="3"> + <thead> + <row> + <entry>Name</entry> + <entry>Data Type</entry> + <entry>Description</entry> + </row> + </thead> + + <tbody> + <row> + <entry><literal>grantor</literal></entry> + <entry><type>sql_identifier</type></entry> + <entry>Name of the user that granted the privilege</entry> + </row> + + <row> + <entry><literal>grantee</literal</entry> + <entry><type>sql_identifier</type></entry> + <entry>Name of the user that the privilege was granted to</entry> + </row> + + <row> + <entry><literal>table_catalog</literal></entry> + <entry><type>sql_identifier</type></entry> + <entry>Name of the database that contains the table that contains the column (always the current database)</entry> + </row> + + <row> + <entry><literal>table_schema</literal</entry> + <entry><type>sql_identifier</type></entry> + <entry>Name of the schema that contains the table that contains the column</entry> + </row> + + <row> + <entry><literal>table_name</literal</entry> + <entry><type>sql_identifier</type></entry> + <entry>Name of the table that contains the column</entry> + </row> + + <row> + <entry><literal>column_name</literal</entry> + <entry><type>sql_identifier</type></entry> + <entry>Name of the column</entry> + </row> + + <row> + <entry><literal>privilege_type</literal</entry> + <entry><type>character_data</type></entry> + <entry> + Type of the privilege: <literal>SELECT</literal>, + <literal>DELETE</literal>, <literal>INSERT</literal>, + <literal>UPDATE</literal>, <literal>REFERENCES</literal>, or + <literal>TRIGGER</literal> + </entry> + </row> + + <row> + <entry><literal>is_grantable</literal></entry> + <entry><type>character_data</type></entry> + <entry><literal>YES</literal> if the privilege is grantable, <literal>NO</literal> if not</entry> + </row> + </tbody> + </tgroup> + </table> + </sect1> + + <sect1 id="infoschema-column-udt-usage"> + <title><literal>column_udt_usage</literal></title> + + <para> + The view <literal>column_udt_usage</literal> 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 <xref + linkend="infoschema-columns"> for details. + </para> + + <table> + <title><literal>column_udt_usage</literal> Columns</title> + + <tgroup cols="3"> + <thead> + <row> + <entry>Name</entry> + <entry>Data Type</entry> + <entry>Description</entry> + </row> + </thead> + + <tbody> + <row> + <entry><literal>udt_catalog</literal></entry> + <entry><type>sql_identifier</type></entry> + <entry> + Name of the database that the column data type (the underlying + type of the domain, if applicable) is defined in (always the + current database) + </entry> + </row> + + <row> + <entry><literal>udt_schema</literal></entry> + <entry><type>sql_identifier</type></entry> + <entry> + Name of the schema that the column data type (the underlying + type of the domain, if applicable) is defined in + </entry> + </row> + + <row> + <entry><literal>udt_name</literal></entry> + <entry><type>sql_identifier</type></entry> + <entry> + Name of the column data type (the underlying type of the + domain, if applicable) + </entry> + </row> + + <row> + <entry><literal>table_catalog</literal></entry> + <entry><type>sql_identifier</type></entry> + <entry>Name of the database containing the table (always the current database)</entry> + </row> + + <row> + <entry><literal>table_schema</literal></entry> + <entry><type>sql_identifier</type></entry> + <entry>Name of the schema containing the table</entry> + </row> + + <row> + <entry><literal>table_name</literal></entry> + <entry><type>sql_identifier</type></entry> + <entry>Name of the table</entry> + </row> + + <row> + <entry><literal>column_name</literal></entry> + <entry><type>sql_identifier</type></entry> + <entry>Name of the column</entry> + </row> + </tbody> + </tgroup> + </table> + </sect1> + <sect1 id="infoschema-columns"> <title><literal>columns</literal></title> @@ -595,6 +765,91 @@ </para> </sect1> + <sect1 id="infoschema-constraint-column-usage"> + <title><literal>constraint_column_usage</literal></title> + + <para> + The view <literal>constraint_column_usage</literal> 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. + </para> + + <table> + <title><literal>constraint_column_usage</literal> Columns</title> + + <tgroup cols="3"> + <thead> + <row> + <entry>Name</entry> + <entry>Data Type</entry> + <entry>Description</entry> + </row> + </thead> + + <tbody> + <row> + <entry><literal>table_catalog</literal></entry> + <entry><type>sql_identifier</type></entry> + <entry> + Name of the database that contains the table that contains the + column that is used by some constraint (always the current + database) + </entry> + </row> + + <row> + <entry><literal>table_schema</literal</entry> + <entry><type>sql_identifier</type></entry> + <entry> + Name of the schema that contains the table that contains the + column that is used by some constraint + </entry> + </row> + + <row> + <entry><literal>table_name</literal</entry> + <entry><type>sql_identifier</type></entry> + <entry> + Name of the table that contains the column that is used by some + constraint + </entry> + </row> + + <row> + <entry><literal>column_name</literal</entry> + <entry><type>sql_identifier</type></entry> + <entry> + Name of the column that is used by some constraint + </entry> + </row> + + <row> + <entry><literal>constraint_catalog</literal></entry> + <entry><type>sql_identifier</type></entry> + <entry>Name of the database that contains the constraint (always the current database)</entry> + </row> + + <row> + <entry><literal>constraint_schema</literal</entry> + <entry><type>sql_identifier</type></entry> + <entry>Name of the schema that contains the constraint</entry> + </row> + + <row> + <entry><literal>constraint_name</literal</entry> + <entry><type>sql_identifier</type></entry> + <entry>Name of the constraint</entry> + </row> + </tbody> + </tgroup> + </table> + </sect1> + <sect1 id="infoschema-constraint-table-usage"> <title><literal>constraint_table_usage</literal></title> @@ -605,9 +860,10 @@ <literal>table_constraints</literal>, 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. </para> <table> @@ -742,6 +998,69 @@ </table> </sect1> + <sect1 id="infoschema-domain-udt-usage"> + <title><literal>domain_udt_usage</literal></title> + + <para> + The view <literal>domain_udt_usage</literal> 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. + </para> + + <table> + <title><literal>domain_udt_usage</literal> Columns</title> + + <tgroup cols="3"> + <thead> + <row> + <entry>Name</entry> + <entry>Data Type</entry> + <entry>Description</entry> + </row> + </thead> + + <tbody> + <row> + <entry><literal>udt_catalog</literal></entry> + <entry><type>sql_identifier</type></entry> + <entry>Name of the database that the domain data type is defined in (always the current database)</entry> + </row> + + <row> + <entry><literal>udt_schema</literal></entry> + <entry><type>sql_identifier</type></entry> + <entry>Name of the schema that the domain data type is defined in</entry> + </row> + + <row> + <entry><literal>udt_name</literal></entry> + <entry><type>sql_identifier</type></entry> + <entry>Name of the domain data type</entry> + </row> + + <row> + <entry><literal>domain_catalog</literal></entry> + <entry><type>sql_identifier</type></entry> + <entry>Name of the database that contains the domain (always the current database)</entry> + </row> + + <row> + <entry><literal>domain_schema</literal</entry> + <entry><type>sql_identifier</type></entry> + <entry>Name of the schema that contains the domain</entry> + </row> + + <row> + <entry><literal>domain_name</literal</entry> + <entry><type>sql_identifier</type></entry> + <entry>Name of the domain</entry> + </row> + </tbody> + </tgroup> + </table> + </sect1> + <sect1 id="infoschema-domains"> <title><literal>domains</literal></title> @@ -911,7 +1230,7 @@ <entry>Default expression of the domain</entry> </row> - <row> + <row> <entry><literal>udt_catalog</literal></entry> <entry><type>sql_identifier</type></entry> <entry>Name of the database that the domain data type is defined in (always the current database)</entry> @@ -967,6 +1286,97 @@ </table> </sect1> + <sect1 id="infoschema-key-column-usage"> + <title><literal>key_column_usage</literal></title> + + <para> + The view <literal>key_column_usage</literal> 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. + </para> + + <table> + <title><literal>key_column_usage</literal> Columns</title> + + <tgroup cols="3"> + <thead> + <row> + <entry>Name</entry> + <entry>Data Type</entry> + <entry>Description</entry> + </row> + </thead> + + <tbody> + <row> + <entry><literal>constraint_catalog</literal></entry> + <entry><type>sql_identifier</type></entry> + <entry>Name of the database that contains the constraint (always the current database)</entry> + </row> + + <row> + <entry><literal>constraint_schema</literal</entry> + <entry><type>sql_identifier</type></entry> + <entry>Name of the schema that contains the constraint</entry> + </row> + + <row> + <entry><literal>constraint_name</literal</entry> + <entry><type>sql_identifier</type></entry> + <entry>Name of the constraint</entry> + </row> + + <row> + <entry><literal>table_catalog</literal></entry> + <entry><type>sql_identifier</type></entry> + <entry> + Name of the database that contains the table that contains the + column that is restricted by some constraint (always the + current database) + </entry> + </row> + + <row> + <entry><literal>table_schema</literal</entry> + <entry><type>sql_identifier</type></entry> + <entry> + Name of the schema that contains the table that contains the + column that is restricted by some constraint + </entry> + </row> + + <row> + <entry><literal>table_name</literal</entry> + <entry><type>sql_identifier</type></entry> + <entry> + Name of the table that contains the column that is restricted + by some constraint + </entry> + </row> + + <row> + <entry><literal>column_name</literal</entry> + <entry><type>sql_identifier</type></entry> + <entry> + Name of the column that is restricted by some constraint + </entry> + </row> + + <row> + <entry><literal>ordinal_position</literal</entry> + <entry><type>cardinal_number</type></entry> + <entry> + Ordinal position of the column within the constraint key (count + starts at 1) + </entry> + </row> + </tbody> + </tgroup> + </table> + </sect1> + <sect1 id="infoschema-parameters"> <title><literal>parameters</literal></title> @@ -1331,7 +1741,7 @@ <row> <entry><literal>grantor</literal></entry> <entry><type>sql_identifier</type></entry> - <entry>Name of the user that granted the privileges</entry> + <entry>Name of the user that granted the privilege</entry> </row> <row> @@ -2397,7 +2807,7 @@ <row> <entry><literal>grantor</literal></entry> <entry><type>sql_identifier</type></entry> - <entry>Name of the user that granted the privileges</entry> + <entry>Name of the user that granted the privilege</entry> </row> <row> @@ -2537,6 +2947,321 @@ </table> </sect1> + <sect1 id="infoschema-triggers"> + <title><literal>triggers</literal></title> + + <para> + The view <literal>triggers</literal> 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.) + </para> + + <table> + <title><literal>triggers</literal> Columns</title> + + <tgroup cols="3"> + <thead> + <row> + <entry>Name</entry> + <entry>Data Type</entry> + <entry>Description</entry> + </row> + </thead> + + <tbody> + <row> + <entry><literal>trigger_catalog</literal></entry> + <entry><type>sql_identifier</type></entry> + <entry>Name of the database that contains the trigger (always the current database)</entry> + </row> + + <row> + <entry><literal>trigger_schema</literal</entry> + <entry><type>sql_identifier</type></entry> + <entry>Name of the schema that contains the trigger</entry> + </row> + + <row> + <entry><literal>trigger_name</literal</entry> + <entry><type>sql_identifier</type></entry> + <entry>Name of the trigger</entry> + </row> + + <row> + <entry><literal>event_manipulation</literal</entry> + <entry><type>character_data</type></entry> + <entry> + Event that fires the trigger (<literal>INSERT</literal>, + <literal>UPDATE</literal>, or <literal>DELETE</literal>) + </entry> + </row> + + <row> + <entry><literal>event_object_catalog</literal></entry> + <entry><type>sql_identifier</type></entry> + <entry> + Name of the database that contains the table that the trigger + is defined on (always the current database) + </entry> + </row> + + <row> + <entry><literal>event_object_schema</literal</entry> + <entry><type>sql_identifier</type></entry> + <entry>Name of the schema that contains the table that the trigger is defined on</entry> + </row> + + <row> + <entry><literal>event_object_name</literal</entry> + <entry><type>sql_identifier</type></entry> + <entry>Name of the table that the trigger is defined on</entry> + </row> + + <row> + <entry><literal>action_order</literal</entry> + <entry><type>cardinal_number</type></entry> + <entry>Not yet implemented</entry> + </row> + + <row> + <entry><literal>action_condition</literal</entry> + <entry><type>character_data</type></entry> + <entry>Applies to a feature not available in PostgreSQL</entry> + </row> + + <row> + <entry><literal>action_statement</literal</entry> + <entry><type>character_data</type></entry> + <entry> + Statement that is executed by the trigger (currently always + <literal>EXECUTE PROCEDURE + <replaceable>function</replaceable>(...)</literal>) + </entry> + </row> + + <row> + <entry><literal>action_orientation</literal</entry> + <entry><type>character_data</type></entry> + <entry> + Identifies whether the trigger fires once for each processed + row or once for each statement (<literal>ROW</literal> or + <literal>STATEMENT</literal>) + </entry> + </row> + + <row> + <entry><literal>condition_timing</literal</entry> + <entry><type>character_data</type></entry> + <entry> + Time at which the trigger fires (<literal>BEFORE</literal> or + <literal>AFTER</literal>) + </entry> + </row> + + <row> + <entry><literal>condition_reference_old_table</literal</entry> + <entry><type>sql_identifier</type></entry> + <entry>Applies to a feature not available in PostgreSQL</entry> + </row> + + <row> + <entry><literal>condition_reference_new_table</literal</entry> + <entry><type>sql_identifier</type></entry> + <entry>Applies to a feature not available in PostgreSQL</entry> + </row> + </tbody> + </tgroup> + </table> + + <para> + 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. (<literal>trigger_catalog</literal> and + <literal>trigger_schema</literal> 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., + <literal>ON INSERT OR UPDATE</literal>), 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 <literal>triggers</literal> is + really <literal>(trigger_catalog, trigger_schema, trigger_name, + event_object_name, event_manipulation)</literal> instead of + <literal>(trigger_catalog, trigger_schema, trigger_name)</literal>, + 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. + </para> + </sect1> + + <sect1 id="infoschema-view-column-usage"> + <title><literal>view_column_usage</literal></title> + + <para> + The view <literal>view_column_usage</literal> identifies all + columns that are used in the query expression of a view (the + <command>SELECT</command> statement that defines the view). A + column is only included if the current user is the owner of the + table that contains the column. + </para> + + <note> + <para> + Columns of system tables are not included. This should be fixed + sometime. + </para> + </note> + + <table> + <title><literal>view_column_usage</literal> Columns</title> + + <tgroup cols="3"> + <thead> + <row> + <entry>Name</entry> + <entry>Data Type</entry> + <entry>Description</entry> + </row> + </thead> + + <tbody> + <row> + <entry><literal>view_catalog</literal></entry> + <entry><type>sql_identifier</type></entry> + <entry>Name of the database that contains the view (always the current database)</entry> + </row> + + <row> + <entry><literal>view_schema</literal</entry> + <entry><type>sql_identifier</type></entry> + <entry>Name of the schema that contains the view</entry> + </row> + + <row> + <entry><literal>view_name</literal</entry> + <entry><type>sql_identifier</type></entry> + <entry>Name of the view</entry> + </row> + + <row> + <entry><literal>table_catalog</literal></entry> + <entry><type>sql_identifier</type></entry> + <entry> + Name of the database that contains the table that contains the + column that is used by the view (always the current database) + </entry> + </row> + + <row> + <entry><literal>table_schema</literal</entry> + <entry><type>sql_identifier</type></entry> + <entry> + Name of the schema that contains the table that contains the + column that is used by the view + </entry> + </row> + + <row> + <entry><literal>table_name</literal</entry> + <entry><type>sql_identifier</type></entry> + <entry> + Name of the table that contains the column that is used by the + view + </entry> + </row> + + <row> + <entry><literal>column_name</literal</entry> + <entry><type>sql_identifier</type></entry> + <entry>Name of the column that is used by the view</entry> + </row> + </tbody> + </tgroup> + </table> + </sect1> + + <sect1 id="infoschema-view-table-usage"> + <title><literal>view_table_usage</literal></title> + + <para> + The view <literal>view_table_usage</literal> identifies all tables + that are used in the query expression of a view (the + <command>SELECT</command> statement that defines the view). A + table is only included if the current user is the owner of that + table. + </para> + + <note> + <para> + System tables are not included. This should be fixed sometime. + </para> + </note> + + <table> + <title><literal>view_table_usage</literal> Columns</title> + + <tgroup cols="3"> + <thead> + <row> + <entry>Name</entry> + <entry>Data Type</entry> + <entry>Description</entry> + </row> + </thead> + + <tbody> + <row> + <entry><literal>view_catalog</literal></entry> + <entry><type>sql_identifier</type></entry> + <entry>Name of the database that contains the view (always the current database)</entry> + </row> + + <row> + <entry><literal>view_schema</literal</entry> + <entry><type>sql_identifier</type></entry> + <entry>Name of the schema that contains the view</entry> + </row> + + <row> + <entry><literal>view_name</literal</entry> + <entry><type>sql_identifier</type></entry> + <entry>Name of the view</entry> + </row> + + <row> + <entry><literal>table_catalog</literal></entry> + <entry><type>sql_identifier</type></entry> + <entry> + Name of the database that contains the table the table that is + used by the view (always the current database) + </entry> + </row> + + <row> + <entry><literal>table_schema</literal</entry> + <entry><type>sql_identifier</type></entry> + <entry> + Name of the schema that contains the table that is used by the + view + </entry> + </row> + + <row> + <entry><literal>table_name</literal</entry> + <entry><type>sql_identifier</type></entry> + <entry> + Name of the table that is used by the view + </entry> + </row> + </tbody> + </tgroup> + </table> + </sect1> + <sect1 id="infoschema-views"> <title><literal>views</literal></title> |