Support all SQL:2011 options for window frame clauses.

This patch adds the ability to use "RANGE offset PRECEDING/FOLLOWING"
frame boundaries in window functions.  We'd punted on that back in the
original patch to add window functions, because it was not clear how to
do it in a reasonably data-type-extensible fashion.  That problem is
resolved here by adding the ability for btree operator classes to provide
an "in_range" support function that defines how to add or subtract the
RANGE offset value.  Factoring it this way also allows the operator class
to avoid overflow problems near the ends of the datatype's range, if it
wishes to expend effort on that.  (In the committed patch, the integer
opclasses handle that issue, but it did not seem worth the trouble to
avoid overflow failures for datetime types.)

The patch includes in_range support for the integer_ops opfamily
(int2/int4/int8) as well as the standard datetime types.  Support for
other numeric types has been requested, but that seems like suitable
material for a follow-on patch.

In addition, the patch adds GROUPS mode which counts the offset in
ORDER-BY peer groups rather than rows, and it adds the frame_exclusion
options specified by SQL:2011.  As far as I can see, we are now fully
up to spec on window framing options.

Existing behaviors remain unchanged, except that I changed the errcode
for a couple of existing error reports to meet the SQL spec's expectation
that negative "offset" values should be reported as SQLSTATE 22013.

Internally and in relevant parts of the documentation, we now consistently
use the terminology "offset PRECEDING/FOLLOWING" rather than "value
PRECEDING/FOLLOWING", since the term "value" is confusingly vague.

Oliver Ford, reviewed and whacked around some by me

Discussion: https://postgr.es/m/CAGMVOdu9sivPAxbNN0X+q19Sfv9edEPv=HibOJhB14TJv_RCQg@mail.gmail.com

1 files changed, 81 insertions, 42 deletions

diff --git a/doc/src/sgml/ref/select.sgml b/doc/src/sgml/ref/select.sgml
index 8a3e86b6db6..b5d3d3a071e 100644
--- a/doc/src/sgml/ref/select.sgml
+++ b/doc/src/sgml/ref/select.sgml
@@ -859,28 +859,39 @@ WINDOW <replaceable class="parameter">window_name</replaceable> AS ( <replaceabl
     The <replaceable class="parameter">frame_clause</replaceable> can be one of
 
 <synopsis>
-{ RANGE | ROWS } <replaceable>frame_start</replaceable>
-{ RANGE | ROWS } BETWEEN <replaceable>frame_start</replaceable> AND <replaceable>frame_end</replaceable>
+{ RANGE | ROWS | GROUPS } <replaceable>frame_start</replaceable> [ <replaceable>frame_exclusion</replaceable> ]
+{ RANGE | ROWS | GROUPS } BETWEEN <replaceable>frame_start</replaceable> AND <replaceable>frame_end</replaceable> [ <replaceable>frame_exclusion</replaceable> ]
 </synopsis>
 
-    where <replaceable>frame_start</replaceable> and <replaceable>frame_end</replaceable> can be
-    one of
+    where <replaceable>frame_start</replaceable>
+    and <replaceable>frame_end</replaceable> can be one of
 
 <synopsis>
 UNBOUNDED PRECEDING
-<replaceable>value</replaceable> PRECEDING
+<replaceable>offset</replaceable> PRECEDING
 CURRENT ROW
-<replaceable>value</replaceable> FOLLOWING
+<replaceable>offset</replaceable> FOLLOWING
 UNBOUNDED FOLLOWING
 </synopsis>
 
+    and <replaceable>frame_exclusion</replaceable> can be one of
+
+<synopsis>
+EXCLUDE CURRENT ROW
+EXCLUDE GROUP
+EXCLUDE TIES
+EXCLUDE NO OTHERS
+</synopsis>
+
     If <replaceable>frame_end</replaceable> is omitted it defaults to <literal>CURRENT
     ROW</literal>.  Restrictions are that
     <replaceable>frame_start</replaceable> cannot be <literal>UNBOUNDED FOLLOWING</literal>,
     <replaceable>frame_end</replaceable> cannot be <literal>UNBOUNDED PRECEDING</literal>,
     and the <replaceable>frame_end</replaceable> choice cannot appear earlier in the
-    above list than the <replaceable>frame_start</replaceable> choice &mdash; for example
-    <literal>RANGE BETWEEN CURRENT ROW AND <replaceable>value</replaceable>
+    above list of <replaceable>frame_start</replaceable>
+    and <replaceable>frame_end</replaceable> options than
+    the <replaceable>frame_start</replaceable> choice does &mdash; for example
+    <literal>RANGE BETWEEN CURRENT ROW AND <replaceable>offset</replaceable>
     PRECEDING</literal> is not allowed.
    </para>
 
@@ -888,33 +899,72 @@ UNBOUNDED FOLLOWING
     The default framing option is <literal>RANGE UNBOUNDED PRECEDING</literal>,
     which is the same as <literal>RANGE BETWEEN UNBOUNDED PRECEDING AND
     CURRENT ROW</literal>; it sets the frame to be all rows from the partition start
-    up through the current row's last peer (a row that <literal>ORDER
-    BY</literal> considers equivalent to the current row, or all rows if there
-    is no <literal>ORDER BY</literal>).
+    up through the current row's last <firstterm>peer</firstterm> (a row
+    that the window's <literal>ORDER BY</literal> clause considers
+    equivalent to the current row), or all rows if there
+    is no <literal>ORDER BY</literal>.
     In general, <literal>UNBOUNDED PRECEDING</literal> means that the frame
     starts with the first row of the partition, and similarly
     <literal>UNBOUNDED FOLLOWING</literal> means that the frame ends with the last
-    row of the partition (regardless of <literal>RANGE</literal> or <literal>ROWS</literal>
-    mode).  In <literal>ROWS</literal> mode, <literal>CURRENT ROW</literal>
-    means that the frame starts or ends with the current row; but in
-    <literal>RANGE</literal> mode it means that the frame starts or ends with
-    the current row's first or last peer in the <literal>ORDER BY</literal> ordering.
-    The <replaceable>value</replaceable> <literal>PRECEDING</literal> and
-    <replaceable>value</replaceable> <literal>FOLLOWING</literal> cases are currently only
-    allowed in <literal>ROWS</literal> 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</literal> options can produce unpredictable
+    row of the partition, regardless
+    of <literal>RANGE</literal>, <literal>ROWS</literal>
+    or <literal>GROUPS</literal> mode.
+    In <literal>ROWS</literal> mode, <literal>CURRENT ROW</literal> means
+    that the frame starts or ends with the current row; but
+    in <literal>RANGE</literal> or <literal>GROUPS</literal> mode it means
+    that the frame starts or ends with the current row's first or last peer
+    in the <literal>ORDER BY</literal> ordering.
+    The <replaceable>offset</replaceable> <literal>PRECEDING</literal> and
+    <replaceable>offset</replaceable> <literal>FOLLOWING</literal> options
+    vary in meaning depending on the frame mode.
+    In <literal>ROWS</literal> mode, the <replaceable>offset</replaceable>
+    is an integer indicating that the frame starts or ends that many rows
+    before or after the current row.
+    In <literal>GROUPS</literal> mode, the <replaceable>offset</replaceable>
+    is an integer indicating that the frame starts or ends that many peer
+    groups before or after the current row's peer group, where
+    a <firstterm>peer group</firstterm> is a group of rows that are
+    equivalent according to <literal>ORDER BY</literal>.
+    In <literal>RANGE</literal> mode, use of
+    an <replaceable>offset</replaceable> option requires that there be
+    exactly one <literal>ORDER BY</literal> column in the window definition.
+    Then the frame contains those rows whose ordering column value is no
+    more than <replaceable>offset</replaceable> less than
+    (for <literal>PRECEDING</literal>) or more than
+    (for <literal>FOLLOWING</literal>) the current row's ordering column
+    value.  In these cases the data type of
+    the <replaceable>offset</replaceable> expression depends on the data
+    type of the ordering column.  For numeric ordering columns it is
+    typically of the same type as the ordering column, but for datetime
+    ordering columns it is an <type>interval</type>.
+    In all these cases, the value of the <replaceable>offset</replaceable>
+    must be non-null and non-negative.  Also, while
+    the <replaceable>offset</replaceable> does not have to be a simple
+    constant, it cannot contain variables, aggregate functions, or window
+    functions.
+   </para>
+
+   <para>
+    The <replaceable>frame_exclusion</replaceable> option allows rows around
+    the current row to be excluded from the frame, even if they would be
+    included according to the frame start and frame end options.
+    <literal>EXCLUDE CURRENT ROW</literal> excludes the current row from the
+    frame.
+    <literal>EXCLUDE GROUP</literal> excludes the current row and its
+    ordering peers from the frame.
+    <literal>EXCLUDE TIES</literal> excludes any peers of the current
+    row from the frame, but not the current row itself.
+    <literal>EXCLUDE NO OTHERS</literal> simply specifies explicitly the
+    default behavior of not excluding the current row or its peers.
+   </para>
+
+   <para>
+    Beware that the <literal>ROWS</literal> mode can produce unpredictable
     results if the <literal>ORDER BY</literal> ordering does not order the rows
-    uniquely.  The <literal>RANGE</literal> options are designed to ensure that
-    rows that are peers in the <literal>ORDER BY</literal> ordering are treated
-    alike; all peer rows will be in the same frame.
+    uniquely.  The <literal>RANGE</literal> and <literal>GROUPS</literal>
+    modes are designed to ensure that rows that are peers in
+    the <literal>ORDER BY</literal> ordering are treated alike: all rows of
+    a given peer group will be in the frame or excluded from it.
    </para>
 
    <para>
@@ -1982,17 +2032,6 @@ SELECT distributors.* WHERE distributors.name = 'Westward';
   </refsect2>
 
   <refsect2>
-   <title><literal>WINDOW</literal> Clause Restrictions</title>
-
-   <para>
-    The SQL standard provides additional options for the window
-    <replaceable class="parameter">frame_clause</replaceable>.
-    <productname>PostgreSQL</productname> currently supports only the
-    options listed above.
-   </para>
-  </refsect2>
-
-  <refsect2>
    <title><literal>LIMIT</literal> and <literal>OFFSET</literal></title>
 
    <para>