Extend the set of frame options supported for window functions.

This patch allows the frame to start from CURRENT ROW (in either RANGE or
ROWS mode), and it also adds support for ROWS n PRECEDING and ROWS n FOLLOWING
start and end points.  (RANGE value PRECEDING/FOLLOWING isn't there yet ---
the grammar works, but that's all.)

Hitoshi Harada, reviewed by Pavel Stehule

3 files changed, 112 insertions, 48 deletions

diff --git a/doc/src/sgml/func.sgml b/doc/src/sgml/func.sgml
index fed003c4d01..a4ea1462a8b 100644
--- a/doc/src/sgml/func.sgml
+++ b/doc/src/sgml/func.sgml
@@ -1,4 +1,4 @@
-<!-- $PostgreSQL: pgsql/doc/src/sgml/func.sgml,v 1.501 2010/02/07 20:48:09 tgl Exp $ -->
+<!-- $PostgreSQL: pgsql/doc/src/sgml/func.sgml,v 1.502 2010/02/12 17:33:19 tgl Exp $ -->
 
  <chapter id="functions">
   <title>Functions and Operators</title>
@@ -10559,21 +10559,23 @@ SELECT xmlagg(x) FROM (SELECT x FROM test ORDER BY y DESC) AS tab;
    <function>nth_value</> consider only the rows within the <quote>window
    frame</>, which by default contains the rows from the start of the
    partition through the last peer of the current row.  This is
-   likely to give unhelpful results for <function>nth_value</> and
-   particularly <function>last_value</>.  You can redefine the frame as
-   being the whole partition by adding <literal>ROWS BETWEEN UNBOUNDED
-   PRECEDING AND UNBOUNDED FOLLOWING</> to the <literal>OVER</> clause.
-   See <xref linkend="syntax-window-functions"> for more information.
+   likely to give unhelpful results for <function>last_value</> and
+   sometimes also <function>nth_value</>.  You can redefine the frame by
+   adding a suitable frame specification (<literal>RANGE</> or
+   <literal>ROWS</>) to the <literal>OVER</> clause.
+   See <xref linkend="syntax-window-functions"> for more information
+   about frame specifications.
   </para>
 
   <para>
    When an aggregate function is used as a window function, it aggregates
-   over the rows within the current row's window frame.  To obtain
-   aggregation over the whole partition, omit <literal>ORDER BY</> or use
-   <literal>ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING</>.
+   over the rows within the current row's window frame.
    An aggregate used with <literal>ORDER BY</> and the default window frame
    definition produces a <quote>running sum</> type of behavior, which may or
-   may not be what's wanted.
+   may not be what's wanted.  To obtain
+   aggregation over the whole partition, omit <literal>ORDER BY</> or use
+   <literal>ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING</>.
+   Other frame specifications can be used to obtain other effects.
   </para>
 
   <note>
diff --git a/doc/src/sgml/ref/select.sgml b/doc/src/sgml/ref/select.sgml
index 804a697e496..395ca79604c 100644
--- a/doc/src/sgml/ref/select.sgml
+++ b/doc/src/sgml/ref/select.sgml
@@ -1,5 +1,5 @@
 <!--
-$PostgreSQL: pgsql/doc/src/sgml/ref/select.sgml,v 1.128 2009/10/28 14:55:37 tgl Exp $
+$PostgreSQL: pgsql/doc/src/sgml/ref/select.sgml,v 1.129 2010/02/12 17:33:19 tgl Exp $
 PostgreSQL documentation
 -->
 
@@ -616,27 +616,66 @@ WINDOW <replaceable class="parameter">window_name</replaceable> AS ( <replaceabl
    <para>
     The optional <replaceable class="parameter">frame_clause</> defines
     the <firstterm>window frame</> for window functions that depend on the
-    frame (not all do).  It can be one of
+    frame (not all do).  The window frame is a set of related rows for
+    each row of the query (called the <firstterm>current row</>).
+    The <replaceable class="parameter">frame_clause</> can be one of
+
+<synopsis>
+[ RANGE | ROWS ] <replaceable>frame_start</>
+[ RANGE | ROWS ] BETWEEN <replaceable>frame_start</> AND <replaceable>frame_end</>
+</synopsis>
+
+    where <replaceable>frame_start</> and <replaceable>frame_end</> can be
+    one of
+
 <synopsis>
-RANGE UNBOUNDED PRECEDING
-RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
-RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
-ROWS UNBOUNDED PRECEDING
-ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
-ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
+UNBOUNDED PRECEDING
+<replaceable>value</replaceable> PRECEDING
+CURRENT ROW
+<replaceable>value</replaceable> FOLLOWING
+UNBOUNDED FOLLOWING
 </synopsis>
-    The first two are equivalent and are also the default: they set the
-    frame to be all rows from the partition start up through the current row's
-    last peer in the <literal>ORDER BY</> ordering (which means all rows if
-    there is no <literal>ORDER BY</>).  The options
-    <literal>RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING</> and
-    <literal>ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING</>
-    are also equivalent: they always select all rows in the partition.
-    Lastly, <literal>ROWS UNBOUNDED PRECEDING</> or its verbose equivalent
-    <literal>ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW</> select
-    all rows up through the current row (regardless of duplicates).
-    Beware that this option can produce implementation-dependent results
-    if the <literal>ORDER BY</> ordering does not order the rows uniquely.
+
+    If <replaceable>frame_end</> is omitted it defaults to <literal>CURRENT
+    ROW</>.  Restrictions are that
+    <replaceable>frame_start</> cannot be <literal>UNBOUNDED FOLLOWING</>,
+    <replaceable>frame_end</> cannot be <literal>UNBOUNDED PRECEDING</>,
+    and the <replaceable>frame_end</> choice cannot appear earlier in the
+    above list than the <replaceable>frame_start</> choice &mdash; for example
+    <literal>RANGE BETWEEN CURRENT ROW AND <replaceable>value</>
+    PRECEDING</literal> is not allowed.
+   </para>
+
+   <para>
+    The default framing option is <literal>RANGE UNBOUNDED PRECEDING</>,
+    which is the same as <literal>RANGE BETWEEN UNBOUNDED PRECEDING AND
+    CURRENT ROW</>; it sets the frame to be all rows from the partition start
+    up through the current row's last peer in the <literal>ORDER BY</>
+    ordering (which means all rows if there is no <literal>ORDER BY</>).
+    In general, <literal>UNBOUNDED PRECEDING</> means that the frame
+    starts with the first row of the partition, and similarly
+    <literal>UNBOUNDED FOLLOWING</> means that the frame ends with the last
+    row of the partition (regardless of <literal>RANGE</> or <literal>ROWS</>
+    mode).  In <literal>ROWS</> mode, <literal>CURRENT ROW</>
+    means that the frame starts or ends with the current row; but in
+    <literal>RANGE</> mode it means that the frame starts or ends with
+    the current row's first or last peer in the <literal>ORDER BY</> ordering.
+    The <replaceable>value</> <literal>PRECEDING</> and
+    <replaceable>value</> <literal>FOLLOWING</> cases are currently only
+    allowed in <literal>ROWS</> mode.  They indicate that the frame starts
+    or ends with the row that many rows before or after the current row.
+    <replaceable>value</replaceable> must be an integer expression not
+    containing any variables, aggregate functions, or window functions.
+    The value must not be null or negative; but it can be zero, which
+    selects the current row itself.
+   </para>
+
+   <para>
+    Beware that the <literal>ROWS</> options can produce unpredictable
+    results if the <literal>ORDER BY</> ordering does not order the rows
+    uniquely.  The <literal>RANGE</> options are designed to ensure that
+    rows that are peers in the <literal>ORDER BY</> ordering are treated
+    alike; any two peer rows will be both in or both not in the frame.
    </para>
 
    <para>
diff --git a/doc/src/sgml/syntax.sgml b/doc/src/sgml/syntax.sgml
index 64aec4f6843..83ecb8653c6 100644
--- a/doc/src/sgml/syntax.sgml
+++ b/doc/src/sgml/syntax.sgml
@@ -1,4 +1,4 @@
-<!-- $PostgreSQL: pgsql/doc/src/sgml/syntax.sgml,v 1.141 2010/02/04 00:19:28 tgl Exp $ -->
+<!-- $PostgreSQL: pgsql/doc/src/sgml/syntax.sgml,v 1.142 2010/02/12 17:33:19 tgl Exp $ -->
 
 <chapter id="sql-syntax">
  <title>SQL Syntax</title>
@@ -1667,14 +1667,21 @@ SELECT array_agg(a ORDER BY b DESC) FROM table;
     and the optional <replaceable class="parameter">frame_clause</replaceable>
     can be one of
 <synopsis>
-RANGE UNBOUNDED PRECEDING
-RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
-RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
-ROWS UNBOUNDED PRECEDING
-ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
-ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
+[ RANGE | ROWS ] <replaceable>frame_start</>
+[ RANGE | ROWS ] BETWEEN <replaceable>frame_start</> AND <replaceable>frame_end</>
 </synopsis>
+    where <replaceable>frame_start</> and <replaceable>frame_end</> can be
+    one of
+<synopsis>
+UNBOUNDED PRECEDING
+<replaceable>value</replaceable> PRECEDING
+CURRENT ROW
+<replaceable>value</replaceable> FOLLOWING
+UNBOUNDED FOLLOWING
+</synopsis>
+   </para>
 
+   <para>
     Here, <replaceable>expression</replaceable> represents any value
     expression that does not itself contain window function calls.
     The <literal>PARTITION BY</> and <literal>ORDER BY</> lists have
@@ -1699,19 +1706,35 @@ ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
     The <replaceable class="parameter">frame_clause</replaceable> specifies
     the set of rows constituting the <firstterm>window frame</>, for those
     window functions that act on the frame instead of the whole partition.
+    If <replaceable>frame_end</> is omitted it defaults to <literal>CURRENT
+    ROW</>.  Restrictions are that
+    <replaceable>frame_start</> cannot be <literal>UNBOUNDED FOLLOWING</>,
+    <replaceable>frame_end</> cannot be <literal>UNBOUNDED PRECEDING</>,
+    and the <replaceable>frame_end</> choice cannot appear earlier in the
+    above list than the <replaceable>frame_start</> choice &mdash; for example
+    <literal>RANGE BETWEEN CURRENT ROW AND <replaceable>value</>
+    PRECEDING</literal> is not allowed.
     The default framing option is <literal>RANGE UNBOUNDED PRECEDING</>,
     which is the same as <literal>RANGE BETWEEN UNBOUNDED PRECEDING AND
-    CURRENT ROW</>; it selects rows up through the current row's last
-    peer in the <literal>ORDER BY</> ordering (which means all rows if
-    there is no <literal>ORDER BY</>).  The options
-    <literal>RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING</> and
-    <literal>ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING</>
-    are also equivalent: they always select all rows in the partition.
-    Lastly, <literal>ROWS UNBOUNDED PRECEDING</> or its verbose equivalent
-    <literal>ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW</> select
-    all rows up through the current row (regardless of duplicates).
-    Beware that this option can produce implementation-dependent results
-    if the <literal>ORDER BY</> ordering does not order the rows uniquely.
+    CURRENT ROW</>; it sets the frame to be all rows from the partition start
+    up through the current row's last peer in the <literal>ORDER BY</>
+    ordering (which means all rows if there is no <literal>ORDER BY</>).
+    In general, <literal>UNBOUNDED PRECEDING</> means that the frame
+    starts with the first row of the partition, and similarly
+    <literal>UNBOUNDED FOLLOWING</> means that the frame ends with the last
+    row of the partition (regardless of <literal>RANGE</> or <literal>ROWS</>
+    mode).  In <literal>ROWS</> mode, <literal>CURRENT ROW</>
+    means that the frame starts or ends with the current row; but in
+    <literal>RANGE</> mode it means that the frame starts or ends with
+    the current row's first or last peer in the <literal>ORDER BY</> ordering.
+    The <replaceable>value</> <literal>PRECEDING</> and
+    <replaceable>value</> <literal>FOLLOWING</> cases are currently only
+    allowed in <literal>ROWS</> mode.  They indicate that the frame starts
+    or ends with the row that many rows before or after the current row.
+    <replaceable>value</replaceable> must be an integer expression not
+    containing any variables, aggregate functions, or window functions.
+    The value must not be null or negative; but it can be zero, which
+    selects the current row itself.
    </para>
 
    <para>