From gmurphy@lawlogix.com Tue Aug  3 15:12:33 2010
Received: from maia.hub.org (maia-5.hub.org [200.46.204.29])
	by mail.postgresql.org (Postfix) with ESMTP id 79B0063382F
	for <pgsql-sql-postgresql.org@mail.postgresql.org>;
	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
	<pgsql-sql-postgresql.org@mail.postgresql.org>;
	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 <pgsql-sql@postgresql.org>; 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 <pgsql-sql@postgresql.org>; 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" <gmurphy@lawlogix.com>
To: <pgsql-sql@postgresql.org>
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

<html xmlns:v=3D"urn:schemas-microsoft-com:vml" =
xmlns:o=3D"urn:schemas-microsoft-com:office:office" =
xmlns:w=3D"urn:schemas-microsoft-com:office:word" =
xmlns:x=3D"urn:schemas-microsoft-com:office:excel" =
xmlns:p=3D"urn:schemas-microsoft-com:office:powerpoint" =
xmlns:a=3D"urn:schemas-microsoft-com:office:access" =
xmlns:dt=3D"uuid:C2F41010-65B3-11d1-A29F-00AA00C14882" =
xmlns:s=3D"uuid:BDC6E3F0-6DA3-11d1-A2A3-00AA00C14882" =
xmlns:rs=3D"urn:schemas-microsoft-com:rowset" xmlns:z=3D"#RowsetSchema" =
xmlns:b=3D"urn:schemas-microsoft-com:office:publisher" =
xmlns:ss=3D"urn:schemas-microsoft-com:office:spreadsheet" =
xmlns:c=3D"urn:schemas-microsoft-com:office:component:spreadsheet" =
xmlns:odc=3D"urn:schemas-microsoft-com:office:odc" =
xmlns:oa=3D"urn:schemas-microsoft-com:office:activation" =
xmlns:html=3D"http://www.w3.org/TR/REC-html40" =
xmlns:q=3D"http://schemas.xmlsoap.org/soap/envelope/" =
xmlns:rtc=3D"http://microsoft.com/officenet/conferencing" =
xmlns:D=3D"DAV:" xmlns:Repl=3D"http://schemas.microsoft.com/repl/" =
xmlns:mt=3D"http://schemas.microsoft.com/sharepoint/soap/meetings/" =
xmlns:x2=3D"http://schemas.microsoft.com/office/excel/2003/xml" =
xmlns:ppda=3D"http://www.passport.com/NameSpace.xsd" =
xmlns:ois=3D"http://schemas.microsoft.com/sharepoint/soap/ois/" =
xmlns:dir=3D"http://schemas.microsoft.com/sharepoint/soap/directory/" =
xmlns:ds=3D"http://www.w3.org/2000/09/xmldsig#" =
xmlns:dsp=3D"http://schemas.microsoft.com/sharepoint/dsp" =
xmlns:udc=3D"http://schemas.microsoft.com/data/udc" =
xmlns:xsd=3D"http://www.w3.org/2001/XMLSchema" =
xmlns:sub=3D"http://schemas.microsoft.com/sharepoint/soap/2002/1/alerts/"=
 xmlns:ec=3D"http://www.w3.org/2001/04/xmlenc#" =
xmlns:sp=3D"http://schemas.microsoft.com/sharepoint/" =
xmlns:sps=3D"http://schemas.microsoft.com/sharepoint/soap/" =
xmlns:xsi=3D"http://www.w3.org/2001/XMLSchema-instance" =
xmlns:udcs=3D"http://schemas.microsoft.com/data/udc/soap" =
xmlns:udcxf=3D"http://schemas.microsoft.com/data/udc/xmlfile" =
xmlns:udcp2p=3D"http://schemas.microsoft.com/data/udc/parttopart" =
xmlns:wf=3D"http://schemas.microsoft.com/sharepoint/soap/workflow/" =
xmlns:dsss=3D"http://schemas.microsoft.com/office/2006/digsig-setup" =
xmlns:dssi=3D"http://schemas.microsoft.com/office/2006/digsig" =
xmlns:mdssi=3D"http://schemas.openxmlformats.org/package/2006/digital-sig=
nature" =
xmlns:mver=3D"http://schemas.openxmlformats.org/markup-compatibility/2006=
" xmlns:m=3D"http://schemas.microsoft.com/office/2004/12/omml" =
xmlns:mrels=3D"http://schemas.openxmlformats.org/package/2006/relationshi=
ps" xmlns:spwp=3D"http://microsoft.com/sharepoint/webpartpages" =
xmlns:ex12t=3D"http://schemas.microsoft.com/exchange/services/2006/types"=
 =
xmlns:ex12m=3D"http://schemas.microsoft.com/exchange/services/2006/messag=
es" =
xmlns:pptsl=3D"http://schemas.microsoft.com/sharepoint/soap/SlideLibrary/=
" =
xmlns:spsl=3D"http://microsoft.com/webservices/SharePointPortalServer/Pub=
lishedLinksService" xmlns:Z=3D"urn:schemas-microsoft-com:" =
xmlns:st=3D"&#1;" xmlns=3D"http://www.w3.org/TR/REC-html40">

<head>
<meta http-equiv=3DContent-Type content=3D"text/html; =
charset=3Dus-ascii">
<meta name=3DGenerator content=3D"Microsoft Word 12 (filtered medium)">
<style>
<!--
 /* Font Definitions */
 @font-face
	{font-family:Calibri;
	panose-1:2 15 5 2 2 2 4 3 2 4;}
@font-face
	{font-family:Verdana;
	panose-1:2 11 6 4 3 5 4 4 2 4;}
 /* Style Definitions */
 p.MsoNormal, li.MsoNormal, div.MsoNormal
	{margin:0in;
	margin-bottom:.0001pt;
	font-size:11.0pt;
	font-family:"Calibri","sans-serif";}
a:link, span.MsoHyperlink
	{mso-style-priority:99;
	color:blue;
	text-decoration:underline;}
a:visited, span.MsoHyperlinkFollowed
	{mso-style-priority:99;
	color:purple;
	text-decoration:underline;}
span.EmailStyle17
	{mso-style-type:personal-compose;
	font-family:"Calibri","sans-serif";
	color:windowtext;}
.MsoChpDefault
	{mso-style-type:export-only;}
@page Section1
	{size:8.5in 11.0in;
	margin:1.0in 1.0in 1.0in 1.0in;}
div.Section1
	{page:Section1;}
-->
</style>
<!--[if gte mso 9]><xml>
 <o:shapedefaults v:ext=3D"edit" spidmax=3D"1026" />
</xml><![endif]--><!--[if gte mso 9]><xml>
 <o:shapelayout v:ext=3D"edit">
  <o:idmap v:ext=3D"edit" data=3D"1" />
 </o:shapelayout></xml><![endif]-->
</head>

<body lang=3DEN-US link=3Dblue vlink=3Dpurple>

<div class=3DSection1>

<p class=3DMsoNormal>We recently upgraded from 8.3 to 8.4 and are =
noticing a
change in behavior that we can&#8217;t seem to associate with a =
particular
server setting.<o:p></o:p></p>

<p class=3DMsoNormal><o:p>&nbsp;</o:p></p>

<p class=3DMsoNormal>In 8.3, the following compiles and works =
perfectly:<o:p></o:p></p>

<p class=3DMsoNormal>CREATE OR REPLACE FUNCTION test_function2(tText =
TEXT)
RETURNS TEXT AS<o:p></o:p></p>

<p class=3DMsoNormal>$BODY$<o:p></o:p></p>

<p =
class=3DMsoNormal>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&=
nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; BEGIN<o:p></o:p></p>

<p =
class=3DMsoNormal>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&=
nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&n=
bsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; =
--<o:p></o:p></p>

<p =
class=3DMsoNormal>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&=
nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&n=
bsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; RETURN
REPLACE(tText,'\','\\');<o:p></o:p></p>

<p =
class=3DMsoNormal>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&=
nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; END;<o:p></o:p></p>

<p class=3DMsoNormal>$BODY$<o:p></o:p></p>

<p class=3DMsoNormal>LANGUAGE 'plpgsql' VOLATILE;<o:p></o:p></p>

<p class=3DMsoNormal><o:p>&nbsp;</o:p></p>

<p class=3DMsoNormal>SELECT test_function2('Bob\');<o:p></o:p></p>

<p class=3DMsoNormal><o:p>&nbsp;</o:p></p>

<p class=3DMsoNormal>test_function2<o:p></o:p></p>

<p class=3DMsoNormal>text<o:p></o:p></p>

<p class=3DMsoNormal>&quot;BOB\\&quot;<o:p></o:p></p>

<p class=3DMsoNormal><o:p>&nbsp;</o:p></p>

<p class=3DMsoNormal>However, in 8.4, attempting to save the same =
function
results in:<o:p></o:p></p>

<p class=3DMsoNormal>ERROR:&nbsp; unterminated string<o:p></o:p></p>

<p class=3DMsoNormal>CONTEXT:&nbsp; compile of PL/pgSQL function
&quot;test_function2&quot; near line 3<o:p></o:p></p>

<p class=3DMsoNormal><o:p>&nbsp;</o:p></p>

<p class=3DMsoNormal>It&#8217;s clear that it&#8217;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.&nbsp; <o:p></o:p></p>

<p class=3DMsoNormal><o:p>&nbsp;</o:p></p>

<p class=3DMsoNormal>Escaping the backslash with another backslash, with =
or
without the E character at the start of the string, doesn&#8217;t =
resolve
anything.&nbsp; In fact, escaping the backslash like so:<o:p></o:p></p>

<p class=3DMsoNormal>RETURN REPLACE(tText,'\\','\\\\');<o:p></o:p></p>

<p class=3DMsoNormal>Works perfectly&#8230;to replace two =
backslashes:<o:p></o:p></p>

<p class=3DMsoNormal><o:p>&nbsp;</o:p></p>

<p class=3DMsoNormal>SELECT test_function2('Bob\');<o:p></o:p></p>

<p class=3DMsoNormal><o:p>&nbsp;</o:p></p>

<p class=3DMsoNormal>test_function2<o:p></o:p></p>

<p class=3DMsoNormal>text<o:p></o:p></p>

<p class=3DMsoNormal>&quot;BOB\&quot;<o:p></o:p></p>

<p class=3DMsoNormal><o:p>&nbsp;</o:p></p>

<p class=3DMsoNormal>SELECT test_function2('Bob\\');<o:p></o:p></p>

<p class=3DMsoNormal><o:p>&nbsp;</o:p></p>

<p class=3DMsoNormal>test_function2<o:p></o:p></p>

<p class=3DMsoNormal>text<o:p></o:p></p>

<p class=3DMsoNormal>&quot;BOB\\\\&quot;<o:p></o:p></p>

<p class=3DMsoNormal><o:p>&nbsp;</o:p></p>

<p class=3DMsoNormal>I&#8217;ve checked the only two server config =
settings that
would appear to impact this:<o:p></o:p></p>

<p class=3DMsoNormal>standard_conforming_strings (set to =
ON)<o:p></o:p></p>

<p class=3DMsoNormal>backslash_quote (set to =
SAFE_ENCODING)<o:p></o:p></p>

<p class=3DMsoNormal><o:p>&nbsp;</o:p></p>

<p class=3DMsoNormal>Changing the server setting doesn&#8217;t appear to =
have an
impact.&nbsp; Does anybody have a suggestion on what I&#8217;m =
missing?<o:p></o:p></p>

<p class=3DMsoNormal><o:p>&nbsp;</o:p></p>

<p class=3DMsoNormal><o:p>&nbsp;</o:p></p>

<p class=3DMsoNormal><o:p>&nbsp;</o:p></p>

<p class=3DMsoNormal><span =
style=3D'font-size:10.0pt;font-family:"Verdana","sans-serif"'>Garrett
Murphy</span><o:p></o:p></p>

</div>

</body>

</html>

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



From tgl@sss.pgh.pa.us Tue Aug  3 15:54:38 2010
Received: from maia.hub.org (maia-2.hub.org [200.46.204.251])
	by mail.postgresql.org (Postfix) with ESMTP id 8CCDB632541
	for <pgsql-sql-postgresql.org@mail.postgresql.org>;
	Tue,  3 Aug 2010 12:54:38 -0300 (ADT)
Received: from mail.postgresql.org ([200.46.204.86])
	by maia.hub.org (mx1.hub.org [200.46.204.251]) (amavisd-maia,
	port 10024) with ESMTP id 99363-02 for
	<pgsql-sql-postgresql.org@mail.postgresql.org>;
	Tue,  3 Aug 2010 15:54:31 +0000 (UTC)
X-Greylist: from auto-whitelisted by SQLgrey-1.7.6
Received: from sss.pgh.pa.us (sss.pgh.pa.us [66.207.139.130])
	by mail.postgresql.org (Postfix) with ESMTP id 6F9AE6321CA
	for <pgsql-sql@postgresql.org>; Tue,  3 Aug 2010 12:54:31 -0300 (ADT)
Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1])
	by sss.pgh.pa.us (8.14.2/8.14.2) with ESMTP id o73FsSV3021045;
	Tue, 3 Aug 2010 11:54:28 -0400 (EDT)
To: "Garrett Murphy" <gmurphy@lawlogix.com>
cc: pgsql-sql@postgresql.org
Subject: Re: Backslash characters in PLPGSQL 
In-reply-to: 
 <076DC33A3D38CE4BBC64D35DDD9DE70C0AD972F3@mse4be2.mse4.exchange.ms>
References: 
 <076DC33A3D38CE4BBC64D35DDD9DE70C0AD972F3@mse4be2.mse4.exchange.ms>
Comments: In-reply-to "Garrett Murphy" <gmurphy@lawlogix.com>
	message dated "Tue, 03 Aug 2010 11:05:39 -0400"
Date: Tue, 03 Aug 2010 11:54:28 -0400
Message-ID: <21044.1280850868@sss.pgh.pa.us>
From: Tom Lane <tgl@sss.pgh.pa.us>
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Spam-Status: No, hits=-1.91 tagged_above=-5 required=5 tests=BAYES_00=-1.9,
	T_RP_MATCHES_RCVD=-0.01
X-Spam-Level: 
X-Archive-Number: 201008/4
X-Sequence-Number: 34444

"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 as an ordinary character;
  formerly it was misinterpreted as escaping the ending quote.  (Since the
  string also had to pass through 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