forked frompostgres/postgres
- Notifications
You must be signed in to change notification settings - Fork6
Commit0a459ce
committed
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 theoriginal patch to add window functions, because it was not clear how todo it in a reasonably data-type-extensible fashion. That problem isresolved here by adding the ability for btree operator classes to providean "in_range" support function that defines how to add or subtract theRANGE offset value. Factoring it this way also allows the operator classto avoid overflow problems near the ends of the datatype's range, if itwishes to expend effort on that. (In the committed patch, the integeropclasses handle that issue, but it did not seem worth the trouble toavoid 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 forother numeric types has been requested, but that seems like suitablematerial for a follow-on patch.In addition, the patch adds GROUPS mode which counts the offset inORDER-BY peer groups rather than rows, and it adds the frame_exclusionoptions specified by SQL:2011. As far as I can see, we are now fullyup to spec on window framing options.Existing behaviors remain unchanged, except that I changed the errcodefor a couple of existing error reports to meet the SQL spec's expectationthat negative "offset" values should be reported as SQLSTATE 22013.Internally and in relevant parts of the documentation, we now consistentlyuse the terminology "offset PRECEDING/FOLLOWING" rather than "valuePRECEDING/FOLLOWING", since the term "value" is confusingly vague.Oliver Ford, reviewed and whacked around some by meDiscussion:https://postgr.es/m/CAGMVOdu9sivPAxbNN0X+q19Sfv9edEPv=HibOJhB14TJv_RCQg@mail.gmail.com1 parent2320945 commit0a459ce
File tree
38 files changed
+4300
-395
lines changed- doc/src/sgml
- ref
- src
- backend
- access/nbtree
- catalog
- commands
- executor
- nodes
- optimizer/plan
- parser
- utils
- adt
- include
- access
- catalog
- nodes
- parser
- test/regress
- expected
- sql
38 files changed
+4300
-395
lines changedLines changed: 180 additions & 1 deletion
Original file line number | Diff line number | Diff line change | |
---|---|---|---|
| |||
207 | 207 |
| |
208 | 208 |
| |
209 | 209 |
| |
210 |
| - | |
| 210 | + | |
211 | 211 |
| |
212 | 212 |
| |
213 | 213 |
| |
| |||
252 | 252 |
| |
253 | 253 |
| |
254 | 254 |
| |
| 255 | + | |
| 256 | + | |
| 257 | + | |
| 258 | + | |
| 259 | + | |
| 260 | + | |
| 261 | + | |
| 262 | + | |
| 263 | + | |
| 264 | + | |
| 265 | + | |
| 266 | + | |
| 267 | + | |
| 268 | + | |
| 269 | + | |
| 270 | + | |
| 271 | + | |
| 272 | + | |
| 273 | + | |
| 274 | + | |
| 275 | + | |
| 276 | + | |
| 277 | + | |
| 278 | + | |
| 279 | + | |
| 280 | + | |
| 281 | + | |
| 282 | + | |
| 283 | + | |
| 284 | + | |
| 285 | + | |
| 286 | + | |
| 287 | + | |
| 288 | + | |
| 289 | + | |
| 290 | + | |
| 291 | + | |
| 292 | + | |
| 293 | + | |
| 294 | + | |
| 295 | + | |
| 296 | + | |
| 297 | + | |
| 298 | + | |
| 299 | + | |
| 300 | + | |
| 301 | + | |
| 302 | + | |
| 303 | + | |
| 304 | + | |
| 305 | + | |
| 306 | + | |
| 307 | + | |
| 308 | + | |
| 309 | + | |
| 310 | + | |
| 311 | + | |
| 312 | + | |
| 313 | + | |
| 314 | + | |
| 315 | + | |
| 316 | + | |
| 317 | + | |
| 318 | + | |
| 319 | + | |
| 320 | + | |
| 321 | + | |
| 322 | + | |
| 323 | + | |
| 324 | + | |
| 325 | + | |
| 326 | + | |
| 327 | + | |
| 328 | + | |
| 329 | + | |
| 330 | + | |
| 331 | + | |
| 332 | + | |
| 333 | + | |
| 334 | + | |
| 335 | + | |
| 336 | + | |
| 337 | + | |
| 338 | + | |
| 339 | + | |
| 340 | + | |
| 341 | + | |
| 342 | + | |
| 343 | + | |
| 344 | + | |
| 345 | + | |
| 346 | + | |
| 347 | + | |
| 348 | + | |
| 349 | + | |
| 350 | + | |
| 351 | + | |
| 352 | + | |
| 353 | + | |
| 354 | + | |
| 355 | + | |
| 356 | + | |
| 357 | + | |
| 358 | + | |
| 359 | + | |
| 360 | + | |
| 361 | + | |
| 362 | + | |
| 363 | + | |
| 364 | + | |
| 365 | + | |
| 366 | + | |
| 367 | + | |
| 368 | + | |
| 369 | + | |
| 370 | + | |
| 371 | + | |
| 372 | + | |
| 373 | + | |
| 374 | + | |
| 375 | + | |
| 376 | + | |
| 377 | + | |
| 378 | + | |
| 379 | + | |
| 380 | + | |
| 381 | + | |
| 382 | + | |
| 383 | + | |
| 384 | + | |
| 385 | + | |
| 386 | + | |
| 387 | + | |
| 388 | + | |
| 389 | + | |
| 390 | + | |
| 391 | + | |
| 392 | + | |
| 393 | + | |
| 394 | + | |
| 395 | + | |
| 396 | + | |
| 397 | + | |
| 398 | + | |
| 399 | + | |
| 400 | + | |
| 401 | + | |
| 402 | + | |
| 403 | + | |
| 404 | + | |
| 405 | + | |
| 406 | + | |
| 407 | + | |
| 408 | + | |
| 409 | + | |
| 410 | + | |
| 411 | + | |
| 412 | + | |
| 413 | + | |
| 414 | + | |
| 415 | + | |
| 416 | + | |
| 417 | + | |
| 418 | + | |
| 419 | + | |
| 420 | + | |
| 421 | + | |
| 422 | + | |
| 423 | + | |
| 424 | + | |
| 425 | + | |
| 426 | + | |
| 427 | + | |
| 428 | + | |
| 429 | + | |
| 430 | + | |
| 431 | + | |
| 432 | + | |
| 433 | + | |
255 | 434 |
| |
256 | 435 |
| |
257 | 436 |
| |
|
Lines changed: 3 additions & 2 deletions
Original file line number | Diff line number | Diff line change | |
---|---|---|---|
| |||
14729 | 14729 |
| |
14730 | 14730 |
| |
14731 | 14731 |
| |
14732 |
| - | |
14733 |
| - | |
| 14732 | + | |
| 14733 | + | |
| 14734 | + | |
14734 | 14735 |
| |
14735 | 14736 |
| |
14736 | 14737 |
| |
|
Lines changed: 81 additions & 42 deletions
Original file line number | Diff line number | Diff line change | |
---|---|---|---|
| |||
859 | 859 |
| |
860 | 860 |
| |
861 | 861 |
| |
862 |
| - | |
863 |
| - | |
| 862 | + | |
| 863 | + | |
864 | 864 |
| |
865 | 865 |
| |
866 |
| - | |
867 |
| - | |
| 866 | + | |
| 867 | + | |
868 | 868 |
| |
869 | 869 |
| |
870 | 870 |
| |
871 |
| - | |
| 871 | + | |
872 | 872 |
| |
873 |
| - | |
| 873 | + | |
874 | 874 |
| |
| 875 | + | |
| 876 | + | |
| 877 | + | |
| 878 | + | |
| 879 | + | |
| 880 | + | |
| 881 | + | |
| 882 | + | |
| 883 | + | |
875 | 884 |
| |
876 | 885 |
| |
877 | 886 |
| |
878 | 887 |
| |
879 | 888 |
| |
880 | 889 |
| |
881 | 890 |
| |
882 |
| - | |
883 |
| - | |
| 891 | + | |
| 892 | + | |
| 893 | + | |
| 894 | + | |
884 | 895 |
| |
885 | 896 |
| |
886 | 897 |
| |
887 | 898 |
| |
888 | 899 |
| |
889 | 900 |
| |
890 | 901 |
| |
891 |
| - | |
892 |
| - | |
893 |
| - | |
| 902 | + | |
| 903 | + | |
| 904 | + | |
| 905 | + | |
894 | 906 |
| |
895 | 907 |
| |
896 | 908 |
| |
897 |
| - | |
898 |
| - | |
899 |
| - | |
900 |
| - | |
901 |
| - | |
902 |
| - | |
903 |
| - | |
904 |
| - | |
905 |
| - | |
906 |
| - | |
907 |
| - | |
908 |
| - | |
909 |
| - | |
910 |
| - | |
911 |
| - | |
912 |
| - | |
913 |
| - | |
| 909 | + | |
| 910 | + | |
| 911 | + | |
| 912 | + | |
| 913 | + | |
| 914 | + | |
| 915 | + | |
| 916 | + | |
| 917 | + | |
| 918 | + | |
| 919 | + | |
| 920 | + | |
| 921 | + | |
| 922 | + | |
| 923 | + | |
| 924 | + | |
| 925 | + | |
| 926 | + | |
| 927 | + | |
| 928 | + | |
| 929 | + | |
| 930 | + | |
| 931 | + | |
| 932 | + | |
| 933 | + | |
| 934 | + | |
| 935 | + | |
| 936 | + | |
| 937 | + | |
| 938 | + | |
| 939 | + | |
| 940 | + | |
| 941 | + | |
| 942 | + | |
| 943 | + | |
| 944 | + | |
| 945 | + | |
| 946 | + | |
| 947 | + | |
| 948 | + | |
| 949 | + | |
| 950 | + | |
| 951 | + | |
| 952 | + | |
| 953 | + | |
| 954 | + | |
| 955 | + | |
| 956 | + | |
| 957 | + | |
| 958 | + | |
| 959 | + | |
| 960 | + | |
| 961 | + | |
| 962 | + | |
914 | 963 |
| |
915 |
| - | |
916 |
| - | |
917 |
| - | |
| 964 | + | |
| 965 | + | |
| 966 | + | |
| 967 | + | |
918 | 968 |
| |
919 | 969 |
| |
920 | 970 |
| |
| |||
1981 | 2031 |
| |
1982 | 2032 |
| |
1983 | 2033 |
| |
1984 |
| - | |
1985 |
| - | |
1986 |
| - | |
1987 |
| - | |
1988 |
| - | |
1989 |
| - | |
1990 |
| - | |
1991 |
| - | |
1992 |
| - | |
1993 |
| - | |
1994 |
| - | |
1995 | 2034 |
| |
1996 | 2035 |
| |
1997 | 2036 |
| |
|
0 commit comments
Comments
(0)