ddl.sgml

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

</programlisting>
    You can even omit the schema name, in which case the schema name
    will be the same as the user name.  See <xref
    linkend="ddl-schemas-patterns"> for how this can be useful.
   </para>

   <para>
    Schema names beginning with <literal>pg_</> are reserved for
    system purposes and may not be created by users.
   </para>
  </sect2>

  <sect2 id="ddl-schemas-public">
   <title>The Public Schema</title>

   <indexterm zone="ddl-schemas-public">
    <primary>schema</primary>
    <secondary>public</secondary>
   </indexterm>

   <para>
    In the previous sections we created tables without specifying any
    schema names.  By default, such tables (and other objects) are
    automatically put into a schema named <quote>public</quote>.  Every new
    database contains such a schema.  Thus, the following are equivalent:
<programlisting>
CREATE TABLE products ( ... );
</programlisting>
    and
<programlisting>
CREATE TABLE public.products ( ... );
</programlisting>
   </para>
  </sect2>

  <sect2 id="ddl-schemas-path">
   <title>The Schema Search Path</title>

   <indexterm>
    <primary>search path</primary>
   </indexterm>

   <indexterm>
    <primary>unqualified name</primary>
   </indexterm>

   <indexterm>
    <primary>name</primary>
    <secondary>unqualified</secondary>
   </indexterm>

   <para>
    Qualified names are tedious to write, and it's often best not to
    wire a particular schema name into applications anyway.  Therefore
    tables are often referred to by <firstterm>unqualified names</>,
    which consist of just the table name.  The system determines which table
    is meant by following a <firstterm>search path</>, which is a list
    of schemas to look in.  The first matching table in the search path
    is taken to be the one wanted.  If there is no match in the search
    path, an error is reported, even if matching table names exist
    in other schemas in the database.
   </para>

   <indexterm>
    <primary>schema</primary>
    <secondary>current</secondary>
   </indexterm>

   <para>
    The first schema named in the search path is called the current schema.
    Aside from being the first schema searched, it is also the schema in
    which new tables will be created if the <command>CREATE TABLE</>
    command does not specify a schema name.
   </para>

   <indexterm>
    <primary>search_path</primary>
   </indexterm>

   <para>
    To show the current search path, use the following command:
<programlisting>
SHOW search_path;
</programlisting>
    In the default setup this returns:
<screen>
 search_path
--------------
 $user,public
</screen>
    The first element specifies that a schema with the same name as
    the current user is to be searched.  If no such schema exists,
    the entry is ignored.  The second element refers to the
    public schema that we have seen already.
   </para>

   <para>
    The first schema in the search path that exists is the default
    location for creating new objects.  That is the reason that by
    default objects are created in the public schema.  When objects
    are referenced in any other context without schema qualification
    (table modification, data modification, or query commands) the
    search path is traversed until a matching object is found.
    Therefore, in the default configuration, any unqualified access
    again can only refer to the public schema.
   </para>

   <para>
    To put our new schema in the path, we use
<programlisting>
SET search_path TO myschema,public;
</programlisting>
    (We omit the <literal>$user</literal> here because we have no
    immediate need for it.)  And then we can access the table without
    schema qualification:
<programlisting>
DROP TABLE mytable;
</programlisting>
    Also, since <literal>myschema</literal> is the first element in
    the path, new objects would by default be created in it.
   </para>

   <para>
    We could also have written
<programlisting>
SET search_path TO myschema;
</programlisting>
    Then we no longer have access to the public schema without
    explicit qualification.  There is nothing special about the public
    schema except that it exists by default.  It can be dropped, too.
   </para>

   <para>
    See also <xref linkend="functions-info"> for other ways to manipulate
    the schema search path.
   </para>

   <para>
    The search path works in the same way for data type names, function names,
    and operator names as it does for table names.  Data type and function
    names can be qualified in exactly the same way as table names.  If you
    need to write a qualified operator name in an expression, there is a
    special provision: you must write
<synopsis>
<literal>OPERATOR(</><replaceable>schema</><literal>.</><replaceable>operator</><literal>)</>
</synopsis>
    This is needed to avoid syntactic ambiguity.  An example is
<programlisting>
SELECT 3 OPERATOR(pg_catalog.+) 4;
</programlisting>
    In practice one usually relies on the search path for operators,
    so as not to have to write anything so ugly as that.
   </para>
  </sect2>

  <sect2 id="ddl-schemas-priv">
   <title>Schemas and Privileges</title>

   <indexterm zone="ddl-schemas-priv">
    <primary>privilege</primary>
    <secondary sortas="schemas">for schemas</secondary>
   </indexterm>

   <para>
    By default, users cannot access any objects in schemas they do not
    own.  To allow that, the owner of the schema needs to grant the
    <literal>USAGE</literal> privilege on the schema.  To allow users
    to make use of the objects in the schema, additional privileges
    may need to be granted, as appropriate for the object.
   </para>

   <para>
    A user can also be allowed to create objects in someone else's
    schema.  To allow that, the <literal>CREATE</literal> privilege on
    the schema needs to be granted.  Note that by default, everyone
    has <literal>CREATE</literal> and <literal>USAGE</literal> privileges on
    the schema
    <literal>public</literal>.  This allows all users that are able to
    connect to a given database to create objects in its
    <literal>public</literal> schema.  If you do
    not want to allow that, you can revoke that privilege:
<programlisting>
REVOKE CREATE ON SCHEMA public FROM PUBLIC;
</programlisting>
    (The first <quote>public</quote> is the schema, the second
    <quote>public</quote> means <quote>every user</quote>.  In the
    first sense it is an identifier, in the second sense it is a
    key word, hence the different capitalization; recall the
    guidelines from <xref linkend="sql-syntax-identifiers">.)
   </para>
  </sect2>

  <sect2 id="ddl-schemas-catalog">
   <title>The System Catalog Schema</title>

   <indexterm zone="ddl-schemas-catalog">
    <primary>system catalog</primary>
    <secondary>schema</secondary>
   </indexterm>

   <para>
    In addition to <literal>public</> and user-created schemas, each
    database contains a <literal>pg_catalog</> schema, which contains
    the system tables and all the built-in data types, functions, and
    operators.  <literal>pg_catalog</> is always effectively part of
    the search path.  If it is not named explicitly in the path then
    it is implicitly searched <emphasis>before</> searching the path's
    schemas.  This ensures that built-in names will always be
    findable.  However, you may explicitly place
    <literal>pg_catalog</> at the end of your search path if you
    prefer to have user-defined names override built-in names.
   </para>

   <para>
    In <productname>PostgreSQL</productname> versions before 7.3,
    table names beginning with <literal>pg_</> were reserved.  This is
    no longer true: you may create such a table name if you wish, in
    any non-system schema.  However, it's best to continue to avoid
    such names, to ensure that you won't suffer a conflict if some
    future version defines a system table named the same as your
    table.  (With the default search path, an unqualified reference to
    your table name would be resolved as the system table instead.)
    System tables will continue to follow the convention of having
    names beginning with <literal>pg_</>, so that they will not
    conflict with unqualified user-table names so long as users avoid
    the <literal>pg_</> prefix.
   </para>
  </sect2>

  <sect2 id="ddl-schemas-patterns">
   <title>Usage Patterns</title>

   <para>
    Schemas can be used to organize your data in many ways.  There are
    a few usage patterns that are recommended and are easily supported by
    the default configuration:
    <itemizedlist>
     <listitem>
      <para>
       If you do not create any schemas then all users access the
       public schema implicitly.  This simulates the situation where
       schemas are not available at all.  This setup is mainly
       recommended when there is only a single user or a few cooperating
       users in a database.  This setup also allows smooth transition
       from the non-schema-aware world.
      </para>
     </listitem>

     <listitem>
      <para>
       You can create a schema for each user with the same name as
       that user.  Recall that the default search path starts with
       <literal>$user</literal>, which resolves to the user name.
       Therefore, if each user has a separate schema, they access their
       own schemas by default.
      </para>

      <para>
       If you use this setup then you might also want to revoke access
       to the public schema (or drop it altogether), so users are
       truly constrained to their own schemas.
      </para>
     </listitem>

     <listitem>
      <para>
       To install shared applications (tables to be used by everyone,
       additional functions provided by third parties, etc.), put them
       into separate schemas.  Remember to grant appropriate
       privileges to allow the other users to access them.  Users can
       then refer to these additional objects by qualifying the names
       with a schema name, or they can put the additional schemas into
       their search path, as they choose.
      </para>
     </listitem>
    </itemizedlist>
   </para>
  </sect2>

  <sect2 id="ddl-schemas-portability">
   <title>Portability</title>

   <para>
    In the SQL standard, the notion of objects in the same schema
    being owned by different users does not exist.  Moreover, some
    implementations do not allow you to create schemas that have a
    different name than their owner.  In fact, the concepts of schema
    and user are nearly equivalent in a database system that
    implements only the basic schema support specified in the
    standard.  Therefore, many users consider qualified names to
    really consist of
    <literal><replaceable>username</>.<replaceable>tablename</></literal>.
    This is how <productname>PostgreSQL</productname> will effectively
    behave if you create a per-user schema for every user.
   </para>

   <para>
    Also, there is no concept of a <literal>public</> schema in the
    SQL standard.  For maximum conformance to the standard, you should
    not use (perhaps even remove) the <literal>public</> schema.
   </para>

   <para>
    Of course, some SQL database systems might not implement schemas
    at all, or provide namespace support by allowing (possibly
    limited) cross-database access.  If you need to work with those
    systems, then maximum portability would be achieved by not using
    schemas at all.
   </para>
  </sect2>
 </sect1>

 <sect1 id="ddl-inherit">
  <title>Inheritance</title>

  <indexterm>
   <primary>inheritance</primary>
  </indexterm>

  <indexterm>
   <primary>table</primary>
   <secondary>inheritance</secondary>
  </indexterm>

  <para>
   <productname>PostgreSQL</productname> implements table inheritance
   which can be a useful tool for database designers.  (SQL:1999 and
   later define a type inheritance feature, which differs in many
   respects from the features described here.)
  </para>

  <para>
   Let's start with an example: suppose we are trying to build a data
   model for cities.  Each state has many cities, but only one
   capital. We want to be able to quickly retrieve the capital city
   for any particular state. This can be done by creating two tables,
   one for state capitals and one for cities that are not
   capitals. However, what happens when we want to ask for data about
   a city, regardless of whether it is a capital or not? The
   inheritance feature can help to resolve this problem. We define the
   <structname>capitals</structname> table so that it inherits from
   <structname>cities</structname>:

<programlisting>
CREATE TABLE cities (
    name            text,
    population      float,
    altitude        int     -- in feet
);

CREATE TABLE capitals (
    state           char(2)
) INHERITS (cities);
</programlisting>

   In this case, the <structname>capitals</> table <firstterm>inherits</>
   all the columns of its parent table, <structname>cities</>. State
   capitals also have an extra column, <structfield>state</>, that shows
   their state.
  </para>

  <para>
   In <productname>PostgreSQL</productname>, a table can inherit from
   zero or more other tables, and a query can reference either all
   rows of a table or all rows of a table plus all of its descendant tables.
   The latter behavior is the default.
   For example, the following query finds the names of all cities,
   including state capitals, that are located at an altitude over
   500ft:

<programlisting>
SELECT name, altitude
    FROM cities
    WHERE altitude &gt; 500;
</programlisting>

   Given the sample data from the <productname>PostgreSQL</productname>
   tutorial (see <xref linkend="tutorial-sql-intro">), this returns:

<programlisting>
   name    | altitude
-----------+----------
 Las Vegas |     2174
 Mariposa  |     1953
 Madison   |      845
</programlisting>
  </para>

  <para>
   On the other hand, the following query finds all the cities that
   are not state capitals and are situated