Re: Add RESPECT/IGNORE NULLS and FROM FIRST/LAST options

>> The attached test patch is mostly the same as in the previous patch
>> set, but it doesn't fail on row_number anymore as the main patch
>> only rejects aggregate functions. The test patch also adds a test for
>
>> +SELECT sum(orbit) RESPECT NULLS OVER () FROM planets; -- succeeds
>
> I think the standard does not allow to specify RESPECT NULLS other
> than lead, lag, first_value, last_value and nth_value. Unless we agree
> that PostgreSQL violates the standard in this regard, you should not
> allow to use RESPECT NULLS for the window functions, expect lead etc.
> and aggregates.
>
> See my patch.
>
>> +/*
>> + * Window function option clauses
>> + */
>> +opt_null_treatment:
>> + RESPECT NULLS_P { $$ = RESPECT_NULLS; }
>> + | IGNORE_P NULLS_P { $$ = IGNORE_NULLS; }
>> + | /*EMPTY*/ { $$ = NULL_TREATMENT_NOT_SET; }
>> + ;
>
> With this, you can check if null treatment clause is used or not in
> each window function.
>
> In my previous patch I did the check in parse/analysis but I think
> it's better to be checked in each window function. This way,
>
> - need not to add a column to pg_proc.
>
> - allow user defined window functions to decide by themselves whether
> they can accept null treatment option.

Attached is the patch to implement this (on top of your patch).

test=# SELECT row_number() RESPECT NULLS OVER () FROM (SELECT 1) AS s;
ERROR: window function row_number cannot have RESPECT NULLS or IGNORE NULLS

Best reagards,
--
Tatsuo Ishii
SRA OSS LLC
English: http://www.sraoss.co.jp/index_en/
Japanese:http://www.sraoss.co.jp