Howto retrieve a custom type via JDBC - Mailing list pgsql-jdbc

Hi,

I've created a custom datatype that I would like to retrieve via JDBC.
To make the problem more fun, the values are aggregated into an array.

The type definition is:

  CREATE TYPE annotation AS ( namespace varchar, name varchar, value varchar );

The query looks something like this:

  SELECT
    node_ref AS id,
    array_agg(DISTINCT ROW(namespace, name, value)::annotation) AS annotation
  ...
  GROUP BY id;

I've hacked together some experimental code:

  Array array = rs.getArray(2);
  if (array != null) {
    ResultSet arrayRs = array.getResultSet();
    while (arrayRs.next()) {
      PGobject anno = (PGobject) arrayRs.getObject("value");
      // anno.getValue() returns the String "(namespace,name,value)"
      PGtokenizer t = new PGtokenizer(PGtokenizer.remove(anno.getValue(), "(", ")"), ',');
      if (t.getSize() != 3)
        throw new RuntimeException("Could not read to annotation type: " + anno.getValue());
      String namespace = t.getToken(0);
      String name = t.getToken(1);
      String value = t.getToken(2);
      Annotation annotation = new Annotation(namespace, name, value);
    }
  }

The code above is not only fugly, but it breaks if there's a comma in
one of those values (e.g. (tiger,lemma,"1,62")).  In this case, they are
4 tokens.  BTW, the quotes around 1,62 are inserted by PostgreSQL,
they're not actually there in database.  If PostgreSQL is smart enough
to insert the quotes, it should be able to correctly parse the string,
shouldn't it?

I got the idea to use PGtokenizer from looking at PGbox and stuff.  But
my reason to create a custom type in the first place was to get to its
constituents in a clean way.  Previously, I had simply concatenated the
strings with ":" and "=" in SQL and splitted them back into the
components in my Java code.

So, the question is: How to cleanly retrieve the components of a custom
datatype using JDBC?

Cheers,
Viktor