Adds RANGE BETWEEN with a start and end value, as well as an exclusions clause, to the window functions. This partially resolves TODO list item "Implement full support for window framing clauses".
== Specification == The window functions already allow a "ROWS BETWEEN start_value PRECEDING/FOLLOWING AND end_value PRECEDING/FOLLOWING" to restrict the number of rows within a partition that are piped into an aggregate function based on their position before or after the current row. This patch adds an equivalent for RANGE which restricts the rows based on whether the _values_ of the ORDER BY column for all other rows in the partition are within the start_value and end_value bounds. This brings PostgreSQL to parity with Oracle, and implements a SQL:2011 standard feature. SQL:2011 also defines a window frame exclusion clause, which excludes certain rows from the result. This clause doesn't seem to be implemented in any mainstream RDBMS (MariaDb mentions that fact in its documentation here: https://mariadb.com/kb/en/library/window-functions-overview/ and has it on its TODO list). This patch implements three EXCLUDE clauses described in the standard: EXCLUDE CURRENT ROW - excludes the current row from the result EXCLUDE TIES - excludes identical rows from the result EXCLUDE NO OTHERS - does nothing, is the default behavior; exists purely to describe the intention not to exclude any other rows The RANGE BETWEEN clause requires a single ORDER BY column which must be either an integer or a date/time type. If the column is a date/time type then start_value and end_value must both be an interval type. If the column is an integer, then the values must both be integers. == Testing == Tested on Windows with MinGW. All existing regression tests pass. New tests and updated documentation is included. Tests show both the new RANGE with values working and the exclusion clause working in both RANGE and ROWS mode. == Future Work == The standard also defines, in addition to RANGE and ROWS, a GROUPS option with a corresponding EXCLUDE GROUP option. This also doesn't seem to be implemented anywhere else, and I plan to implement it next. This patch also adds some new error messages which have not been internationalized.
0001-window-frame-v1.patch
Description: Binary data
