Support all SQL:2011 options for window frame clauses

Enterprise / PostgreSQL - Tom Lane [sss.pgh.pa.us] - 7 February 2018 05:06 EST

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

0a459cec96 Support all SQL:2011 options for window frame clauses.
doc/src/sgml/btree.sgml | 181 ++-
doc/src/sgml/func.sgml | 5 +-
doc/src/sgml/ref/select.sgml | 123 +-
doc/src/sgml/syntax.sgml | 144 ++-
doc/src/sgml/xindex.sgml | 60 +-
src/backend/access/nbtree/nbtvalidate.c | 32 +-
src/backend/catalog/dependency.c | 16 +
src/backend/catalog/sql_features.txt | 2 +-
src/backend/commands/opclasscmds.c | 29 +-
src/backend/executor/nodeWindowAgg.c | 1056 +++++++++++++----
src/backend/nodes/copyfuncs.c | 10 +
src/backend/nodes/equalfuncs.c | 5 +
src/backend/nodes/outfuncs.c | 10 +
src/backend/nodes/readfuncs.c | 10 +
src/backend/optimizer/plan/createplan.c | 14 +
src/backend/parser/gram.y | 71 +-
src/backend/parser/parse_agg.c | 8 +
src/backend/parser/parse_clause.c | 156 ++-
src/backend/parser/parse_expr.c | 3 +
src/backend/parser/parse_func.c | 1 +
src/backend/utils/adt/date.c | 107 ++
src/backend/utils/adt/int.c | 152 +++
src/backend/utils/adt/int8.c | 42 +-
src/backend/utils/adt/ruleutils.c | 20 +-
src/backend/utils/adt/timestamp.c | 104 ++
src/backend/utils/errcodes.txt | 1 +
src/include/access/nbtree.h | 8 +-
src/include/catalog/catversion.h | 2 +-
src/include/catalog/pg_amproc.h | 13 +
src/include/catalog/pg_proc.h | 28 +
src/include/nodes/execnodes.h | 25 +-
src/include/nodes/parsenodes.h | 48 +-
src/include/nodes/plannodes.h | 6 +
src/include/parser/kwlist.h | 3 +
src/include/parser/parse_node.h | 3 +-
src/test/regress/expected/alter_generic.out | 4 +-
src/test/regress/expected/window.out | 1697 ++++++++++++++++++++++++++-
src/test/regress/sql/window.sql | 496 +++++++-
38 files changed, 4300 insertions(+), 395 deletions(-)

Upstream: git.postgresql.org


  • Share