Google Groups no longer supports new Usenet posts or subscriptions. Historical content remains viewable.
Dismiss

implicit cast of bytea data to String (postgresql 9.2.10)

682 views
Skip to first unread message

Peter.C

unread,
Apr 17, 2015, 9:55:26 PM4/17/15
to
I'd like to get PreparedStatement.setString() to automatically save a Java String as UTF-8 bytes into a bytea field and ResultSet.getString() to automatically convert the bytea bytes as UTF-8 into a Java String.

I have the PreparedStatement.setString() assignment cast working:
create function bytea(text) returns bytea as
$$
select convert_to($1, 'UTF8');
$$
language sql strict;

create cast (text as bytea)
with function bytea(text)
as assignment;

create function bytea(character varying) returns bytea as
$$
select convert_to(CAST ($1 as text), 'UTF8');
$$
language sql strict;

create cast (character varying as bytea)
with function bytea(character varying)
as assignment;


I tried something similar with ResultSet.getString() but it does not work:
create function text(bytea) returns text as
$$
select convert_from($1, 'UTF8');
$$
language sql strict;

create cast (bytea as text)
with function text(bytea)
as assignment;

create function "varchar"(bytea) returns character varying as
$$
select cast(convert_from($1, 'UTF8') as character varying);
$$
language sql strict;

create cast (bytea as character varying)
with function "varchar"(bytea)
as assignment;

From JDBC I call ResultSet.getString(1) on a bytea field and I get something like:
\x736574537472696e672829

Using getBytes() and using Java to convert shows the data is good:
System.out.println("-------------------------From getString():");
System.out.println(rs.getString(1));

byte[] bytes = rs.getBytes(1);
String isStr = new String(bytes, Charset.forName("UTF-8"));
System.out.println("-------------------------From getBytes():");
System.out.println(isStr);

-------------------------From getString():
\x736574537472696e672829
-------------------------From getBytes():
setString()


Is there a way to get this to work?
0 new messages