Thread: Question about Escaping text when calling pgplsql functions
I have a situation where data in a text column contains line breaks of the form \r\n<br /><br />if I run a select such asselect count(1) from table where wordcol = 'word1\r\nword2' I get 0.<br /><br />Same if I run select count(1) from tablewhere wordcol = 'word1\nword2'.<br /><br />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).<br /><br />This is fine if I've got the literal string.<br /><br/>The issue I have is that I'm calling a function:<br /><br />create or replace function escapereadertest(word text)returns int as $$<br />declare<br /> retval int;<br />begin<br /> select count(1) from table where wordcol = wordinto retval;<br /> return retval;<br />end;$$<br />language 'plpgsql';<br /><br />I haven't found a way to escapethe word variable.<br /><br />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 aswell. I'd like to get 18.<br /><br />Is there any way of escaping the word variable inside the function or of pre-escapingthe value in java before sending it on or do I have to dynamically build the sql to get the E prefixed to theword as a quoted literal.<br /><br />I've looked at the string functions but haven't found what I'm looking for - I maybelooking in the wrong place.<br /><br />Thanks for any ideas.<br /><br />Matthias<br />