information_schema.sgml

PostgreSQL 8.1.4的源码 适用于Linux下的开源数据库系统

<!-- $PostgreSQL: pgsql/doc/src/sgml/information_schema.sgml,v 1.22.2.1 2005/12/08 20:46:54 petere Exp $ -->

<chapter id="information-schema">
 <title>The Information Schema</title>

 <indexterm zone="information-schema">
  <primary>information schema</primary>
 </indexterm>

 <para>
  The information schema consists of a set of views that contain
  information about the objects defined in the current database.  The
  information schema is defined in the SQL standard and can therefore
  be expected to be portable and remain stable &mdash; unlike the system
  catalogs, which are specific to
  <productname>PostgreSQL</productname> and are modelled after
  implementation concerns.  The information schema views do not,
  however, contain information about
  <productname>PostgreSQL</productname>-specific features; to inquire
  about those you need to query the system catalogs or other
  <productname>PostgreSQL</productname>-specific views.
 </para>

 <sect1 id="infoschema-schema">
  <title>The Schema</title>

  <para>
   The information schema itself is a schema named
   <literal>information_schema</literal>.  This schema automatically
   exists in all databases.  The owner of this schema is the initial
   database user in the cluster, and that user naturally has all the
   privileges on this schema, including the ability to drop it (but
   the space savings achieved by that are minuscule).
  </para>

  <para>
   By default, the information schema is not in the schema search
   path, so you need to access all objects in it through qualified
   names.  Since the names of some of the objects in the information
   schema are generic names that might occur in user applications, you
   should be careful if you want to put the information schema in the
   path.
  </para>
 </sect1>

 <sect1 id="infoschema-datatypes">
  <title>Data Types</title>

  <para>
   The columns of the information schema views use special data types
   that are defined in the information schema.  These are defined as
   simple domains over ordinary built-in types.  You should not use
   these types for work outside the information schema, but your
   applications must be prepared for them if they select from the
   information schema.
  </para>

  <para>
   These types are:

   <variablelist>
    <varlistentry>
     <term><type>cardinal_number</type></term>
     <listitem>
      <para>
       A nonnegative integer.
      </para>
     </listitem>
    </varlistentry>

    <varlistentry>
     <term><type>character_data</type></term>
     <listitem>
      <para>
       A character string (without specific maximum length).
      </para>
     </listitem>
    </varlistentry>

    <varlistentry>
     <term><type>sql_identifier</type></term>
     <listitem>
      <para>
       A character string.  This type is used for SQL identifiers, the
       type <type>character_data</type> is used for any other kind of
       text data.
      </para>
     </listitem>
    </varlistentry>

    <varlistentry>
     <term><type>time_stamp</type></term>
     <listitem>
      <para>
       A domain over the type <type>timestamp</type>
      </para>
     </listitem>
    </varlistentry>
   </variablelist>

   Every column in the information schema has one of these four types.
  </para>

  <para>
   Boolean (true/false) data is represented in the information schema
   by a column of type <type>character_data</type> that contains
   either <literal>YES</literal> or <literal>NO</literal>.  (The
   information schema was invented before the type
   <type>boolean</type> was added to the SQL standard, so this
   convention is necessary to keep the information schema backward
   compatible.)
  </para>
 </sect1>

 <sect1 id="infoschema-information-schema-catalog-name">
  <title><literal>information_schema_catalog_name</literal></title>

  <para>
   <literal>information_schema_catalog_name</literal> is a table that
   always contains one row and one column containing the name of the
   current database (current catalog, in SQL terminology).
  </para>

  <table>
   <title><literal>information_schema_catalog_name</literal> Columns</title>

   <tgroup cols="3">
    <thead>
     <row>
      <entry>Name</entry>
      <entry>Data Type</entry>
      <entry>Description</entry>
     </row>
    </thead>

    <tbody>
     <row>
      <entry><literal>catalog_name</literal></entry>
      <entry><type>sql_identifier</type></entry>
      <entry>Name of the database that contains this information schema</entry>
     </row>
    </tbody>
   </tgroup>
  </table>
 </sect1>

 <sect1 id="infoschema-applicable-roles">
  <title><literal>applicable_roles</literal></title>

  <para>
   The view <literal>applicable_roles</literal> identifies all groups
   that the current user is a member of.  (A role is the same thing as
   a group.)  Generally, it is better to use the view
   <literal>enabled_roles</literal> instead of this one; see also
   there.
  </para>

  <table>
   <title><literal>applicable_roles</literal> Columns</title>

   <tgroup cols="3">
    <thead>
     <row>
      <entry>Name</entry>
      <entry>Data Type</entry>
      <entry>Description</entry>
     </row>
    </thead>

    <tbody>
     <row>
      <entry><literal>grantee</literal></entry>
      <entry><type>sql_identifier</type></entry>
      <entry>Always the name of the current user</entry>
     </row>

     <row>
      <entry><literal>role_name</literal></entry>
      <entry><type>sql_identifier</type></entry>
      <entry>Name of a group</entry>
     </row>

     <row>
      <entry><literal>is_grantable</literal></entry>
      <entry><type>character_data</type></entry>
      <entry>Applies to a feature not available in <productname>PostgreSQL</></entry>
     </row>
    </tbody>
   </tgroup>
  </table>
 </sect1>

 <sect1 id="infoschema-check-constraints">
  <title><literal>check_constraints</literal></title>

  <para>
   The view <literal>check_constraints</literal> contains all check
   constraints, either defined on a table or on a domain, that are
   owned by the current user.  (The owner of the table or domain is
   the owner of the constraint.)
  </para>

  <table>
   <title><literal>check_constraints</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><literal>sql_identifier</literal></entry>
      <entry>Name of the database containing the constraint (always the current database)</entry>
     </row>

     <row>
      <entry><literal>constraint_schema</literal></entry>
      <entry><literal>sql_identifier</literal></entry>
      <entry>Name of the schema containing the constraint</entry>
     </row>

     <row>
      <entry><literal>constraint_name</literal></entry>
      <entry><literal>sql_identifier</literal></entry>
      <entry>Name of the constraint</entry>
     </row>

     <row>
      <entry><literal>check_clause</literal></entry>
      <entry><literal>character_data</literal></entry>
      <entry>The check expression of the check constraint</entry>
     </row>
    </tbody>
   </tgroup>
  </table>
 </sect1>

 <sect1 id="infoschema-column-domain-usage">
  <title><literal>column_domain_usage</literal></title>

  <para>
   The view <literal>column_domain_usage</literal> identifies all
   columns (of a table or a view) that make use of some domain defined
   in the current database and owned by the current user.
  </para>

  <table>
   <title><literal>column_domain_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>domain_catalog</literal></entry>
      <entry><type>sql_identifier</type></entry>
      <entry>Name of the database containing 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 containing the domain</entry>
     </row>

     <row>
      <entry><literal>domain_name</literal></entry>
      <entry><type>sql_identifier</type></entry>
      <entry>Name of the domain</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-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.  Privileges granted to groups are identified in the
   view <literal>role_column_grants</literal>.
  </para>

  <para>
   In <productname>PostgreSQL</productname>, 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 it only covers
   privilege types where column granularity is possible:
   <literal>SELECT</literal>, <literal>INSERT</literal>,
   <literal>UPDATE</literal>, <literal>REFERENCES</literal>.  If you
   want to make your applications fit for possible future
   developments, it is generally the right choice to use this view
   instead of <literal>table_privileges</literal> if one of those
   privilege types is concerned.
  </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 or group 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>INSERT</literal>, <literal>UPDATE</literal>, or
       <literal>REFERENCES</literal>
      </entry>
     </row>