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;
--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs
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
--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs