Backslash characters in PLPGSQL

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

Responses

Browse pgsql-sql by date

  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