From: | Matthias Howell <matthias(dot)howell(at)gmail(dot)com> |
---|---|
To: | pgsql-sql(at)postgresql(dot)org |
Subject: | Question about Escaping text when calling pgplsql functions |
Date: | 2011-10-21 15:38:57 |
Message-ID: | CAEVmz5GKJBqnTWYaj45_0nz3-KzDgEEBwtUdWpLSMekNGo+RbA@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-sql |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Emi Lu | 2011-10-21 15:47:19 | Re: how to temporally disable foreign key constraint check |
Previous Message | Oliveiros d'Azevedo Cristina | 2011-10-21 14:58:03 | Re: how to temporally disable foreign key constraint check |