select.7

来自「PostgreSQL 8.2中增加了很多企业用户所需要的功能和性能上的提高,其开」· 7 代码 · 共 886 行 · 第 1/2 页

.sp
.nf
ORDER BY \fIexpression\fR [ ASC | DESC | USING \fIoperator\fR ] [, ...]
.sp
.fi
\fIexpression\fR can be the
name or ordinal number of an output column
(\fBSELECT\fR list item), or it can be an arbitrary
expression formed from input-column values.
.PP
The ORDER BY clause causes the result rows to
be sorted according to the specified expressions. If two rows are
equal according to the leftmost expression, the are compared
according to the next expression and so on. If they are equal
according to all specified expressions, they are returned in
an implementation-dependent order.
.PP
The ordinal number refers to the ordinal (left-to-right) position
of the result column. This feature makes it possible to define an
ordering on the basis of a column that does not have a unique
name. This is never absolutely necessary because it is always
possible to assign a name to a result column using the
AS clause.
.PP
It is also possible to use arbitrary expressions in the
ORDER BY clause, including columns that do not
appear in the \fBSELECT\fR result list. Thus the
following statement is valid:
.sp
.nf
SELECT name FROM distributors ORDER BY code;
.sp
.fi
A limitation of this feature is that an ORDER BY
clause applying to the result of a UNION,
INTERSECT, or EXCEPT clause may only
specify an output column name or number, not an expression.
.PP
If an ORDER BY expression is a simple name that
matches both a result column name and an input column name,
ORDER BY will interpret it as the result column name.
This is the opposite of the choice that GROUP BY will
make in the same situation. This inconsistency is made to be
compatible with the SQL standard.
.PP
Optionally one may add the key word ASC (ascending) or
DESC (descending) after any expression in the
ORDER BY clause. If not specified, ASC is
assumed by default. Alternatively, a specific ordering operator
name may be specified in the USING clause.
ASC is usually equivalent to USING < and
DESC is usually equivalent to USING >.
(But the creator of a user-defined data type can define exactly what the
default sort ordering is, and it might correspond to operators with other
names.)
.PP
The null value sorts higher than any other value. In other words,
with ascending sort order, null values sort at the end, and with
descending sort order, null values sort at the beginning.
.PP
Character-string data is sorted according to the locale-specific
collation order that was established when the database cluster
was initialized.
.SS "DISTINCT CLAUSE"
.PP
If DISTINCT is specified, all duplicate rows are
removed from the result set (one row is kept from each group of
duplicates). ALL specifies the opposite: all rows are
kept; that is the default.
.PP
DISTINCT ON ( \fIexpression\fR [, ...] )
keeps only the first row of each set of rows where the given
expressions evaluate to equal. The DISTINCT ON
expressions are interpreted using the same rules as for
ORDER BY (see above). Note that the ``first
row'' of each set is unpredictable unless ORDER
BY is used to ensure that the desired row appears first. For
example,
.sp
.nf
SELECT DISTINCT ON (location) location, time, report
    FROM weather_reports
    ORDER BY location, time DESC;
.sp
.fi
retrieves the most recent weather report for each location. But
if we had not used ORDER BY to force descending order
of time values for each location, we'd have gotten a report from
an unpredictable time for each location.
.PP
The DISTINCT ON expression(s) must match the leftmost
ORDER BY expression(s). The ORDER BY clause
will normally contain additional expression(s) that determine the
desired precedence of rows within each DISTINCT ON group.
.SS "LIMIT CLAUSE"
.PP
The LIMIT clause consists of two independent
sub-clauses:
.sp
.nf
LIMIT { \fIcount\fR | ALL }
OFFSET \fIstart\fR
.sp
.fi
\fIcount\fR specifies the
maximum number of rows to return, while \fIstart\fR specifies the number of rows
to skip before starting to return rows. When both are specified,
\fIstart\fR rows are skipped
before starting to count the \fIcount\fR rows to be returned.
.PP
When using LIMIT, it is a good idea to use an
ORDER BY clause that constrains the result rows into a
unique order. Otherwise you will get an unpredictable subset of
the query's rows \(em 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 ORDER BY.
.PP
The query planner takes 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 LIMIT and OFFSET. Thus, using
different LIMIT/OFFSET values to select
different subsets of a query result \fBwill give
inconsistent results\fR unless you enforce a predictable
result ordering with 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
ORDER BY is used to constrain the order.
.SS "FOR UPDATE/FOR SHARE CLAUSE"
.PP
The FOR UPDATE clause has this form:
.sp
.nf
FOR UPDATE [ OF \fItable_name\fR [, ...] ] [ NOWAIT ]
.sp
.fi
.PP
The closely related FOR SHARE clause has this form:
.sp
.nf
FOR SHARE [ OF \fItable_name\fR [, ...] ] [ NOWAIT ]
.sp
.fi
.PP
FOR UPDATE causes the rows retrieved by the
\fBSELECT\fR 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 \fBUPDATE\fR,
\fBDELETE\fR, or \fBSELECT FOR UPDATE\fR
of these rows will be blocked until the current transaction ends.
Also, if an \fBUPDATE\fR, \fBDELETE\fR,
or \fBSELECT FOR UPDATE\fR from another transaction
has already locked a selected row or rows, \fBSELECT FOR
UPDATE\fR 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 in the documentation.
.PP
To prevent the operation from waiting for other transactions to commit,
use the NOWAIT option. \fBSELECT FOR UPDATE
NOWAIT\fR reports an error, rather than waiting, if a selected row
cannot be locked immediately. Note that NOWAIT applies only
to the row-level lock(s) \(em the required ROW SHARE
table-level lock is still taken in the ordinary way (see
in the documentation). You can use the NOWAIT option of
LOCK [\fBlock\fR(7)]
if you need to acquire the table-level lock without waiting.
.PP
FOR SHARE behaves similarly, except that it
acquires a shared rather than exclusive lock on each retrieved
row. A shared lock blocks other transactions from performing
\fBUPDATE\fR, \fBDELETE\fR, or \fBSELECT
FOR UPDATE\fR on these rows, but it does not prevent them
from performing \fBSELECT FOR SHARE\fR.
.PP
If specific tables are named in FOR UPDATE
or FOR SHARE,
then only rows coming from those tables are locked; any other
tables used in the \fBSELECT\fR are simply read as
usual. A FOR UPDATE or FOR SHARE
clause without a table list affects all tables used in the command.
If FOR UPDATE or FOR SHARE is
applied to a view or sub-query, it affects all tables used in
the view or sub-query.
.PP
Multiple FOR UPDATE and FOR SHARE
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 FOR UPDATE and
FOR SHARE clauses, then it is processed as
FOR UPDATE. Similarly, a table is processed
as NOWAIT if that is specified in any of the clauses
affecting it.
.PP
FOR UPDATE and FOR SHARE 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.
.sp
.RS
.B "Caution:"
Avoid locking a row and then modifying it within a later savepoint or
\fBPL/pgSQL\fR exception block. A subsequent
rollback would cause the lock to be lost. For example,
.sp
.nf
BEGIN;
SELECT * FROM mytable WHERE key = 1 FOR UPDATE;
SAVEPOINT s;
UPDATE mytable SET ... WHERE key = 1;
ROLLBACK TO s;
.sp
.fi
After the \fBROLLBACK\fR, 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 PostgreSQL.
.RE
.sp
.sp
.RS
.B "Caution:"
It is possible for a \fBSELECT\fR command using both
LIMIT and FOR UPDATE/SHARE
clauses to return fewer rows than specified by LIMIT.
This is because 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 SELECT unblocks, the row might have been deleted
or updated so that it does not meet the query WHERE condition
anymore, in which case it will not be returned.
.RE
.sp
.SH "EXAMPLES"
.PP
To join the table films with the table
distributors:
.sp
.nf
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
 ...
.sp
.fi
.PP
To sum the column len of all films and group
the results by kind:
.sp
.nf
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
.sp
.fi
.PP
To sum the column len of all films, group
the results by kind and show those group totals
that are less than 5 hours:
.sp
.nf
SELECT kind, sum(len) AS total
    FROM films
    GROUP BY kind
    HAVING sum(len) < interval '5 hours';

   kind   | total
----------+-------
 Comedy   | 02:58
 Romantic | 04:38
.sp
.fi
.PP
The following two examples are identical ways of sorting the individual
results according to the contents of the second column
(name):
.sp
.nf
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
.sp
.fi
.PP
The next example shows how to obtain the union of the tables
distributors and
actors, restricting the results to those that begin
with the letter W in each table. Only distinct rows are wanted, so the
key word ALL is omitted.
.sp
.nf
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
.sp
.fi
.PP
This example shows how to use a function in the FROM
clause, both with and without a column definition list:
.sp
.nf
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
.sp
.fi
.SH "COMPATIBILITY"
.PP
Of course, the \fBSELECT\fR statement is compatible
with the SQL standard. But there are some extensions and some
missing features.
.SS "OMITTED FROM CLAUSES"
.PP
PostgreSQL allows one to omit the
FROM clause. It has a straightforward use to
compute the results of simple expressions:
.sp
.nf
SELECT 2+2;

 ?column?
----------
        4
.sp
.fi
Some other SQL databases cannot do this except
by introducing a dummy one-row table from which to do the
\fBSELECT\fR.
.PP
Note that if a FROM clause is not specified,
the query cannot reference any database tables. For example, the
following query is invalid:
.sp
.nf
SELECT distributors.* WHERE distributors.name = 'Westward';
.sp
.fi
PostgreSQL releases prior to
8.1 would accept queries of this form, and add an implicit entry
to the query's FROM 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 add_missing_from configuration variable can be
enabled.
.SS "THE AS KEY WORD"
.PP
In the SQL standard, the optional key word AS is just
noise and can be omitted without affecting the meaning. The
PostgreSQL parser requires this key
word when renaming output columns because the type extensibility
features lead to parsing ambiguities without it.
AS is optional in FROM
items, however.
.SS "NAMESPACE AVAILABLE TO GROUP BY AND ORDER BY"
.PP
In the SQL-92 standard, an ORDER BY clause may
only use result column names or numbers, while a GROUP
BY clause may only use expressions based on input column
names. PostgreSQL extends each of
these clauses to allow the other choice as well (but it uses the
standard's interpretation if there is ambiguity).
PostgreSQL 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.
.PP
SQL:1999 and later use a slightly different definition which is not
entirely upward compatible with SQL-92. 
In most cases, however, PostgreSQL
will interpret an ORDER BY or GROUP
BY expression the same way SQL:1999 does.
.SS "NONSTANDARD CLAUSES"
.PP
The clauses DISTINCT ON,
LIMIT, and OFFSET are not
defined in the SQL standard.