plpgsql language not aware of standard_conforming_strings ?

From: "Sabin Coanda" <sabin(dot)coanda(at)deuromedia(dot)ro>
To: pgsql-sql(at)postgresql(dot)org
Subject: plpgsql language not aware of standard_conforming_strings ?
Date: 2007-11-12 10:20:19
Message-ID: fh99cq$2cfn$1@news.hub.org
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-sql

Hi there,

Having standard_conforming_strings = 'on', I build the following scenario.

I request SELECT replace( 'a\b', '\', '\\' ), which get me the result:

replace
---------
a\\b

I'd like to build a function that give me the same result, as:

CREATE OR REPLACE FUNCTION "test"(s varchar)
RETURNS varchar AS
$BODY$
BEGIN
RETURN replace( s, '\', '\\' );
END;
$BODY$
LANGUAGE 'plpgsql' VOLATILE;

But I rises the error:
ERROR: unterminated string
SQL state: 42804
Context: compile of PL/pgSQL function "test" near line 3

Ok, I suppose the function is not aware of standard_conforming_strings =
'on', so I have to change \ with \\. I make the following function:

CREATE OR REPLACE FUNCTION "test"(s varchar) RETURNS varchar AS $BODY$
BEGIN
RETURN replace( s, '\\', '\\\\' );
END;
$BODY$
LANGUAGE 'plpgsql' VOLATILE;

The function is created without errors.

But this is useless because SELECT test( 'a\b' ); returns a\b, and SELECT
test( 'a\\b' ); returns a\\\\b.

How can I get my desired function that means when I call test( 'a\b' ) it
will return 'a\\b' ?

TIA,
Sabin

Responses

Browse pgsql-sql by date

  From Date Subject
Next Message Achilleas Mantzios 2007-11-12 10:20:54 Re: show value of backslashes in string array argument
Previous Message Achilleas Mantzios 2007-11-12 09:58:18 Re: show value of backslashes in string array argument