-<!-- $PostgreSQL: pgsql/doc/src/sgml/information_schema.sgml,v 1.43 2009/12/30 22:48:10 petere Exp $ -->
+<!-- doc/src/sgml/information_schema.sgml -->
<chapter id="information-schema">
<title>The Information Schema</title>
<productname>PostgreSQL</productname>-specific views.
</para>
+ <note>
+ <para>
+ When querying the database for constraint information, it is possible
+ for a standard-compliant query that expects to return one row to
+ return several. This is because the SQL standard requires constraint
+ names to be unique within a schema, but
+ <productname>PostgreSQL</productname> does not enforce this
+ restriction. <productname>PostgreSQL</productname>
+ automatically-generated constraint names avoid duplicates in the
+ same schema, but users can specify such duplicate names.
+ </para>
+
+ <para>
+ This problem can appear when querying information schema views such
+ as <literal>check_constraint_routine_usage</>,
+ <literal>check_constraints</>, <literal>domain_constraints</>, and
+ <literal>referential_constraints</>. Some other views have similar
+ issues but contain the table name to help distinguish duplicate
+ rows, e.g., <literal>constraint_column_usage</>,
+ <literal>constraint_table_usage</>, <literal>table_constraints</>.
+ </para>
+ </note>
+
+
<sect1 id="infoschema-schema">
<title>The Schema</title>
</para>
</sect1>
+ <sect1 id="infoschema-character-sets">
+ <title><literal>character_sets</literal></title>
+
+ <para>
+ The view <literal>character_sets</literal> identifies the character
+ sets available in the current database. Since PostgreSQL does not
+ support multiple character sets within one database, this view only
+ shows one, which is the database encoding.
+ </para>
+
+ <para>
+ Take note of how the following terms are used in the SQL standard:
+ <variablelist>
+ <varlistentry>
+ <term>character repertoire</term>
+ <listitem>
+ <para>
+ An abstract collection of characters, for
+ example <literal>UNICODE</literal>, <literal>UCS</literal>, or
+ <literal>LATIN1</literal>. Not exposed as an SQL object, but
+ visible in this view.
+ </para>
+ </listitem>
+ </varlistentry>
+
+ <varlistentry>
+ <term>character encoding form</term>
+ <listitem>
+ <para>
+ An encoding of some character repertoire. Most older character
+ repertoires only use one encoding form, and so there are no
+ separate names for them (e.g., <literal>LATIN1</literal> is an
+ encoding form applicable to the <literal>LATIN1</literal>
+ repertoire). But for example Unicode has the encoding forms
+ <literal>UTF8</literal>, <literal>UTF16</literal>, etc. (not
+ all supported by PostgreSQL). Encoding forms are not exposed
+ as an SQL object, but are visible in this view.
+ </para>
+ </listitem>
+ </varlistentry>
+
+ <varlistentry>
+ <term>character set</term>
+ <listitem>
+ <para>
+ A named SQL object that identifies a character repertoire, a
+ character encoding, and a default collation. A predefined
+ character set would typically have the same name as an encoding
+ form, but users could define other names. For example, the
+ character set <literal>UTF8</literal> would typically identify
+ the character repertoire <literal>UCS</literal>, encoding
+ form <literal>UTF8</literal>, and some default collation.
+ </para>
+ </listitem>
+ </varlistentry>
+ </variablelist>
+
+ You can think of an <quote>encoding</quote> in PostgreSQL either as
+ a character set or a character encoding form. They will have the
+ same name, and there can only be one in one database.
+ </para>
+
+ <table>
+ <title><literal>character_sets</literal> Columns</title>
+
+ <tgroup cols="3">
+ <thead>
+ <row>
+ <entry>Name</entry>
+ <entry>Data Type</entry>
+ <entry>Description</entry>
+ </row>
+ </thead>
+
+ <tbody>
+ <row>
+ <entry><literal>character_set_catalog</literal></entry>
+ <entry><literal>sql_identifier</literal></entry>
+ <entry>Character sets are currently not implemented as schema objects, so this column is null.</entry>
+ </row>
+
+ <row>
+ <entry><literal>character_set_schema</literal></entry>
+ <entry><literal>sql_identifier</literal></entry>
+ <entry>Character sets are currently not implemented as schema objects, so this column is null.</entry>
+ </row>
+
+ <row>
+ <entry><literal>character_set_name</literal></entry>
+ <entry><literal>sql_identifier</literal></entry>
+ <entry>Name of the character set, currently implemented as showing the name of the database encoding</entry>
+ </row>
+
+ <row>
+ <entry><literal>character_repertoire</literal></entry>
+ <entry><literal>sql_identifier</literal></entry>
+ <entry>Character repertoire, showing <literal>UCS</literal> if the encoding is <literal>UTF8</literal>, else just the encoding name</entry>
+ </row>
+
+ <row>
+ <entry><literal>form_of_use</literal></entry>
+ <entry><literal>sql_identifier</literal></entry>
+ <entry>Character encoding form, same as the database encoding</entry>
+ </row>
+
+ <row>
+ <entry><literal>default_collate_catalog</literal></entry>
+ <entry><literal>sql_identifier</literal></entry>
+ <entry>Name of the database containing the default collation (always the current database, if any collation is identified)</entry>
+ </row>
+
+ <row>
+ <entry><literal>default_collate_schema</literal></entry>
+ <entry><literal>sql_identifier</literal></entry>
+ <entry>Name of the schema containing the default collation</entry>
+ </row>
+
+ <row>
+ <entry><literal>default_collate_name</literal></entry>
+ <entry><literal>sql_identifier</literal></entry>
+ <entry>
+ Name of the default collation. The default collation is
+ identified as the collation that matches
+ the <literal>COLLATE</literal> and <literal>CTYPE</literal>
+ settings of the current database. If there is no such
+ collation, then this column and the associated schema and
+ catalog columns are null.
+ </entry>
+ </row>
+ </tbody>
+ </tgroup>
+ </table>
+ </sect1>
+
<sect1 id="infoschema-check-constraint-routine-usage">
<title><literal>check_constraint_routine_usage</literal></title>
</table>
</sect1>
+ <sect1 id="infoschema-collations">
+ <title><literal>collations</literal></title>
+
+ <para>
+ The view <literal>collations</literal> contains the collations
+ available in the current database.
+ </para>
+
+ <table>
+ <title><literal>collations</literal> Columns</title>
+
+ <tgroup cols="3">
+ <thead>
+ <row>
+ <entry>Name</entry>
+ <entry>Data Type</entry>
+ <entry>Description</entry>
+ </row>
+ </thead>
+
+ <tbody>
+ <row>
+ <entry><literal>collation_catalog</literal></entry>
+ <entry><literal>sql_identifier</literal></entry>
+ <entry>Name of the database containing the collation (always the current database)</entry>
+ </row>
+
+ <row>
+ <entry><literal>collation_schema</literal></entry>
+ <entry><literal>sql_identifier</literal></entry>
+ <entry>Name of the schema containing the collation</entry>
+ </row>
+
+ <row>
+ <entry><literal>collation_name</literal></entry>
+ <entry><literal>sql_identifier</literal></entry>
+ <entry>Name of the default collation</entry>
+ </row>
+
+ <row>
+ <entry><literal>pad_attribute</literal></entry>
+ <entry><literal>character_data</literal></entry>
+ <entry>
+ Always <literal>NO PAD</literal> (The alternative <literal>PAD
+ SPACE</literal> is not supported by PostgreSQL.)
+ </entry>
+ </row>
+ </tbody>
+ </tgroup>
+ </table>
+ </sect1>
+
+ <sect1 id="infoschema-collation-character-set-applicab"> <!-- max 44 characters -->
+ <title><literal>collation_character_set_applicability</literal></title>
+
+ <para>
+ The view <literal>collation_character_set_applicability</literal>
+ identifies which character set the available collations are
+ applicable to. In PostgreSQL, there is only one character set per
+ database (see explanation
+ in <xref linkend="infoschema-character-sets">), so this view does
+ not provide much useful information.
+ </para>
+
+ <table>
+ <title><literal>collation_character_set_applicability</literal> Columns</title>
+
+ <tgroup cols="3">
+ <thead>
+ <row>
+ <entry>Name</entry>
+ <entry>Data Type</entry>
+ <entry>Description</entry>
+ </row>
+ </thead>
+
+ <tbody>
+ <row>
+ <entry><literal>collation_catalog</literal></entry>
+ <entry><literal>sql_identifier</literal></entry>
+ <entry>Name of the database containing the collation (always the current database)</entry>
+ </row>
+
+ <row>
+ <entry><literal>collation_schema</literal></entry>
+ <entry><literal>sql_identifier</literal></entry>
+ <entry>Name of the schema containing the collation</entry>
+ </row>
+
+ <row>
+ <entry><literal>collation_name</literal></entry>
+ <entry><literal>sql_identifier</literal></entry>
+ <entry>Name of the default collation</entry>
+ </row>
+
+ <row>
+ <entry><literal>character_set_catalog</literal></entry>
+ <entry><literal>sql_identifier</literal></entry>
+ <entry>Character sets are currently not implemented as schema objects, so this column is null</entry>
+ </row>
+
+ <row>
+ <entry><literal>character_set_schema</literal></entry>
+ <entry><literal>sql_identifier</literal></entry>
+ <entry>Character sets are currently not implemented as schema objects, so this column is null</entry>
+ </row>
+
+ <row>
+ <entry><literal>character_set_name</literal></entry>
+ <entry><literal>sql_identifier</literal></entry>
+ <entry>Name of the character set</entry>
+ </row>
+ </tbody>
+ </tgroup>
+ </table>
+ </sect1>
+
<sect1 id="infoschema-column-domain-usage">
<title><literal>column_domain_usage</literal></title>
<para>
The view <literal>element_types</literal> contains the data type
- descriptors of the elements of arrays. When a table column,
+ descriptors of the elements of arrays. When a table column, composite-type attribute,
domain, function parameter, or function return value is defined to
be of an array type, the respective information schema view only
contains <literal>ARRAY</literal> in the column
SELECT c.column_name, c.data_type, e.data_type AS element_type
FROM information_schema.columns c LEFT JOIN information_schema.element_types e
ON ((c.table_catalog, c.table_schema, c.table_name, 'TABLE', c.dtd_identifier)
- = (e.object_catalog, e.object_schema, e.object_name, e.object_type, e.dtd_identifier))
+ = (e.object_catalog, e.object_schema, e.object_name, e.object_type, e.collection_type_identifier))
WHERE c.table_schema = '...' AND c.table_name = '...'
ORDER BY c.ordinal_position;
</programlisting>
<entry>
The type of the object that uses the array being described: one
of <literal>TABLE</literal> (the array is used by a column of
- that table), <literal>DOMAIN</literal> (the array is used by
- that domain), <literal>ROUTINE</literal> (the array is used by
- a parameter or the return data type of that function).
+ that table), <literal>USER-DEFINED TYPE</literal> (the array is
+ used by an attribute of that composite type),
+ <literal>DOMAIN</literal> (the array is used by that domain),
+ <literal>ROUTINE</literal> (the array is used by a parameter or
+ the return data type of that function).
</entry>
</row>
<row>
- <entry><literal>dtd_identifier</literal></entry>
+ <entry><literal>collection_type_identifier</literal></entry>
<entry><type>sql_identifier</type></entry>
<entry>
The identifier of the data type descriptor of the array being
- described
+ described. Use this to join with the
+ <literal>dtd_identifier</literal> columns of other information
+ schema views.
</entry>
</row>
<entry>Always null, because arrays always have unlimited maximum cardinality in <productname>PostgreSQL</></entry>
</row>
+ <row>
+ <entry><literal>dtd_identifier</literal></entry>
+ <entry><type>sql_identifier</type></entry>
+ <entry>
+ An identifier of the data type descriptor of the element. This
+ is currently not useful.
+ </entry>
+ </row>
</tbody>
</tgroup>
</table>
</table>
</sect1>
- <sect1 id="infoschema-key-column-usage">
- <title><literal>key_column_usage</literal></title>
+ <sect1 id="infoschema-foreign-table-options">
+ <title><literal>foreign_table_options</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 the current user
- has access to, by way of being the owner or having some privilege.
+ The view <literal>foreign_table_options</literal> contains all the
+ options defined for foreign tables in the current database. Only
+ those foreign tables are shown that the current user has access to
+ (by way of being the owner or having some privilege).
</para>
<table>
- <title><literal>key_column_usage</literal> Columns</title>
+ <title><literal>foreign_table_options</literal> Columns</title>
<tgroup cols="3">
<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><literal>foreign_table_catalog</literal></entry>
<entry><type>sql_identifier</type></entry>
- <entry>Name of the schema that contains the constraint</entry>
+ <entry>Name of the database that contains the foreign table (always the current database)</entry>
</row>
<row>
- <entry><literal>constraint_name</literal></entry>
+ <entry><literal>foreign_table_schema</literal></entry>
<entry><type>sql_identifier</type></entry>
- <entry>Name of the constraint</entry>
+ <entry>Name of the schema that contains the foreign table</entry>
</row>
<row>
- <entry><literal>table_catalog</literal></entry>
+ <entry><literal>foreign_table_name</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 this constraint (always the
- current database)
- </entry>
+ <entry>Name of the foreign table</entry>
</row>
<row>
- <entry><literal>table_schema</literal></entry>
+ <entry><literal>foreign_server_catalog</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 this constraint
- </entry>
+ <entry>Name of the database that the foreign server is defined in (always the current database)</entry>
</row>
<row>
- <entry><literal>table_name</literal></entry>
+ <entry><literal>foreign_server_name</literal></entry>
<entry><type>sql_identifier</type></entry>
- <entry>
- Name of the table that contains the column that is restricted
- by this constraint
- </entry>
+ <entry>Name of the foreign server</entry>
</row>
<row>
- <entry><literal>column_name</literal></entry>
+ <entry><literal>option_name</literal></entry>
<entry><type>sql_identifier</type></entry>
- <entry>
- Name of the column that is restricted by this 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>
+ <entry>Name of an option</entry>
</row>
<row>
- <entry><literal>position_in_unique_constraint</literal></entry>
- <entry><type>cardinal_number</type></entry>
- <entry>
- For a foreign-key constraint, ordinal position of the referenced
- column within its unique constraint (count starts at 1);
- otherwise null
- </entry>
+ <entry><literal>option_value</literal></entry>
+ <entry><type>character_data</type></entry>
+ <entry>Value of the option</entry>
</row>
</tbody>
</tgroup>
</table>
</sect1>
- <sect1 id="infoschema-parameters">
- <title><literal>parameters</literal></title>
+ <sect1 id="infoschema-foreign-tables">
+ <title><literal>foreign_tables</literal></title>
<para>
- The view <literal>parameters</literal> contains information about
- the parameters (arguments) of all functions in the current database.
+ The view <literal>foreign_tables</literal> contains all foreign
+ tables defined in the current database. Only those foreign
+ tables are shown that the current user has access to (by way of
+ being the owner or having some privilege).
+ </para>
+
+ <table>
+ <title><literal>foreign_tables</literal> Columns</title>
+
+ <tgroup cols="3">
+ <thead>
+ <row>
+ <entry>Name</entry>
+ <entry>Data Type</entry>
+ <entry>Description</entry>
+ </row>
+ </thead>
+
+ <tbody>
+ <row>
+ <entry><literal>foreign_table_catalog</literal></entry>
+ <entry><type>sql_identifier</type></entry>
+ <entry>Name of the database that the foreign table is defined in (always the current database)</entry>
+ </row>
+
+ <row>
+ <entry><literal>foreign_table_schema</literal></entry>
+ <entry><type>sql_identifier</type></entry>
+ <entry>Name of the schema that contains the foreign table</entry>
+ </row>
+
+ <row>
+ <entry><literal>foreign_table_name</literal></entry>
+ <entry><type>sql_identifier</type></entry>
+ <entry>Name of the foreign table</entry>
+ </row>
+
+ <row>
+ <entry><literal>foreign_server_catalog</literal></entry>
+ <entry><type>sql_identifier</type></entry>
+ <entry>Name of the database that the foreign server is defined in (always the current database)</entry>
+ </row>
+
+ <row>
+ <entry><literal>foreign_server_name</literal></entry>
+ <entry><type>sql_identifier</type></entry>
+ <entry>Name of the foreign server</entry>
+ </row>
+ </tbody>
+ </tgroup>
+ </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 the current user
+ has access to, by way of being the owner or having some privilege.
+ </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 this 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 this 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 this 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 this 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>
+
+ <row>
+ <entry><literal>position_in_unique_constraint</literal></entry>
+ <entry><type>cardinal_number</type></entry>
+ <entry>
+ For a foreign-key constraint, ordinal position of the referenced
+ column within its unique constraint (count starts at 1);
+ otherwise null
+ </entry>
+ </row>
+ </tbody>
+ </tgroup>
+ </table>
+ </sect1>
+
+ <sect1 id="infoschema-parameters">
+ <title><literal>parameters</literal></title>
+
+ <para>
+ The view <literal>parameters</literal> contains information about
+ the parameters (arguments) of all functions in the current database.
Only those functions are shown that the current user has access to
(by way of being the owner or having some privilege).
</para>
</tbody>
</tgroup>
</table>
+
</sect1>
<sect1 id="infoschema-role-column-grants">
difference between this view
and <literal>column_privileges</literal> is that this view omits
columns that have been made accessible to the current user by way
- of a grant to public.
+ of a grant to <literal>PUBLIC</literal>.
</para>
<table>
difference between this view
and <literal>routine_privileges</literal> is that this view omits
functions that have been made accessible to the current user by way
- of a grant to public.
+ of a grant to <literal>PUBLIC</literal>.
</para>
<table>
difference between this view
and <literal>table_privileges</literal> is that this view omits
tables that have been made accessible to the current user by way of
- a grant to public.
+ a grant to <literal>PUBLIC</literal>.
</para>
<table>
</table>
</sect1>
+ <sect1 id="infoschema-role-udt-grants">
+ <title><literal>role_udt_grants</literal></title>
+
+ <para>
+ The view <literal>role_udt_grants</literal> is intended to identify
+ <literal>USAGE</literal> privileges granted on user-defined types
+ where the grantor or grantee is a currently enabled role. Further
+ information can be found under
+ <literal>udt_privileges</literal>. The only effective difference
+ between this view and <literal>udt_privileges</literal> is that
+ this view omits objects that have been made accessible to the
+ current user by way of a grant to <literal>PUBLIC</literal>. Since
+ data types do not have real privileges in PostgreSQL, but only an
+ implicit grant to <literal>PUBLIC</literal>, this view is empty.
+ </para>
+
+ <table>
+ <title><literal>role_udt_grants</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>The name of the role that granted the privilege</entry>
+ </row>
+
+ <row>
+ <entry><literal>grantee</literal></entry>
+ <entry><type>sql_identifier</type></entry>
+ <entry>The name of the role that the privilege was granted to</entry>
+ </row>
+
+ <row>
+ <entry><literal>udt_catalog</literal></entry>
+ <entry><type>sql_identifier</type></entry>
+ <entry>Name of the database containing the type (always the current database)</entry>
+ </row>
+
+ <row>
+ <entry><literal>udt_schema</literal></entry>
+ <entry><type>sql_identifier</type></entry>
+ <entry>Name of the schema containing the type</entry>
+ </row>
+
+ <row>
+ <entry><literal>udt_name</literal></entry>
+ <entry><type>sql_identifier</type></entry>
+ <entry>Name of the type</entry>
+ </row>
+
+ <row>
+ <entry><literal>privilege_type</literal></entry>
+ <entry><type>character_data</type></entry>
+ <entry>Always <literal>TYPE USAGE</literal></entry>
+ </row>
+
+ <row>
+ <entry><literal>is_grantable</literal></entry>
+ <entry><type>yes_or_no</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-role-usage-grants">
<title><literal>role_usage_grants</literal></title>
<literal>usage_privileges</literal>. The only effective difference
between this view and <literal>usage_privileges</literal> is that
this view omits objects that have been made accessible to the
- current user by way of a grant to public.
+ current user by way of a grant to <literal>PUBLIC</literal>.
</para>
<table>
<row>
<entry><literal>object_type</literal></entry>
<entry><type>character_data</type></entry>
- <entry><literal>DOMAIN</literal> or <literal>FOREIGN DATA WRAPPER</literal> or <literal>FOREIGN SERVER</literal></entry>
+ <entry><literal>COLLATION</literal> or <literal>DOMAIN</literal> or <literal>FOREIGN DATA WRAPPER</literal> or <literal>FOREIGN SERVER</literal></entry>
</row>
<row>
</row>
<row>
- <entry><literal>maximum_value</literal></entry>
- <entry><type>cardinal_number</type></entry>
- <entry>Not yet implemented</entry>
+ <entry><literal>start_value</literal></entry>
+ <entry><type>character_data</type></entry>
+ <entry>The start value of the sequence</entry>
</row>
<row>
<entry><literal>minimum_value</literal></entry>
- <entry><type>cardinal_number</type></entry>
- <entry>Not yet implemented</entry>
+ <entry><type>character_data</type></entry>
+ <entry>The minimum value of the sequence</entry>
+ </row>
+
+ <row>
+ <entry><literal>maximum_value</literal></entry>
+ <entry><type>character_data</type></entry>
+ <entry>The maximum value of the sequence</entry>
</row>
<row>
<entry><literal>increment</literal></entry>
- <entry><type>cardinal_number</type></entry>
- <entry>Not yet implemented</entry>
+ <entry><type>character_data</type></entry>
+ <entry>The increment of the sequence</entry>
</row>
<row>
<entry><literal>cycle_option</literal></entry>
<entry><type>yes_or_no</type></entry>
- <entry>Not yet implemented</entry>
+ <entry><literal>YES</literal> if the sequence cycles, else <literal>NO</literal></entry>
</row>
</tbody>
</tgroup>
</table>
+
+ <para>
+ Note that in accordance with the SQL standard, the start, minimum,
+ maximum, and increment values are returned as character strings.
+ </para>
</sect1>
<sect1 id="infoschema-sql-features">
<entry>
Type of the table: <literal>BASE TABLE</literal> for a
persistent base table (the normal table type),
- <literal>VIEW</literal> for a view, or <literal>LOCAL
- TEMPORARY</literal> for a temporary table
+ <literal>VIEW</literal> for a view, <literal>FOREIGN TABLE</literal>
+ for a foreign table, or
+ <literal>LOCAL TEMPORARY</literal> for a temporary table
</entry>
</row>
<row>
<entry><literal>user_defined_type_catalog</literal></entry>
<entry><type>sql_identifier</type></entry>
- <entry>Applies to a feature not available in <productname>PostgreSQL</></entry>
+ <entry>
+ If the table is a typed table, the name of the database that
+ contains the underlying data type (always the current
+ database), else null.
+ </entry>
</row>
<row>
<entry><literal>user_defined_type_schema</literal></entry>
<entry><type>sql_identifier</type></entry>
- <entry>Applies to a feature not available in <productname>PostgreSQL</></entry>
+ <entry>
+ If the table is a typed table, the name of the schema that
+ contains the underlying data type, else null.
+ </entry>
</row>
<row>
<entry><literal>user_defined_type_name</literal></entry>
<entry><type>sql_identifier</type></entry>
- <entry>Applies to a feature not available in <productname>PostgreSQL</></entry>
+ <entry>
+ If the table is a typed table, the name of the underlying data
+ type, else null.
+ </entry>
</row>
<row>
<row>
<entry><literal>is_typed</literal></entry>
<entry><type>yes_or_no</type></entry>
- <entry>Applies to a feature not available in <productname>PostgreSQL</></entry>
+ <entry><literal>YES</literal> if the table is a typed table, <literal>NO</literal> if not</entry>
</row>
<row>
</table>
</sect1>
- <sect1 id="infoschema-triggers">
- <title><literal>triggers</literal></title>
+ <sect1 id="infoschema-triggered-update-columns">
+ <title><literal>triggered_update_columns</literal></title>
<para>
- The view <literal>triggers</literal> contains all triggers defined
- in the current database on tables that the current user owns or has
- some non-SELECT privilege on.
+ For triggers in the current database that specify a column list
+ (like <literal>UPDATE OF column1, column2</literal>), the
+ view <literal>triggered_update_columns</literal> identifies these
+ columns. Triggers that do not specify a column list are not
+ included in this view. Only those columns are shown that the
+ current user owns or has some non-SELECT privilege on.
</para>
<table>
- <title><literal>triggers</literal> Columns</title>
+ <title><literal>triggered_update_columns</literal> Columns</title>
<tgroup cols="3">
<thead>
</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>
</row>
<row>
- <entry><literal>action_order</literal></entry>
- <entry><type>cardinal_number</type></entry>
- <entry>Not yet implemented</entry>
+ <entry><literal>event_object_column</literal></entry>
+ <entry><type>sql_identifier</type></entry>
+ <entry>Name of the column that the trigger is defined on</entry>
+ </row>
+ </tbody>
+ </tgroup>
+ </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 on tables and views that the current user owns
+ or has some non-SELECT privilege on.
+ </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_table</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>
</row>
<row>
- <entry><literal>condition_timing</literal></entry>
+ <entry><literal>action_timing</literal></entry>
<entry><type>character_data</type></entry>
<entry>
- Time at which the trigger fires (<literal>BEFORE</literal> or
- <literal>AFTER</literal>)
+ Time at which the trigger fires (<literal>BEFORE</literal>,
+ <literal>AFTER</literal>, or <literal>INSTEAD OF</literal>)
</entry>
</row>
<row>
- <entry><literal>condition_reference_old_table</literal></entry>
+ <entry><literal>action_reference_old_table</literal></entry>
<entry><type>sql_identifier</type></entry>
<entry>Applies to a feature not available in <productname>PostgreSQL</></entry>
</row>
<row>
- <entry><literal>condition_reference_new_table</literal></entry>
+ <entry><literal>action_reference_new_table</literal></entry>
<entry><type>sql_identifier</type></entry>
<entry>Applies to a feature not available in <productname>PostgreSQL</></entry>
</row>
<row>
- <entry><literal>condition_reference_old_row</literal></entry>
+ <entry><literal>action_reference_old_row</literal></entry>
<entry><type>sql_identifier</type></entry>
<entry>Applies to a feature not available in <productname>PostgreSQL</></entry>
</row>
<row>
- <entry><literal>condition_reference_new_row</literal></entry>
+ <entry><literal>action_reference_new_row</literal></entry>
<entry><type>sql_identifier</type></entry>
<entry>Applies to a feature not available in <productname>PostgreSQL</></entry>
</row>
Triggers in <productname>PostgreSQL</productname> have two
incompatibilities with the SQL standard that affect the
representation in the information schema. First, trigger names are
- local to
the table in <productname>PostgreSQL</productname>, rather
+ local to
each table in <productname>PostgreSQL</productname>, rather
than being independent schema objects. Therefore there can be duplicate
- trigger names defined in one schema, as long as they belong to
+ trigger names defined in one schema, so 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
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_table, event_manipulation)</literal> instead of
+ <literal>(trigger_catalog, trigger_schema, event_object_table,
+ trigger_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>
+
+ <note>
+ <para>
+ Prior to <productname>PostgreSQL</> 9.1, this view's columns
+ <structfield>action_timing</structfield>,
+ <structfield>action_reference_old_table</structfield>,
+ <structfield>action_reference_new_table</structfield>,
+ <structfield>action_reference_old_row</structfield>, and
+ <structfield>action_reference_new_row</structfield>
+ were named
+ <structfield>condition_timing</structfield>,
+ <structfield>condition_reference_old_table</structfield>,
+ <structfield>condition_reference_new_table</structfield>,
+ <structfield>condition_reference_old_row</structfield>, and
+ <structfield>condition_reference_new_row</structfield>
+ respectively.
+ That was how they were named in the SQL:1999 standard.
+ The new naming conforms to SQL:2003 and later.
+ </para>
+ </note>
+ </sect1>
+
+ <sect1 id="infoschema-udt-privileges">
+ <title><literal>udt_privileges</literal></title>
+
+ <para>
+ The view <literal>udt_privileges</literal> is intended to identify
+ <literal>USAGE</literal> privileges granted on user-defined types
+ to a currently enabled role or by a currently enabled role. Since
+ data types do not have real privileges
+ in <productname>PostgreSQL</productname>, this view shows implicit
+ non-grantable <literal>USAGE</literal> privileges granted by the
+ owner to <literal>PUBLIC</literal> for all types, including
+ built-in ones (except domains,
+ see <xref linkend="infoschema-usage-privileges"> for that).
+ </para>
+
+ <table>
+ <title><literal>udt_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 role that granted the privilege</entry>
+ </row>
+
+ <row>
+ <entry><literal>grantee</literal></entry>
+ <entry><type>sql_identifier</type></entry>
+ <entry>Name of the role that the privilege was granted to</entry>
+ </row>
+
+ <row>
+ <entry><literal>udt_catalog</literal></entry>
+ <entry><type>sql_identifier</type></entry>
+ <entry>Name of the database containing the type (always the current database)</entry>
+ </row>
+
+ <row>
+ <entry><literal>udt_schema</literal></entry>
+ <entry><type>sql_identifier</type></entry>
+ <entry>Name of the schema containing the type</entry>
+ </row>
+
+ <row>
+ <entry><literal>udt_name</literal></entry>
+ <entry><type>sql_identifier</type></entry>
+ <entry>Name of the type</entry>
+ </row>
+
+ <row>
+ <entry><literal>privilege_type</literal></entry>
+ <entry><type>character_data</type></entry>
+ <entry>Always <literal>TYPE USAGE</literal></entry>
+ </row>
+
+ <row>
+ <entry><literal>is_grantable</literal></entry>
+ <entry><type>yes_or_no</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-usage-privileges">
<literal>USAGE</literal> privileges granted on various kinds of
objects to a currently enabled role or by a currently enabled role.
In <productname>PostgreSQL</productname>, this currently applies to
- domains, foreign-data wrappers, and foreign servers. There is one
+ collations, domains, foreign-data wrappers, and foreign servers. There is one
row for each combination of object, grantor, and grantee.
</para>
<para>
- Since domains do not have real privileges
+ Since collations and domains do not have real privileges
in <productname>PostgreSQL</productname>, this view shows implicit
non-grantable <literal>USAGE</literal> privileges granted by the
- owner to <literal>PUBLIC</literal> for all domains. The other
+ owner to <literal>PUBLIC</literal> for all collations and domains. The other
object types, however, show real privileges.
</para>
<row>
<entry><literal>object_type</literal></entry>
<entry><type>character_data</type></entry>
- <entry><literal>DOMAIN</literal> or <literal>FOREIGN DATA WRAPPER</literal> or <literal>FOREIGN SERVER</literal></entry>
+ <entry><literal>COLLATION</literal> or <literal>DOMAIN</literal> or <literal>FOREIGN DATA WRAPPER</literal> or <literal>FOREIGN SERVER</literal></entry>
</row>
<row>
</table>
</sect1>
+ <sect1 id="infoschema-user-defined-types">
+ <title><literal>user_defined_types</literal></title>
+
+ <para>
+ The view <literal>user_defined_types</literal> currently contains
+ all composite types defined in the current database.
+ </para>
+
+ <para>
+ SQL knows about two kinds of user-defined types: structured types
+ (also known as composite types
+ in <productname>PostgreSQL</productname>) and distinct types (not
+ implemented in <productname>PostgreSQL</productname>). To be
+ future-proof, use the
+ column <literal>user_defined_type_category</literal> to
+ differentiate between these. Other user-defined types such as base
+ types and enums, which are <productname>PostgreSQL</productname>
+ extensions, are not shown here. For domains,
+ see <xref linkend="infoschema-domains"> instead.
+ </para>
+
+ <table>
+ <title><literal>user_defined_types</literal> Columns</title>
+
+ <tgroup cols="3">
+ <thead>
+ <row>
+ <entry>Name</entry>
+ <entry>Data Type</entry>
+ <entry>Description</entry>
+ </row>
+ </thead>
+
+ <tbody>
+ <row>
+ <entry><literal>user_defined_type_catalog</literal></entry>
+ <entry><type>sql_identifier</type></entry>
+ <entry>Name of the database that contains the type (always the current database)</entry>
+ </row>
+
+ <row>
+ <entry><literal>user_defined_type_schema</literal></entry>
+ <entry><type>sql_identifier</type></entry>
+ <entry>Name of the schema that contains the type</entry>
+ </row>
+
+ <row>
+ <entry><literal>user_defined_type_name</literal></entry>
+ <entry><type>sql_identifier</type></entry>
+ <entry>Name of the type</entry>
+ </row>
+
+ <row>
+ <entry><literal>user_defined_type_category</literal></entry>
+ <entry><type>character_data</type></entry>
+ <entry>
+ Currently always <literal>STRUCTURED</literal>
+ </entry>
+ </row>
+
+ <row>
+ <entry><literal>is_instantiable</literal></entry>
+ <entry><type>yes_or_no</type></entry>
+ <entry>Applies to a feature not available in <productname>PostgreSQL</></entry>
+ </row>
+
+ <row>
+ <entry><literal>is_final</literal></entry>
+ <entry><type>yes_or_no</type></entry>
+ <entry>Applies to a feature not available in <productname>PostgreSQL</></entry>
+ </row>
+
+ <row>
+ <entry><literal>ordering_form</literal></entry>
+ <entry><type>character_data</type></entry>
+ <entry>Applies to a feature not available in <productname>PostgreSQL</></entry>
+ </row>
+
+ <row>
+ <entry><literal>ordering_category</literal></entry>
+ <entry><type>character_data</type></entry>
+ <entry>Applies to a feature not available in <productname>PostgreSQL</></entry>
+ </row>
+
+ <row>
+ <entry><literal>ordering_routine_catalog</literal></entry>
+ <entry><type>sql_identifier</type></entry>
+ <entry>Applies to a feature not available in <productname>PostgreSQL</></entry>
+ </row>
+
+ <row>
+ <entry><literal>ordering_routine_schema</literal></entry>
+ <entry><type>sql_identifier</type></entry>
+ <entry>Applies to a feature not available in <productname>PostgreSQL</></entry>
+ </row>
+
+ <row>
+ <entry><literal>ordering_routine_name</literal></entry>
+ <entry><type>sql_identifier</type></entry>
+ <entry>Applies to a feature not available in <productname>PostgreSQL</></entry>
+ </row>
+
+ <row>
+ <entry><literal>reference_type</literal></entry>
+ <entry><type>character_data</type></entry>
+ <entry>Applies to a feature not available in <productname>PostgreSQL</></entry>
+ </row>
+
+ <row>
+ <entry><literal>data_type</literal></entry>
+ <entry><type>character_data</type></entry>
+ <entry>
+ Always <literal>USER-DEFINED TYPE</literal> (for joining
+ against <literal>object_type</literal> columns in other
+ views)
+ </entry>
+ </row>
+
+ <row>
+ <entry><literal>character_maximum_length</literal></entry>
+ <entry><type>cardinal_number</type></entry>
+ <entry>Applies to a feature not available in <productname>PostgreSQL</></entry>
+ </row>
+
+ <row>
+ <entry><literal>character_octet_length</literal></entry>
+ <entry><type>cardinal_number</type></entry>
+ <entry>Applies to a feature not available in <productname>PostgreSQL</></entry>
+ </row>
+
+ <row>
+ <entry><literal>character_set_catalog</literal></entry>
+ <entry><type>sql_identifier</type></entry>
+ <entry>Applies to a feature not available in <productname>PostgreSQL</></entry>
+ </row>
+
+ <row>
+ <entry><literal>character_set_schema</literal></entry>
+ <entry><type>sql_identifier</type></entry>
+ <entry>Applies to a feature not available in <productname>PostgreSQL</></entry>
+ </row>
+
+ <row>
+ <entry><literal>character_set_name</literal></entry>
+ <entry><type>sql_identifier</type></entry>
+ <entry>Applies to a feature not available in <productname>PostgreSQL</></entry>
+ </row>
+
+ <row>
+ <entry><literal>collation_catalog</literal></entry>
+ <entry><type>sql_identifier</type></entry>
+ <entry>Applies to a feature not available in <productname>PostgreSQL</></entry>
+ </row>
+
+ <row>
+ <entry><literal>collation_schema</literal></entry>
+ <entry><type>sql_identifier</type></entry>
+ <entry>Applies to a feature not available in <productname>PostgreSQL</></entry>
+ </row>
+
+ <row>
+ <entry><literal>collation_name</literal></entry>
+ <entry><type>sql_identifier</type></entry>
+ <entry>Applies to a feature not available in <productname>PostgreSQL</></entry>
+ </row>
+
+ <row>
+ <entry><literal>numeric_precision</literal></entry>
+ <entry><type>cardinal_number</type></entry>
+ <entry>Applies to a feature not available in <productname>PostgreSQL</></entry>
+ </row>
+
+ <row>
+ <entry><literal>numeric_precision_radix</literal></entry>
+ <entry><type>cardinal_number</type></entry>
+ <entry>Applies to a feature not available in <productname>PostgreSQL</></entry>
+ </row>
+
+ <row>
+ <entry><literal>numeric_scale</literal></entry>
+ <entry><type>cardinal_number</type></entry>
+ <entry>Applies to a feature not available in <productname>PostgreSQL</></entry>
+ </row>
+
+ <row>
+ <entry><literal>datetime_precision</literal></entry>
+ <entry><type>cardinal_number</type></entry>
+ <entry>Applies to a feature not available in <productname>PostgreSQL</></entry>
+ </row>
+
+ <row>
+ <entry><literal>interval_type</literal></entry>
+ <entry><type>character_data</type></entry>
+ <entry>Applies to a feature not available in <productname>PostgreSQL</></entry>
+ </row>
+
+ <row>
+ <entry><literal>interval_precision</literal></entry>
+ <entry><type>character_data</type></entry>
+ <entry>Applies to a feature not available in <productname>PostgreSQL</></entry>
+ </row>
+
+ <row>
+ <entry><literal>source_dtd_identifier</literal></entry>
+ <entry><type>sql_identifier</type></entry>
+ <entry>Applies to a feature not available in <productname>PostgreSQL</></entry>
+ </row>
+
+ <row>
+ <entry><literal>ref_dtd_identifier</literal></entry>
+ <entry><type>sql_identifier</type></entry>
+ <entry>Applies to a feature not available in <productname>PostgreSQL</></entry>
+ </row>
+ </tbody>
+ </tgroup>
+ </table>
+ </sect1>
+
<sect1 id="infoschema-user-mapping-options">
<title><literal>user_mapping_options</literal></title>
<row>
<entry><literal>is_trigger_updatable</literal></entry>
<entry><type>yes_or_no</type></entry>
- <entry>Applies to a feature not available in <productname>PostgreSQL</></entry>
+ <entry>
+ <literal>YES</> if the view has an <literal>INSTEAD OF</>
+ <command>UPDATE</> trigger defined on it, <literal>NO</> if not
+ </entry>
</row>
<row>
<entry><literal>is_trigger_deletable</literal></entry>
<entry><type>yes_or_no</type></entry>
- <entry>Applies to a feature not available in <productname>PostgreSQL</></entry>
+ <entry>
+ <literal>YES</> if the view has an <literal>INSTEAD OF</>
+ <command>DELETE</> trigger defined on it, <literal>NO</> if not
+ </entry>
</row>
<row>
<entry><literal>is_trigger_insertable_into</literal></entry>
<entry><type>yes_or_no</type></entry>
- <entry>Applies to a feature not available in <productname>PostgreSQL</></entry>
+ <entry>
+ <literal>YES</> if the view has an <literal>INSTEAD OF</>
+ <command>INSERT</> trigger defined on it, <literal>NO</> if not
+ </entry>
</row>
</tbody>
</tgroup>
OSDN Git Service
