Обсуждение: arrays of composite types, and client drivers like JDBC

Поиск
Список
Период
Сортировка

arrays of composite types, and client drivers like JDBC

От
Rob Nikander
Дата:
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


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

От
Dave Cramer
Дата:


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



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 ?

  

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

От
Rob Nikander
Дата:

> 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
theparsing isn't that difficult, so I wrote something that seems to work. Probably that code should be in the library,
notmy 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