> The SQL standard defines a RESPECT NULLS or IGNORE NULLS option for lead, lag, [...]. This is not implemented in PostgreSQL (http://www.postgresql.org/docs/devel/static/functions-window.html) I've had a go at implementing this, and I've attached the resulting patch. It's not finished yet, but I was hoping to find out if my solution is along the right lines.
In particular, I'm storing the ignore-nulls flag in the frameOptions of a window function definition, and am adding a function to the windowapi.h to get at these options. I'm keeping the last non-null value in WinGetPartitionLocalMemory (which I hope is the right place), but I'm not using any of the *GetDatum macros to access it. An example of my change's behaviour: nwhite=# select *, lag(num,0) ignore nulls over (order by generate_series) from nwhite-# (select generate_series from generate_series(0,10)) s nwhite-# left outer join nwhite-# numbers n nwhite-# on (s.generate_series = n.num); generate_series | num | lag -----------------+-----+----- 0 | | 1 | 1 | 1 2 | | 1 3 | | 1 4 | 4 | 4 5 | 5 | 5 6 | | 5 7 | | 5 8 | | 5 9 | 9 | 9 10 | | 9 (11 rows) I'd find this feature really useful, so I hope you can help me get my patch to a contributable state. Thanks - Nick
lead-lag-ignore-nulls.patch
Description: Binary data
-- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers