Re: IGNORE/RESPECT NULLS can be specified for (prokind == 'f').

> While reviewing the row pattern recognition,
> I noticed that the IGNORE NULLS and RESPECT NULLS clauses can be specified for
> normal functions (prokind == 'f').
> 
> create or replace function dummy2(int) returns bigint AS $$ BEGIN
> RETURN $1; END$$ STABLE LANGUAGE plpgsql;
> select dummy2(1) IGNORE NULLS;
> select dummy2(1) RESPECT NULLS;
> 
> This seems unexpected?

Yes. Other than true window functions are not allowed to use the null
treatment clause per spec.

> Also https://git.postgresql.org/cgit/postgresql.git/commit/?id=25a30bbd4235a49c854036c84fe90f2bc5a87652
> Obviously, RESPECT NULLS and IGNORE NULLS can appear in SELECT query,
> since they are dedicated keywords, shouldn't they be mentioned in
> select.sgml?

No, we should fix the code. See attached patch.

To fix this, ParseFuncOrColumn() now checks whether other than window
functions are used with the null treatment clause. If so, error out.

Also remove the unnecessary test for "aggregate functions do not
accept RESPECT/IGNORE NULLS" because it is now checked in the
early-stage new check. The window regression test expected file is
changed accordingly.

Regards,
--
Tatsuo Ishii
SRA OSS K.K.
English: http://www.sraoss.co.jp/index_en/
Japanese:http://www.sraoss.co.jp