Question about Escaping text when calling pgplsql functions

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

Browse pgsql-sql by date

  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