select.sgml

postgresql8.3.4源码,开源数据库

    clauses can be written if it is necessary to specify different locking
    behavior for different tables.  If the same table is mentioned (or
    implicitly affected) by both <literal>FOR UPDATE</literal> and
    <literal>FOR SHARE</literal> clauses, then it is processed as
    <literal>FOR UPDATE</literal>.  Similarly, a table is processed
    as <literal>NOWAIT</> if that is specified in any of the clauses
    affecting it.
   </para>

   <para>
    <literal>FOR UPDATE</literal> and <literal>FOR SHARE</literal> cannot be
    used in contexts where returned rows cannot be clearly identified with
    individual table rows; for example they cannot be used with aggregation.
   </para>

  <caution>
   <para>
    Avoid locking a row and then modifying it within a later savepoint or
    <application>PL/pgSQL</application> exception block.  A subsequent
    rollback would cause the lock to be lost.  For example:
<programlisting>
BEGIN;
SELECT * FROM mytable WHERE key = 1 FOR UPDATE;
SAVEPOINT s;
UPDATE mytable SET ... WHERE key = 1;
ROLLBACK TO s;
</programlisting>
    After the <command>ROLLBACK</>, the row is effectively unlocked, rather
    than returned to its pre-savepoint state of being locked but not modified.
    This hazard occurs if a row locked in the current transaction is updated
    or deleted, or if a shared lock is upgraded to exclusive: in all these
    cases, the former lock state is forgotten.  If the transaction is then
    rolled back to a state between the original locking command and the
    subsequent change, the row will appear not to be locked at all.  This is
    an implementation deficiency which will be addressed in a future release
    of <productname>PostgreSQL</productname>.
   </para>
  </caution>

  <caution>
   <para>
    It is possible for a <command>SELECT</> command using both
    <literal>LIMIT</literal> and  <literal>FOR UPDATE/SHARE</literal>
    clauses to return fewer rows than specified by <literal>LIMIT</literal>.
    This is because <literal>LIMIT</> is applied first.  The command
    selects the specified number of rows,
    but might then block trying to obtain lock on one or more of them.
    Once the <literal>SELECT</> unblocks, the row might have been deleted
    or updated so that it does not meet the query <literal>WHERE</> condition
    anymore, in which case it will not be returned.
   </para>
  </caution>
  </refsect2>
 </refsect1>

 <refsect1>
  <title>Examples</title>

  <para>
   To join the table <literal>films</literal> with the table
   <literal>distributors</literal>:

<programlisting>
SELECT f.title, f.did, d.name, f.date_prod, f.kind
    FROM distributors d, films f
    WHERE f.did = d.did

       title       | did |     name     | date_prod  |   kind
-------------------+-----+--------------+------------+----------
 The Third Man     | 101 | British Lion | 1949-12-23 | Drama
 The African Queen | 101 | British Lion | 1951-08-11 | Romantic
 ...
</programlisting>
  </para>

  <para>
   To sum the column <literal>len</literal> of all films and group
   the results by <literal>kind</literal>:

<programlisting>
SELECT kind, sum(len) AS total FROM films GROUP BY kind;

   kind   | total
----------+-------
 Action   | 07:34
 Comedy   | 02:58
 Drama    | 14:28
 Musical  | 06:42
 Romantic | 04:38
</programlisting>
  </para>

  <para>
   To sum the column <literal>len</literal> of all films, group
   the results by <literal>kind</literal> and show those group totals
   that are less than 5 hours:

<programlisting>
SELECT kind, sum(len) AS total
    FROM films
    GROUP BY kind
    HAVING sum(len) &lt; interval '5 hours';

   kind   | total
----------+-------
 Comedy   | 02:58
 Romantic | 04:38
</programlisting>
  </para>

  <para>
   The following two examples are identical ways of sorting the individual
   results according to the contents of the second column
   (<literal>name</literal>):

<programlisting>
SELECT * FROM distributors ORDER BY name;
SELECT * FROM distributors ORDER BY 2;

 did |       name
-----+------------------
 109 | 20th Century Fox
 110 | Bavaria Atelier
 101 | British Lion
 107 | Columbia
 102 | Jean Luc Godard
 113 | Luso films
 104 | Mosfilm
 103 | Paramount
 106 | Toho
 105 | United Artists
 111 | Walt Disney
 112 | Warner Bros.
 108 | Westward
</programlisting>
  </para>

  <para>
   The next example shows how to obtain the union of the tables
   <literal>distributors</literal> and
   <literal>actors</literal>, restricting the results to those that begin
   with the letter W in each table.  Only distinct rows are wanted, so the
   key word <literal>ALL</literal> is omitted.

<programlisting>
distributors:               actors:
 did |     name              id |     name
-----+--------------        ----+----------------
 108 | Westward               1 | Woody Allen
 111 | Walt Disney            2 | Warren Beatty
 112 | Warner Bros.           3 | Walter Matthau
 ...                         ...

SELECT distributors.name
    FROM distributors
    WHERE distributors.name LIKE 'W%'
UNION
SELECT actors.name
    FROM actors
    WHERE actors.name LIKE 'W%';

      name
----------------
 Walt Disney
 Walter Matthau
 Warner Bros.
 Warren Beatty
 Westward
 Woody Allen
</programlisting>
  </para>

  <para>
   This example shows how to use a function in the <literal>FROM</>
   clause, both with and without a column definition list:

<programlisting>
CREATE FUNCTION distributors(int) RETURNS SETOF distributors AS $$
    SELECT * FROM distributors WHERE did = $1;
$$ LANGUAGE SQL;

SELECT * FROM distributors(111);
 did |    name
-----+-------------
 111 | Walt Disney

CREATE FUNCTION distributors_2(int) RETURNS SETOF record AS $$
    SELECT * FROM distributors WHERE did = $1;
$$ LANGUAGE SQL;

SELECT * FROM distributors_2(111) AS (f1 int, f2 text);
 f1  |     f2
-----+-------------
 111 | Walt Disney
</programlisting>
  </para>
 </refsect1>
 
 <refsect1>
  <title>Compatibility</title>

  <para>
   Of course, the <command>SELECT</command> statement is compatible
   with the SQL standard.  But there are some extensions and some
   missing features.
  </para>
  
  <refsect2>
   <title>Omitted <literal>FROM</literal> Clauses</title>

   <para>
    <productname>PostgreSQL</productname> allows one to omit the
    <literal>FROM</literal> clause.  It has a straightforward use to
    compute the results of simple expressions:
<programlisting>
SELECT 2+2;

 ?column?
----------
        4
</programlisting>
    Some other <acronym>SQL</acronym> databases cannot do this except
    by introducing a dummy one-row table from which to do the
    <command>SELECT</command>.
   </para>

   <para>
    Note that if a <literal>FROM</literal> clause is not specified,
    the query cannot reference any database tables. For example, the
    following query is invalid:
<programlisting>
SELECT distributors.* WHERE distributors.name = 'Westward';
</programlisting>
    <productname>PostgreSQL</productname> releases prior to
    8.1 would accept queries of this form, and add an implicit entry
    to the query's <literal>FROM</literal> clause for each table
    referenced by the query. This is no longer the default behavior,
    because it does not comply with the SQL standard, and is
    considered by many to be error-prone. For compatibility with
    applications that rely on this behavior the <xref
    linkend="guc-add-missing-from"> configuration variable can be
    enabled.
   </para>
  </refsect2>

  <refsect2>
   <title>The <literal>AS</literal> Key Word</title>

   <para>
    In the SQL standard, the optional key word <literal>AS</> is just
    noise and can be omitted without affecting the meaning.  The
    <productname>PostgreSQL</productname> parser requires this key
    word when renaming output columns because the type extensibility
    features lead to parsing ambiguities without it.
    <literal>AS</literal> is optional in <literal>FROM</literal>
    items, however.
   </para>
  </refsect2>

  <refsect2>
   <title>Namespace Available to <literal>GROUP BY</literal> and <literal>ORDER BY</literal></title>

   <para>
    In the SQL-92 standard, an <literal>ORDER BY</literal> clause can
    only use result column names or numbers, while a <literal>GROUP
    BY</literal> clause can only use expressions based on input column
    names.  <productname>PostgreSQL</productname> extends each of
    these clauses to allow the other choice as well (but it uses the
    standard's interpretation if there is ambiguity).
    <productname>PostgreSQL</productname> also allows both clauses to
    specify arbitrary expressions.  Note that names appearing in an
    expression will always be taken as input-column names, not as
    result-column names.
   </para>

   <para>
    SQL:1999 and later use a slightly different definition which is not
    entirely upward compatible with SQL-92.  
    In most cases, however, <productname>PostgreSQL</productname>
    will interpret an <literal>ORDER BY</literal> or <literal>GROUP
    BY</literal> expression the same way SQL:1999 does.
   </para>
  </refsect2>

  <refsect2>
   <title>Nonstandard Clauses</title>

   <para>
    The clauses <literal>DISTINCT ON</literal>,
    <literal>LIMIT</literal>, and <literal>OFFSET</literal> are not
    defined in the SQL standard.
   </para>
  </refsect2>
 </refsect1>
</refentry>