From: Garrett Murphy <gmurphy@lawlogix.com>
To: pgsql-sql@postgresql.org
Subject: Backslash characters in PLPGSQL
Date: Tue, 3 Aug 2010 11:05:39 -0400
Message-ID: <076DC33A3D38CE4BBC64D35DDD9DE70C0AD972F3@mse4be2.mse4.exchange.ms> (raw)
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: gmurphy@lawlogix.com
Subject: Re: Backslash characters in PLPGSQL
In-Reply-To: <076DC33A3D38CE4BBC64D35DDD9DE70C0AD972F3@mse4be2.mse4.exchange.ms>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
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