Обсуждение: ResultSetMetaData getColumnTypeName()

Hi,
I would like to discuss an issue ResultSetMetaData.getColumnTypeName().
Within current implementation of PostgreSQL JDBC getColumnTypeName()
returns only locale name of a type, even if it is a user-defined type.
The JDBC docs says:
"...returns type name used by the database. If the column type is a
user-defined type, then a fully-qualified type name is returned."

It means, that any other type that is not defined within pg_catalog
should be returned with its schema name, so e.g. type my_price in schema
blabla should be returned as "blabla.my_price". It's quite simple to fix
it and I'm ready to do it, but before that I would like to ask about
your opinion about changing this behavior. I found this problem when I
needed to translate user-defined types to Java and without schema I
cannot be sure, that a type is the one that I want to translate (cause
there can be many types with the same local name).

I would be appreciated for your comments.

Ps. Kris - if we will accept, that getColumnTypeName() should return
qualified name, then that will cause small changes within
org.postgresql.jdbc2.TypeInfoCache class and I would like to know what
about my previous changes within this class that are related to array types.

Best wishes,
Marek

On Wed, 21 Nov 2007, Marek Lewczuk wrote:

> I would like to discuss an issue ResultSetMetaData.getColumnTypeName().
> Within current implementation of PostgreSQL JDBC getColumnTypeName()
> returns only locale name of a type, even if it is a user-defined type.
> The JDBC docs says: "...returns type name used by the database. If the
> column type is a user-defined type, then a fully-qualified type name is
> returned."
>
> It means, that any other type that is not defined within pg_catalog
> should be returned with its schema name, so e.g. type my_price in schema
> blabla should be returned as "blabla.my_price". It's quite simple to fix
> it and I'm ready to do it, but before that I would like to ask about
> your opinion about changing this behavior. I found this problem when I
> needed to translate user-defined types to Java and without schema I
> cannot be sure, that a type is the one that I want to translate (cause
> there can be many types with the same local name).
>

Currently the driver implements DatabaseMetaData.getUDTs by considering
only complex types and domains as UDTs and the javadoc for that method
seems to imply that's correct.  Perhaps the JDBC spec isn't prepared for
the extensibility that pg offers, but you'll need to reconcile these two
definitions if you want to qualify all types outside pg_catalog.

Could explain in more detail how you're mapping these UDTs to Java?  Using
PGobject?  I'm not sure how you'd map without an implemention of
SQLData/Input/Output.

My recollection was that making all type operations in the driver schema
qualified was more work that a simple change to TypeInfoCache, but I don't
recall the details, so I'd like to see what you come up with.  While you
wouldn't want to report the pg_catalog parts of base types you would need
a way to ensure that if you had myschema.int4 it wouldn't conflict.

Kris Jurka

Kris Jurka pisze:
>
> Currently the driver implements DatabaseMetaData.getUDTs by considering
> only complex types and domains as UDTs and the javadoc for that method
> seems to imply that's correct.  Perhaps the JDBC spec isn't prepared for
> the extensibility that pg offers, but you'll need to reconcile these two
> definitions if you want to qualify all types outside pg_catalog.
 From my point of view getUDTs is working just fine.

> My recollection was that making all type operations in the driver schema
> qualified was more work that a simple change to TypeInfoCache, but I
> don't recall the details, so I'd like to see what you come up with.
> While you wouldn't want to report the pg_catalog parts of base types you
> would need a way to ensure that if you had myschema.int4 it wouldn't
> conflict.
Maybe I was to optimistic that is so easy to implement this, but it is
not so hard too. The main change is to use qualified names within
TypeCacheInfo everywhere it is possible, that means that even basic
types should be presented with its schema (pg_catalog). Of course that
change will force other changes especially in TypeCacheInfo internal sql
queries and other places where type name is taken/provided. With this
changes ResultSetMetaData.getColumnTypeName() will return local name for
basic types (that is a type that qualified name's
startsWith("pg_catalog")) and qualified name for all other types.

> Could explain in more detail how you're mapping these UDTs to Java?
> Using PGobject?  I'm not sure how you'd map without an implemention of
> SQLData/Input/Output.
I have made a wrapper for JDBC classes, that override default implementation
of JDBC driver and when needed (ResultSet.getObject()) it translates sql
types to Java types and vice-versa (PreparedStatement.setObject()). My
implementation works only for composite types, cause composite type have
same structure of string representation.

Best wishes,
ML