Thread: BUG #13909: String concat error with CITEXT after 9.5.0 upgrade.
BUG #13909: String concat error with CITEXT after 9.5.0 upgrade.
From
hein@bitechsystems.co.za
Date:
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 ;
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