aboutsummaryrefslogtreecommitdiff
path: root/doc/src
diff options
context:
space:
mode:
authorTom Lane <tgl@sss.pgh.pa.us>2013-11-05 21:58:08 -0500
committerTom Lane <tgl@sss.pgh.pa.us>2013-11-05 21:58:08 -0500
commit920c8261d58c10de7e68d99c8dd21a9650928d59 (patch)
tree12770466f0d74f42e559b0bdcb5c72965711e65c /doc/src
parentd4e6133c681f0038861e5cf8707302bd80917fa0 (diff)
downloadpostgresql-920c8261d58c10de7e68d99c8dd21a9650928d59.tar.gz
postgresql-920c8261d58c10de7e68d99c8dd21a9650928d59.zip
Improve the error message given for modifying a window with frame clause.
For rather inscrutable reasons, SQL:2008 disallows copying-and-modifying a window definition that has any explicit framing clause. The error message we gave for this only made sense if the referencing window definition itself contains an explicit framing clause, which it might well not. Moreover, in the context of an OVER clause it's not exactly obvious that "OVER (windowname)" implies copy-and-modify while "OVER windowname" does not. This has led to multiple complaints, eg bug #5199 from Iliya Krapchatov. Change to a hopefully more intelligible error message, and in the case where we have just "OVER (windowname)", add a HINT suggesting that omitting the parentheses will fix it. Also improve the related documentation. Back-patch to all supported branches.
Diffstat (limited to 'doc/src')
-rw-r--r--doc/src/sgml/syntax.sgml32
1 files changed, 19 insertions, 13 deletions
diff --git a/doc/src/sgml/syntax.sgml b/doc/src/sgml/syntax.sgml
index e3dbc4b5ea5..4fe872290a6 100644
--- a/doc/src/sgml/syntax.sgml
+++ b/doc/src/sgml/syntax.sgml
@@ -1730,10 +1730,10 @@ SELECT string_agg(a ORDER BY a, ',') FROM table; -- incorrect
The syntax of a window function call is one of the following:
<synopsis>
-<replaceable>function_name</replaceable> (<optional><replaceable>expression</replaceable> <optional>, <replaceable>expression</replaceable> ... </optional></optional>) [ FILTER ( WHERE <replaceable>filter_clause</replaceable> ) ] OVER ( <replaceable class="parameter">window_definition</replaceable> )
<replaceable>function_name</replaceable> (<optional><replaceable>expression</replaceable> <optional>, <replaceable>expression</replaceable> ... </optional></optional>) [ FILTER ( WHERE <replaceable>filter_clause</replaceable> ) ] OVER <replaceable>window_name</replaceable>
-<replaceable>function_name</replaceable> ( * ) [ FILTER ( WHERE <replaceable>filter_clause</replaceable> ) ] OVER ( <replaceable class="parameter">window_definition</replaceable> )
+<replaceable>function_name</replaceable> (<optional><replaceable>expression</replaceable> <optional>, <replaceable>expression</replaceable> ... </optional></optional>) [ FILTER ( WHERE <replaceable>filter_clause</replaceable> ) ] OVER ( <replaceable class="parameter">window_definition</replaceable> )
<replaceable>function_name</replaceable> ( * ) [ FILTER ( WHERE <replaceable>filter_clause</replaceable> ) ] OVER <replaceable>window_name</replaceable>
+<replaceable>function_name</replaceable> ( * ) [ FILTER ( WHERE <replaceable>filter_clause</replaceable> ) ] OVER ( <replaceable class="parameter">window_definition</replaceable> )
</synopsis>
where <replaceable class="parameter">window_definition</replaceable>
has the syntax
@@ -1768,15 +1768,14 @@ UNBOUNDED FOLLOWING
<para>
<replaceable>window_name</replaceable> is a reference to a named window
specification defined in the query's <literal>WINDOW</literal> clause.
- Named window specifications are usually referenced with just
- <literal>OVER</> <replaceable>window_name</replaceable>, but it is
- also possible to write a window name inside the parentheses and then
- optionally supply an ordering clause and/or frame clause (the referenced
- window must lack these clauses, if they are supplied here).
- This latter syntax follows the same rules as modifying an existing
- window name within the <literal>WINDOW</literal> clause; see the
- <xref linkend="sql-select"> reference
- page for details.
+ Alternatively, a full <replaceable>window_definition</replaceable> can
+ be given within parentheses, using the same syntax as for defining a
+ named window in the <literal>WINDOW</literal> clause; see the
+ <xref linkend="sql-select"> reference page for details. It's worth
+ pointing out that <literal>OVER wname</> is not exactly equivalent to
+ <literal>OVER (wname)</>; the latter implies copying and modifying the
+ window definition, and will be rejected if the referenced window
+ specification includes a frame clause.
</para>
<para>
@@ -1854,11 +1853,18 @@ UNBOUNDED FOLLOWING
</para>
<para>
+ If <literal>FILTER</literal> is specified, then only the input
+ rows for which the <replaceable>filter_clause</replaceable>
+ evaluates to true are fed to the window function; other rows
+ are discarded. Only aggregate window functions accept
+ a <literal>FILTER</literal> clause.
+ </para>
+
+ <para>
The built-in window functions are described in <xref
linkend="functions-window-table">. Other window functions can be added by
the user. Also, any built-in or user-defined aggregate function can be
- used as a window function. Only aggregate window functions accept
- a <literal>FILTER</literal> clause.
+ used as a window function.
</para>
<para>