We recently upgraded from 8.3 to 8.4 and are noticing a change in behavior that we can’t seem to associate with a particular server setting.
In 8.3, the following compiles and works perfectly:
CREATE OR REPLACE FUNCTION test_function2(tText TEXT) RETURNS TEXT AS
$BODY$
BEGIN
--
RETURN REPLACE(tText,'\','\\');
END;
$BODY$
LANGUAGE 'plpgsql' VOLATILE;
SELECT test_function2('Bob\');
test_function2
text
"BOB\\"
However, in 8.4, attempting to save the same function results in:
ERROR: unterminated string
CONTEXT: compile of PL/pgSQL function "test_function2" near line 3
It’s clear that it’s interpreting the backslashes as escaping the following quote characters, as it compiles and works correctly if I put a space between the slash and the quote character.
Escaping the backslash with another backslash, with or without the E character at the start of the string, doesn’t resolve anything. In fact, escaping the backslash like so:
RETURN REPLACE(tText,'\\','\\\\');
Works perfectly…to replace two backslashes:
SELECT test_function2('Bob\');
test_function2
text
"BOB\"
SELECT test_function2('Bob\\');
test_function2
text
"BOB\\\\"
I’ve checked the only two server config settings that would appear to impact this:
standard_conforming_strings (set to ON)
backslash_quote (set to SAFE_ENCODING)
Changing the server setting doesn’t appear to have an impact. Does anybody have a suggestion on what I’m missing?
Garrett Murphy
