From: "Sabin Coanda" <sabin.coanda@deuromedia.ro>
Subject: plpgsql language not aware of standard_conforming_strings ?
Date: Mon, 12 Nov 2007 12:20:19 +0200
Organization: Hub.Org Networking Services
Lines: 48
Message-ID: <fh99cq$2cfn$1@news.hub.org>
To: pgsql-sql@postgresql.org

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