2011/2/17 Florian Pflug
<fgp@phlo.org>On Feb17, 2011, at 01:14 , Oliver Jowett wrote:
> Any suggestions about how the JDBC driver can express the query to get
> the behavior that it wants? Specifically, the driver wants to call a
> particular function with N OUT or INOUT parameters (and maybe some other
> IN parameters too) and get a resultset with N columns back.
There's no sane way to do that, I fear. You could of course look up the
function definition in the catalog before actually calling it, but with
overloading and polymorphic types finding the right pg_proc entry seems
awfully complex.
Your best option is probably to just document this caveat...
But there still is a bug in the JDBC driver as I originally documented it. Even if you say it's not simple to know whether the signature is actually a single UDT with 6 attributes or just 6 OUT parameters, the result is wrong (as stated in my original mail):
The nested UDT structure completely screws up fetching results. This
is what I get with JDBC:
====================================
PreparedStatement stmt = connection.prepareStatement("select *
from p_enhance_address2()");
ResultSet rs = stmt.executeQuery();
while (rs.next()) {
System.out.println("# of columns: " +
rs.getMetaData().getColumnCount());
System.out.println(rs.getObject(1));
}
====================================
Output:
# of columns: 6
("(""Parliament Hill"",77)",NW31A9)
The result set meta data correctly state that there are 6 OUT columns. But only the first 2 are actually fetched (because of a nested UDT)...