pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedBackslash characters in PLPGSQL
2+ messages / 2 participants
[nested] [flat]
* Backslash characters in PLPGSQL
@ 2010-08-03 15:05 Garrett Murphy <gmurphy@lawlogix.com>
0 siblings, 1 reply; 2+ messages in thread
From: Garrett Murphy @ 2010-08-03 15:05 UTC (permalink / raw)
To: pgsql-sql
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
^ permalink raw reply [nested|flat] 2+ messages in thread
* Re: Backslash characters in PLPGSQL
@ 2010-08-03 15:54 Tom Lane <tgl@sss.pgh.pa.us>
parent: Garrett Murphy <gmurphy@lawlogix.com>
0 siblings, 0 replies; 2+ messages in thread
From: Tom Lane @ 2010-08-03 15:54 UTC (permalink / raw)
To: Garrett Murphy <gmurphy@lawlogix.com>; +Cc: pgsql-sql
"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
^ permalink raw reply [nested|flat] 2+ messages in thread
end of thread, other threads:[~2010-08-03 15:54 UTC | newest]
Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2010-08-03 15:05 Backslash characters in PLPGSQL Garrett Murphy <gmurphy@lawlogix.com>
2010-08-03 15:54 ` Tom Lane <tgl@sss.pgh.pa.us>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox