BUG #13909: String concat error with CITEXT after 9.5.0 upgrade.
The following bug has been logged on the website:
Bug reference: 13909
Logged by: Hein
Email address: hein@bitechsystems.co.za
PostgreSQL version: 9.5.0
Operating system: Windows 10 / Linux
Description:
After upgrading to 9.5 with a new database, concatenation of a citext and a
function that returns citext does not work. It gives a syntax error.
Code to duplicate, requires citext extension:
CREATE OR REPLACE FUNCTION isnull(
p_value citext
,p_replacevalue citext = ''
)
RETURNS citext
AS $$
select case when p_value is null then p_replacevalue else p_value end
$$ LANGUAGE sql IMMUTABLE COST 1;
DO
$$
DECLARE
m_text citext;
BEGIN
m_text = 'test' || isnull(m_text,''); --code breaks here!
--raise notice '%', isnull(m_text);
END;
$$ LANGUAGE plpgsql ;
--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs
hein@bitechsystems.co.za writes:
m_text = 'test' || isnull(m_text,''); --code breaks here!
ISNULL is a keyword, which I can't really recommend using as a function
name. This example used to accidentally fail to fail before 9.5 because
ISNULL was considered to bind tighter than ||. But now it binds less
tightly, meaning that the first part of the expression is parsed as
(('test' ||) IS NULL), which is not what you meant.
You could fix this with more parentheses:
m_text = 'test' || (isnull(m_text,''));
or by double-quoting the function name so it doesn't look like a keyword:
m_text = 'test' || "isnull"(m_text,'');
but you might be better advised to rename the function.
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