Thread: [7.4.6] Escaping strings in text arrays passed through JDBC?

Hi folks,

The list was extremely helpful on my last bit of esoterica, so I
thought that I'd ask something else that is holding me up.

Imagine a PLpgSQL function defined as:

CREATE OR REPLACE FUNCTION do_stuff (text[])
RETURNS INTEGER
AS
'
[... do stuff ...]
' LANGUAGE pgplsql;

I need to call this function via a CallableStatement in JDBC.  I know
that you can set that up like:

CallableStatement stmt = conn.prepareCall ("{ ? = call do_stuff
(?::text[]) }";
stmt.registerOutParameter (1, Types.INTEGER);
stmt.setObject (2, "{x, y, z}")

But what can I do when one of the input strings x, y, or z has a comma
in it?  How can I escape the comma so that the stored procedure will
still see a text array of three elements?

Thanks!

Eli Bingham
SavageBeast Technologies

On Thu, 16 Dec 2004, Eli Bingham wrote:

> CallableStatement stmt = conn.prepareCall ("{ ? = call do_stuff
> (?::text[]) }";
> stmt.registerOutParameter (1, Types.INTEGER);
> stmt.setObject (2, "{x, y, z}")
>
> But what can I do when one of the input strings x, y, or z has a comma
> in it?  How can I escape the comma so that the stored procedure will
> still see a text array of three elements?

You can either use the ARRAY constructor:

jurka=# SELECT ARRAY['x, ', 'y', 'z'];
    array
-------------
 {"x, ",y,z}

or notice how the above added the quotes around the first value, this is
also legal input syntax:

jurka=# SELECT '{"x, ",y,z}::text[];
    text
-------------
 {"x, ",y,z}

This natural leads to how do you escape quotes?  Which is answered by:

jurka=# SELECT ARRAY['"','a'];
    array
------------
 {"\"",a}


For all the details, see:

http://www.postgresql.org/docs/7.4/static/arrays.html#AEN5261

Kris Jurka