Thread: plpgsql language not aware of standard_conforming_strings ?

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$
BEGINRETURN 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$
BEGINRETURN 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 

"Sabin Coanda" <sabin.coanda@deuromedia.ro> wrote in message 
news:fh99cq$2cfn$1@news.hub.org...
...
>
> How can I get my desired function that means when I call test( 'a\b' ) it 
> will return 'a\\b' ?
>

The problem seems to be the constant evaluation in plpgsql functions which 
is not aware of  standard_conforming_strings.
An answer may be to build my own replace function, that doesn't use constant 
evaluation inside.
For instance:

CREATE OR REPLACE FUNCTION myreplace(sText varchar, sSrc varchar, sDst 
varchar)  RETURNS varchar AS $BODY$
BEGINRETURN replace( sText, sSrc, sDst );
END;
$BODY$ LANGUAGE 'plpgsql' VOLATILE;


Using this function will give the expected result, when 
standard_conforming_strings = 'on', so
SELECT myreplace( 'a\b', '\', '\\' ); will give the result 'a\\b' as 
expected.

In fact this is an workaround :((. It would be nice to make the language to 
works like that :).

Regards,
Sabin 

"Sabin Coanda" <sabin.coanda@deuromedia.ro> wrote in message 
news:fh9cbj$2pd2$1@news.hub.org...
>
> "Sabin Coanda" <sabin.coanda@deuromedia.ro> wrote in message 
> news:fh99cq$2cfn$1@news.hub.org...
> ...
>>
>> How can I get my desired function that means when I call test( 'a\b' ) it 
>> will return 'a\\b' ?
>>
>
...

> CREATE OR REPLACE FUNCTION myreplace(sText varchar, sSrc varchar, sDst
...

Unfortunatelly this is not very productive when sSrc or sDst has to be 
constants inside the function. There is another workaround for that, to 
specify '\' as chr(92). For instance:

CREATE OR REPLACE FUNCTION myformat(sText varchar)  RETURNS varchar AS 
$BODY$
BEGINRETURN replace( sText, chr(92), '\\' );
END;
$BODY$ LANGUAGE 'plpgsql' VOLATILE;

Consequently, the statement SELECT myformat('a\b' ) will get the desired 
result a\\b

Sabin