Re: [7.4.6] Escaping strings in text arrays passed through

From: Kris Jurka <books(at)ejurka(dot)com>
To: Eli Bingham <eli(at)savagebeast(dot)com>
Cc: pgsql-jdbc(at)postgresql(dot)org
Subject: Re: [7.4.6] Escaping strings in text arrays passed through
Date: 2004-12-17 02:57:01
Message-ID: Pine.BSO.4.56.0412162147330.8739@leary.csoft.net
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-jdbc

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

In response to

Browse pgsql-jdbc by date

  From Date Subject
Next Message Guillaume Cottenceau 2004-12-17 09:43:29 NPE sent by driver when ResultSet's connection is closed, proposed patch
Previous Message Eli Bingham 2004-12-17 02:35:23 [7.4.6] Escaping strings in text arrays passed through JDBC?