Обсуждение: BUG #14017: strange syntax error

The following bug has been logged on the website:

Bug reference:      14017
Logged by:          Anthony van den Berg
Email address:      anthony@arkia.nl
PostgreSQL version: 9.5.1
Operating system:   linux both fedra and ubuntu
Description:

When running the create or replace function on the following I get this
error

ERROR:  syntax error at or near "("
LINE 5: ...trim(isnull(new.straat,''::text) || ' ' || isnull (  new.hui...


in postgresql 9.4.5 it gives no problem

I have no idea why this happens ans the first isnull call is exactly the
same als the second

CREATE OR REPLACE FUNCTION relaties.update_insert_adressen()
  RETURNS trigger AS
$BODY$
BEGIN
    new.adres = trim(isnull(new.straat,''::text) || ' ' || isnull (
new.huisnummer,''::text));
    return new;
END;
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;
ALTER FUNCTION relaties.update_insert_adressen()
  OWNER TO postgres;



the isnull function looks like this.



CREATE OR REPLACE FUNCTION public."isnull"(
    text,
    text)
  RETURNS text AS
$BODY$SELECT (CASE (SELECT $1
is null) WHEN true THEN $2 ELSE $1 END) AS RESULT$BODY$
  LANGUAGE sql VOLATILE
  COST 100;
ALTER FUNCTION public."isnull"(text, text)
  OWNER TO postgres;

anthony@arkia.nl writes:
> When running the create or replace function on the following I get this
> error

> ERROR:  syntax error at or near "("
> LINE 5: ...trim(isnull(new.straat,''::text) || ' ' || isnull (  new.hui...

ISNULL is a keyword; it's a short form of IS NULL.  It's only by pure
chance that you've ever been able to use this function:

> CREATE OR REPLACE FUNCTION public."isnull"(

without double-quoting its name everywhere.  The fact that it accidentally
worked in this context before 9.5, but fails now, probably has something
to do with the changes we made in 9.5 to give IS NULL the syntactic
precedence required by the SQL standard.

You could rename the function to something that doesn't conflict, like
"is_null".  But really my recommendation is to drop it entirely and use
COALESCE(), which does the same thing, is considerably more efficient,
and is SQL-standard.

            regards, tom lane