From: | "Garrett Murphy" <gmurphy(at)lawlogix(dot)com> |
---|---|
To: | <pgsql-sql(at)postgresql(dot)org> |
Subject: | Backslash characters in PLPGSQL |
Date: | 2010-08-03 15:05:39 |
Message-ID: | 076DC33A3D38CE4BBC64D35DDD9DE70C0AD972F3@mse4be2.mse4.exchange.ms |
Views: | Whole Thread | Raw Message | Download mbox | Resend email |
Thread: | |
Lists: | 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
From | Date | Subject | |
---|---|---|---|
Next Message | Tom Lane | 2010-08-03 15:54:28 | Re: Backslash characters in PLPGSQL |
Previous Message | Tom Lane | 2010-08-03 14:48:25 | Re: aggregate function |