PreparedStatement doesn't get parameter types from PostgreSQL

[jcflack]

In most cases where type mapping is done between PostgreSQL and Java types (function parameters and return values when functions are called, methods on ResultSet), the PostgreSQL type is known, and so the mapping is straightforward. ResultSetMetaData, for example, can tell you the exact type of the column according to PostgreSQL, via getColumnTypeName.

A PreparedStatement, however, does not currently collect the inferred types that PostgreSQL assigned for parameters while parsing the query. The necessary capability was not added to SPI until PostgreSQL 9.0, well after PL/Java's current behavior was set, and even the SPI additions in 9.0 seem a bit of a minefield to use (this message et seq.).

That all seems a bit weird as the corresponding ability has been available to clients through the v3 "extended query protocol" since PostgreSQL 7.4. Took a long time for SPI to catch up.

In any case, PL/Java's current behavior when binding parameters to a PreparedStatement is to begin as if there is no information about the PostgreSQL types of the parameters, and make a best effort to assign those types based on the Java types and values supplied to the set... methods. Accordingly, the current behavior of getParameterMetaData is to default to character varying for any parameter that has not been assigned yet, or otherwise to report the type that was guessed from the binding.

The long-term solution will be to have PL/Java use the PostgreSQL 9.0 API and retrieve the types assigned to the parameters by PostgreSQL's parsing and type inference.

The short term requires some workarounds for current cases where the best-effort guessing based on the Java value falls short. These are the cases where the mapping isn't a clear, well-known one between a single PG type and a single Java type.

The case of UDTs (Java types that implement SQLData) can be easily improved, just by making use of the getSQLTypeName method the SQLData interface already provides. UDTs have always had to implement it, but so far, PL/Java hasn't made use of it.

PL/Java also does not completely implement the three-argument form of setNull, with which a type name specific to the underlying database can be explicitly supplied. At present, the type name parameter is just ignored, and the method behaves like the two-argument form. With this method fully implemented, situations where the best-effort mapping is not finding the right PostgreSQL type could be worked around by first explicitly setting null with the correct type, then setting the desired value. The implementation of this setNull would require reorganizing some of the type checks done in setObject, to behave more in line with other type mapping cases, where the underlying PG type is authoritative and the JDBC and Java types determined by it, rather than the other way around. So that will take a bit more work, but it will be necessary work for the eventual full solution using PostgreSQL's assigned types, anyway.