> 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 (there's no documentation and no tests for a start),
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

Attachment: 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

Reply via email to