Thread: Finding exact column type via java.sql.ResultSetMetaData

Hello,

I'm scratching my head over ResultSetMetaData yet again, this time related to figuring out the correct type of a
column.

Am I right in understanding that given a SQL command whose resultset contains a field of a user defined type not
originatingfrom a table, you cannot look up the exact type for that field via the result set meta data if multiple
typesof the same name are defined in different schemas? AFAICT ResultSetMetaData only gives the name the type is
definedwith in the database, via getColumnTypeName, but not the schema of that type. Should the result for
getColumnTypeNamehave been prefixed with schema-plus-dot, or is that not possible or not according to the spec? Unless
I'mmissing something the code I'm writing will basically have to throw an exception and apologize that different types
withthe same name in different schemas is not supported when it runs into that. 


I'm testing with:

CREATE SCHEMA test_schema;
CREATE SCHEMA another_test_schema;

CREATE TYPE test_schema.test_type AS ( intfield int, textfield varchar );
CREATE TYPE another_test_schema.test_type AS ( other_textfield varchar, other_intfield int );

And given the query:
SELECT (1,'adsf')::test_schema.test_type, ('zxcv',2)::another_test_schema.test_type;

Then java.sql.ResultSetMetaData gives the exact same information for each column, with getColumnTypeName giving
"test_type"for both. Simple test-case with this and a bit of runnable Java included. 

Thanks,
Thor Michael Støre

On Thu, 14 Feb 2013, Dave Cramer wrote:

> Yes, you are correct.  The backend does not provide that information
>

No, the backend does provide the OID of the returned column type.  So
internally we can tell the difference between the two types in different
schemas.  The issue is how to return this information to the user.
ResultSetMetaData.getColumnTypeName doesn't have a corresponding
getColumnTypeSchema so we'd have to qualify the returned value.  The
javadoc says we should in certain circumstances:

Returns:
    type name used by the database. If the column type is a user-defined
type, then a fully-qualified type name is returned.

I'm concerned that doing this would break things for more users than it
would help because different types with the same name is a rarity.  So
we'd probably need an option to enable/disable this.

Kris Jurka