Re: arrays of composite types, and client drivers like JDBC

On Sat, 15 Jun 2019 at 02:33, Rob Nikander <rob(dot)nikander(at)gmail(dot)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(at)postgresintl(dot)com
www.postgresintl.com