select.sgml

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

    <replaceable class="parameter">count</replaceable> specifies the
    maximum number of rows to return, while <replaceable
    class="parameter">start</replaceable> specifies the number of rows
    to skip before starting to return rows.  When both are specified,
    <replaceable class="parameter">start</replaceable> rows are skipped
    before starting to count the <replaceable
    class="parameter">count</replaceable> rows to be returned.
   </para>

   <para>
    When using <literal>LIMIT</>, it is a good idea to use an
    <literal>ORDER BY</> clause that constrains the result rows into a
    unique order.  Otherwise you will get an unpredictable subset of
    the query's rows &mdash; you may be asking for the tenth through
    twentieth rows, but tenth through twentieth in what ordering?  You
    don't know what ordering unless you specify <literal>ORDER BY</>.
   </para>

   <para>
    The query planner takes <literal>LIMIT</> into account when
    generating a query plan, so you are very likely to get different
    plans (yielding different row orders) depending on what you use
    for <literal>LIMIT</> and <literal>OFFSET</>.  Thus, using
    different <literal>LIMIT</>/<literal>OFFSET</> values to select
    different subsets of a query result <emphasis>will give
    inconsistent results</emphasis> unless you enforce a predictable
    result ordering with <literal>ORDER BY</>.  This is not a bug; it
    is an inherent consequence of the fact that SQL does not promise
    to deliver the results of a query in any particular order unless
    <literal>ORDER BY</> is used to constrain the order.
   </para>
  </refsect2>

  <refsect2 id="SQL-FOR-UPDATE-SHARE">
   <title id="sql-for-update-share-title"><literal>FOR UPDATE</literal>/<literal>FOR SHARE</literal> Clause</title>

   <para>
    The <literal>FOR UPDATE</literal> clause has this form:
<synopsis>
FOR UPDATE [ OF <replaceable class="parameter">table_name</replaceable> [, ...] ] [ NOWAIT ]
</synopsis>
   </para>

   <para>
    The closely related <literal>FOR SHARE</literal> clause has this form:
<synopsis>
FOR SHARE [ OF <replaceable class="parameter">table_name</replaceable> [, ...] ] [ NOWAIT ]
</synopsis>
   </para>

   <para>
    <literal>FOR UPDATE</literal> causes the rows retrieved by the
    <command>SELECT</command> statement to be locked as though for
    update.  This prevents them from being modified or deleted by
    other transactions until the current transaction ends.  That is,
    other transactions that attempt <command>UPDATE</command>,
    <command>DELETE</command>, or <command>SELECT FOR UPDATE</command>
    of these rows will be blocked until the current transaction ends.
    Also, if an <command>UPDATE</command>, <command>DELETE</command>,
    or <command>SELECT FOR UPDATE</command> from another transaction
    has already locked a selected row or rows, <command>SELECT FOR
    UPDATE</command> will wait for the other transaction to complete,
    and will then lock and return the updated row (or no row, if the
    row was deleted).  For further discussion see <xref
    linkend="mvcc">.
   </para>

   <para>
    To prevent the operation from waiting for other transactions to commit,
    use the <literal>NOWAIT</> option.  <command>SELECT FOR UPDATE
    NOWAIT</command> reports an error, rather than waiting, if a selected row
    cannot be locked immediately.  Note that <literal>NOWAIT</> applies only
    to the row-level lock(s) &mdash; the required <literal>ROW SHARE</literal>
    table-level lock is still taken in the ordinary way (see
    <xref linkend="mvcc">).  You can use the <literal>NOWAIT</> option of
    <xref linkend="sql-lock" endterm="sql-lock-title">
    if you need to acquire the table-level lock without waiting.
   </para>

   <para>
    <literal>FOR SHARE</literal> behaves similarly, except that it
    acquires a shared rather than exclusive lock on each retrieved
    row.  A shared lock blocks other transactions from performing
    <command>UPDATE</command>, <command>DELETE</command>, or <command>SELECT
    FOR UPDATE</command> on these rows, but it does not prevent them
    from performing <command>SELECT FOR SHARE</command>.
   </para>

   <para>
    It is currently not allowed for a single <command>SELECT</command>
    statement to include both <literal>FOR UPDATE</literal> and
    <literal>FOR SHARE</literal>, nor can different parts of the statement use
    both <literal>NOWAIT</> and normal waiting mode.
   </para>

   <para>
    If specific tables are named in <literal>FOR UPDATE</literal>
    or <literal>FOR SHARE</literal>,
    then only rows coming from those tables are locked; any other
    tables used in the <command>SELECT</command> are simply read as
    usual.
   </para>

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

   <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>
  </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 may
    only use result column names or numbers, while a <literal>GROUP
    BY</literal> clause may 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>

<!-- Keep this comment at the end of the file
Local variables:
mode: sgml
sgml-omittag:nil
sgml-shorttag:t
sgml-minimize-attributes:nil
sgml-always-quote-attributes:t
sgml-indent-step:1
sgml-indent-data:t
sgml-parent-document:nil
sgml-default-dtd-file:"../reference.ced"
sgml-exposed-tags:nil
sgml-local-catalogs:"/usr/lib/sgml/catalog"
sgml-local-ecat-files:nil
End:
-->