Adds the options RESPECT/IGNORE NULLS (null treatment clause) and FROM FIRST/LAST to the non-aggregate window functions.
A previous patch (https://www.postgresql.org/message-id/CA+=vxna5_n1q5q5okxc0aqnndbo2ru6gvw+86wk+onsunjd...@mail.gmail.com) partially implemented this feature. However, that patch worked by adding the null treatment clause to the window frame's frameOptions variable, and consequently had the limitation that it wasn't possible to reuse a window frame definition in a single query where two functions were called that had different null treatment options. This meant that the patch was never committed. The attached path takes a different approach which gets around this limitation. For example, the following query would not work correctly with the implementation in the old patch but does with the attached patch: WITH cte (x) AS ( select null union select 1 union select 2) SELECT x, first_value(x) over w as with_default, first_value(x) respect nulls over w as with_respect, first_value(x) ignore nulls over w as with_ignore from cte WINDOW w as (order by x nulls first rows between unbounded preceding and unbounded following); x | with_default | with_respect | with_ignore ---+--------------+--------------+------------- | | | 1 1 | | | 1 2 | | | 1 (3 rows) == Implementation == The patch adds two types to the pg_type catalog: "ignorenulls" and "fromlast". These types are of the Boolean category, and work as wrappers around the bool type. They are used as function arguments to extra versions of the window functions that take additional boolean arguments. RESPECT NULLS and FROM FIRST are ignored by the parser, but IGNORE NULLS and FROM LAST lead to the extra versions being called with arguments to ignore nulls and order from last. == Testing == Updated documentation and added regression tests. All existing tests pass. This change will need a catversion bump. Thanks to Krasiyan Andreev for initially testing this patch.
0001-respect.patch
Description: Binary data
