I have a situation where data in a text column contains line breaks of the
form \r\n
if I run a select such as select count(1) from table where wordcol =
'word1\r\nword2' I get 0.
Same if I run select count(1) from table where wordcol = 'word1\nword2'.
I get values if I run select count(1) from table where
wordcol=E'word1\r\nword2' I get a value (18 in this case).
This is fine if I've got the literal string.
The issue I have is that I'm calling a function:
create or replace function escapereadertest(word text) returns int as $$
declare
retval int;
begin
select count(1) from table where wordcol = word into retval;
return retval;
end;$$
language 'plpgsql';
I haven't found a way to escape the word variable.
I'm calling this function from java via callable statement like this: call
escapereadertest(?) and setting the parameter to a string that contains
"word1\r\nword2"; Calling it this way gets me 0 as well. I'd like to get
18.
Is there any way of escaping the word variable inside the function or of
pre-escaping the value in java before sending it on or do I have to
dynamically build the sql to get the E prefixed to the word as a quoted
literal.
I've looked at the string functions but haven't found what I'm looking for -
I maybe looking in the wrong place.
Thanks for any ideas.
Matthias
