| From: | Nicholas White <n(dot)j(dot)white(at)gmail(dot)com> |
|---|---|
| To: | pgsql-hackers(at)postgresql(dot)org |
| Subject: | Request for Patch Feedback: Lag & Lead Window Functions Can Ignore Nulls |
| Date: | 2013-03-23 17:00:25 |
| Message-ID: | CA+=vxNa5_N1q5q5OkxC0aQnNdbo2Ru6GVw+86wk+oNsUNJDLig@mail.gmail.com |
| Views: | Whole Thread | Raw Message | Download mbox | Resend email |
| Thread: | |
| Lists: | pgsql-hackers |
> 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
| Attachment | Content-Type | Size |
|---|---|---|
| lead-lag-ignore-nulls.patch | application/octet-stream | 14.1 KB |
| From | Date | Subject | |
|---|---|---|---|
| Next Message | Adriano Lange | 2013-03-23 17:51:18 | Re: SDP query optimizer |
| Previous Message | Tom Lane | 2013-03-23 15:16:26 | Re: timeofday() and clock_timestamp() produce different results when casting to timestamptz |