pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedplpgsql language not aware of standard_conforming_strings ?
3+ messages / 1 participants
[nested] [flat]
* plpgsql language not aware of standard_conforming_strings ?
@ 2007-11-12 10:20 Sabin Coanda <sabin.coanda@deuromedia.ro>
2007-11-12 11:10 ` Re: plpgsql language not aware of standard_conforming_strings ? Sabin Coanda <sabin.coanda@deuromedia.ro>
0 siblings, 1 reply; 3+ messages in thread
From: Sabin Coanda @ 2007-11-12 10:20 UTC (permalink / raw)
To: 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
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: plpgsql language not aware of standard_conforming_strings ?
2007-11-12 10:20 plpgsql language not aware of standard_conforming_strings ? Sabin Coanda <sabin.coanda@deuromedia.ro>
@ 2007-11-12 11:10 ` Sabin Coanda <sabin.coanda@deuromedia.ro>
2007-11-12 16:19 ` Re: plpgsql language not aware of standard_conforming_strings ? Sabin Coanda <sabin.coanda@deuromedia.ro>
0 siblings, 1 reply; 3+ messages in thread
From: Sabin Coanda @ 2007-11-12 11:10 UTC (permalink / raw)
To: pgsql-sql
"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$
BEGIN
RETURN 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
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: plpgsql language not aware of standard_conforming_strings ?
2007-11-12 10:20 plpgsql language not aware of standard_conforming_strings ? Sabin Coanda <sabin.coanda@deuromedia.ro>
2007-11-12 11:10 ` Re: plpgsql language not aware of standard_conforming_strings ? Sabin Coanda <sabin.coanda@deuromedia.ro>
@ 2007-11-12 16:19 ` Sabin Coanda <sabin.coanda@deuromedia.ro>
0 siblings, 0 replies; 3+ messages in thread
From: Sabin Coanda @ 2007-11-12 16:19 UTC (permalink / raw)
To: pgsql-sql
"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$
BEGIN
RETURN replace( sText, chr(92), '\\' );
END;
$BODY$
LANGUAGE 'plpgsql' VOLATILE;
Consequently, the statement SELECT myformat('a\b' ) will get the desired
result a\\b
Sabin
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2007-11-12 16:19 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2007-11-12 10:20 plpgsql language not aware of standard_conforming_strings ? Sabin Coanda <sabin.coanda@deuromedia.ro>
2007-11-12 11:10 ` Sabin Coanda <sabin.coanda@deuromedia.ro>
2007-11-12 16:19 ` Sabin Coanda <sabin.coanda@deuromedia.ro>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox