Thread: Backslash characters in PLPGSQL

<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> 

"Garrett Murphy" <gmurphy@lawlogix.com> writes:
> 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.

I think you're seeing the effects of this 8.4 patch:
http://archives.postgresql.org/pgsql-committers/2009-04/msg00216.php
in particular:

* In standard_conforming_strings mode, backslash as the last character of a non-E string literal is now correctly taken
asan ordinary character; formerly it was misinterpreted as escaping the ending quote.  (Since the string also had to
passthrough the core scanner, this invariably led to syntax errors.)
 

However, as I wrote there, I was under the impression that this fix
didn't break any cases that actually worked usefully before 8.4.
I tried your example and as far as I can tell, 8.3.x fails on
test_function2(), giving "unterminated string" with or without
standard_conforming_strings set.  But 8.4 accepts it, if you have
standard_conforming_strings set.  I wonder whether your actual
function has yet another improperly terminated string on another
line, thereby allowing the 8.3 plpgsql scanner to get back into
sync.  But it's not obvious why that wouldn't lead to visible
misbehavior, or why 8.4 would not like it.
        regards, tom lane