Backslash characters in PLPGSQL - Mailing list pgsql-sql

<div class="Section1"><p class="MsoNormal">We recently upgraded from 8.3 to 8.4 and are noticing a change in behavior
thatwe can’t seem to associate with a particular server setting.<p class="MsoNormal"> <p class="MsoNormal">In 8.3, the
followingcompiles and works perfectly:<p class="MsoNormal">CREATE OR REPLACE FUNCTION test_function2(tText TEXT)
RETURNSTEXT AS<p class="MsoNormal">$BODY$<p class="MsoNormal">                BEGIN<p
class="MsoNormal">                               --<p class="MsoNormal">                                RETURN
REPLACE(tText,'\','\\');<pclass="MsoNormal">                END;<p class="MsoNormal">$BODY$<p
class="MsoNormal">LANGUAGE'plpgsql' VOLATILE;<p class="MsoNormal"> <p class="MsoNormal">SELECT
test_function2('Bob\');<pclass="MsoNormal"> <p class="MsoNormal">test_function2<p class="MsoNormal">text<p
class="MsoNormal">"BOB\\"<pclass="MsoNormal"> <p class="MsoNormal">However, in 8.4, attempting to save the same
functionresults in:<p class="MsoNormal">ERROR:  unterminated string<p class="MsoNormal">CONTEXT:  compile of PL/pgSQL
function"test_function2" near line 3<p class="MsoNormal"> <p class="MsoNormal">It’s clear that it’s interpreting the
backslashesas escaping the following quote characters, as it compiles and works correctly if I put a space between the
slashand the quote character.  <p class="MsoNormal"> <p class="MsoNormal">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
backslashlike so:<p class="MsoNormal">RETURN REPLACE(tText,'\\','\\\\');<p class="MsoNormal">Works perfectly…to replace
twobackslashes:<p class="MsoNormal"> <p class="MsoNormal">SELECT test_function2('Bob\');<p class="MsoNormal"> <p
class="MsoNormal">test_function2<pclass="MsoNormal">text<p class="MsoNormal">"BOB\"<p class="MsoNormal"> <p
class="MsoNormal">SELECTtest_function2('Bob\\');<p class="MsoNormal"> <p class="MsoNormal">test_function2<p
class="MsoNormal">text<pclass="MsoNormal">"BOB\\\\"<p class="MsoNormal"> <p class="MsoNormal">I’ve checked the only two
serverconfig settings that would appear to impact this:<p class="MsoNormal">standard_conforming_strings (set to ON)<p
class="MsoNormal">backslash_quote(set to SAFE_ENCODING)<p class="MsoNormal"> <p class="MsoNormal">Changing the server
settingdoesn’t appear to have an impact.  Does anybody have a suggestion on what I’m missing?<p class="MsoNormal"> <p
class="MsoNormal"> <pclass="MsoNormal"> <p class="MsoNormal"><span
style="font-size:10.0pt;font-family:"Verdana","sans-serif"">GarrettMurphy</span></div>