arrays of composite types, and client drivers like JDBC

On Sat, 15 Jun 2019 at 02:33, Rob Nikander <rob.nikander@gmail.com> wrote:

Hi,

I'm experimenting with Java client libraries (the usual JDBC and some
other async projects, eg [1]). So far, I'm not finding ways to select/read
composite types without ugly string parsing. The simple cases are okay, but
if I have a column that is an array of composites, the client library might
give me a string for a column value like the following, with no builtin way
to parse it.

{"(10,\"abc \"\" \"\" \"\"\",\"2019-06-14
18:16:48.067969\",t)","(11,foo,\"2019-06-14 18:16:48.067969\",f)"}

Maybe I'm missing the part of the JDBC API that I can use here.

If not, then I'm wondering: is there something inherent in the underlying
PG protocol that makes this difficult for all these client/driver
libraries? In other words, maybe the protocol is sending strings meant for
display, not for parsing as data?

I was hoping I'd find an API like...

create type foo as (age int, color text);
create table t (a foo, b foo[]);
....
var resultSet = conn.prepareStatement("select a,b from
t").executeQuery()
var foos = resultSet.getArray("b")
var foo = foos.getElement(0)
var age = foo.getInt(1)
var color = foo.getString(2)

thanks,
Rob

[1]: https://github.com/cretz/pgnio

Basically because java would have to create a type dynamically to parse the
data into.
There's nothing inherently difficult about parsing the data, the problem is
what do we put it into ?

Dave Cramer

davec@postgresintl.com
www.postgresintl.com

On Jun 15, 2019, at 1:47 PM, Dave Cramer <pg@fastcrypt.com> wrote:

Basically because java would have to create a type dynamically to parse the data into.
There's nothing inherently difficult about parsing the data, the problem is what do we put it into ?

(I accidentally replied off-list, so resending this.)

It could parse it to strings and convert only when I call `getInt`, `getTimestamp`, etc. But, like you said, I see that the parsing isn't that difficult, so I wrote something that seems to work. Probably that code should be in the library, not my application. If I test this out some more and it still seems to work, I’ll ask again about contributing to https://github.com/pgjdbc/pgjdbc.

Rob