Implement <null treatment> for window functions
Hackorum builds and tests every patch posted to the lists, not only commitfest submissions. This is Hackorum's own CI rather than the PostgreSQL project's, and it is still under testing - please report anything that looks wrong.
You can run a PostgreSQL built from this patch straight from Docker, with no checkout and no build:
docker run --rm -p 5432:5432 ghcr.io/hackorum-dev/postgres-patch:t42618psql -h localhost -U postgresBuilt from patchset v1 (message #1), July 27, 2026 at 06:58 PM.
Every patchset is also pushed to a branch of our PostgreSQL fork, so you can check out the same tree CI built. Without a PostgreSQL checkout:
git clone --branch t42618_1 https://github.com/hackorum-dev/postgres.gitIn a checkout you already have, add the fork once:
git remote add hackorum https://github.com/hackorum-dev/postgres.gitthen, for this patchset and every later one:
git fetch hackorum t42618_1 && git checkout t42618_1Patchset v1 (message #1) is on t42618_1
This feature adds RESPECT NULLS and IGNORE NULLS syntax to several
window functions, according to the SQL Standard.
Unlike the last time this was attempted[1]/messages/by-id/CAGMVOdsbtRwE_4+v8zjH1d9xfovDeQAGLkP_B6k69_VoFEgX-A@mail.gmail.com -- Vik Fearing, my version does not hardcode
the spec's list of functions that this applies to. Instead, it accepts
it for all true window functions (that is, it does not apply to
aggregates acting as window functions).
This patch also does not attempt to solve the FROM LAST problem. That
remains unimplemented.
For the CREATE FUNCTION syntax, I used TREAT NULLS so as to avoid
creating new keywords.
The second patch adds some new window functions in order to test that
the null treatment works correctly for cases that aren't covered by the
standard functions but that custom functions might want to use. It is
*not* intended to be committed; I am only submitting the first patch for
inclusion in core.
This is based off of 324435eb14.
[1]: /messages/by-id/CAGMVOdsbtRwE_4+v8zjH1d9xfovDeQAGLkP_B6k69_VoFEgX-A@mail.gmail.com -- Vik Fearing
/messages/by-id/CAGMVOdsbtRwE_4+v8zjH1d9xfovDeQAGLkP_B6k69_VoFEgX-A@mail.gmail.com
--
Vik Fearing
Attachments:
t42618_10001-implement-null-treatment-for-window-functions.patchtext/x-patch; charset=UTF-8; name=0001-implement-null-treatment-for-window-functions.patchDownload+812-206
0002-new-window-functions-to-aid-testing.patchtext/x-patch; charset=UTF-8; name=0002-new-window-functions-to-aid-testing.patchDownload+172-1
Vik Fearing <vik@postgresfriends.org> writes:
The second patch adds some new window functions in order to test that
the null treatment works correctly for cases that aren't covered by the
standard functions but that custom functions might want to use. It is
*not* intended to be committed; I am only submitting the first patch for
inclusion in core.
Would it make stense to add them as a test extension under
src/test/modules/?
- ilmari
--
"A disappointingly low fraction of the human race is,
at any given time, on fire." - Stig Sandbeck Mathisen
On 30 Jun 2020, at 15:54, Vik Fearing <vik@postgresfriends.org> wrote:
This feature adds RESPECT NULLS and IGNORE NULLS syntax to several
window functions, according to the SQL Standard.
This fails compilation due to a compiler warning in WinGetFuncArgInPartition
and WinGetFuncArgInFrame (same warning in both):
nodeWindowAgg.c: In function ‘WinGetFuncArgInPartition’:
nodeWindowAgg.c:3274:10: error: ‘step’ may be used uninitialized in this function [-Werror=maybe-uninitialized]
relpos += step;
^
This was with GCC in the Travis build, the Windows build passed and so does
clang locally for me.
cheers ./daniel
On Wed, Jul 01, 2020 at 02:27:45PM +0200, Daniel Gustafsson wrote:
This was with GCC in the Travis build, the Windows build passed and so does
clang locally for me.
This was two months ago, so this patch has been marked as returned
with feedback. Please feel free to resubmit once you have a new
version.
--
Michael