Received: from maia.hub.org (maia-5.hub.org [200.46.204.29]) by mail.postgresql.org (Postfix) with ESMTP id 79B0063382F for ; Tue, 3 Aug 2010 12:12:33 -0300 (ADT) Received: from mail.postgresql.org ([200.46.204.86]) by maia.hub.org (mx1.hub.org [200.46.204.29]) (amavisd-maia, port 10024) with ESMTP id 14723-02 for ; Tue, 3 Aug 2010 15:12:24 +0000 (UTC) X-Greylist: delayed 00:05:41.130951 by SQLgrey-1.7.6 Received: from sh8.exchange.ms (sh8.exchange.ms [64.71.238.90]) by mail.postgresql.org (Postfix) with ESMTP id 3A2F1633586 for ; Tue, 3 Aug 2010 12:12:24 -0300 (ADT) Received: from outbound.mse4.exchange.ms (unknown [10.0.25.204]) by sh8.exchange.ms (Postfix) with ESMTP id 75CB3AC7B5 for ; Tue, 3 Aug 2010 05:03:22 -0400 (EDT) X-MimeOLE: Produced By Microsoft Exchange V6.5 Content-class: urn:content-classes:message MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----_=_NextPart_001_01CB331D.56270763" Subject: Backslash characters in PLPGSQL Date: Tue, 3 Aug 2010 11:05:39 -0400 Message-ID: <076DC33A3D38CE4BBC64D35DDD9DE70C0AD972F3@mse4be2.mse4.exchange.ms> X-MS-Has-Attach: X-MS-TNEF-Correlator: Thread-Topic: Backslash characters in PLPGSQL Thread-Index: AcszHVVm8V49NiCaRwmxml32C/GnDQ== From: "Garrett Murphy" To: X-MailStreet-MailScanner-ID: 75CB3AC7B5.6FDF2 X-MailStreet-MailScanner: Found to be clean X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=-1.899 tagged_above=-5 required=5 tests=BAYES_00=-1.9, HTML_MESSAGE=0.001 X-Spam-Level: X-Archive-Number: 201008/3 X-Sequence-Number: 34443 This is a multi-part message in MIME format. ------_=_NextPart_001_01CB331D.56270763 Content-Type: text/plain; charset="us-ascii" Content-Transfer-Encoding: quoted-printable 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. =20 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; =20 SELECT test_function2('Bob\'); =20 test_function2 text "BOB\\" =20 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 =20 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. =20 =20 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: =20 SELECT test_function2('Bob\'); =20 test_function2 text "BOB\" =20 SELECT test_function2('Bob\\'); =20 test_function2 text "BOB\\\\" =20 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) =20 Changing the server setting doesn't appear to have an impact. Does anybody have a suggestion on what I'm missing? =20 =20 =20 Garrett Murphy ------_=_NextPart_001_01CB331D.56270763 Content-Type: text/html; charset="us-ascii" Content-Transfer-Encoding: quoted-printable

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$

         &= nbsp;      BEGIN

         &= nbsp;           &n= bsp;          = --

         &= nbsp;           &n= bsp;          RETURN REPLACE(tText,'\','\\');

         &= nbsp;      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

------_=_NextPart_001_01CB331D.56270763--