How to read a UDT from a postgres stored function

Question

I can't seem to read a UDT properly from a stored function with the postgres JDBC driver. This is some sample code:

CREATE TYPE u_country AS ENUM ('Brazil', 'England', 'Germany')

CREATE TYPE u_street_type AS (
  street VARCHAR(100),
  no VARCHAR(30)
)

CREATE TYPE u_address_type AS (
  street u_street_type,
  zip VARCHAR(50),
  city VARCHAR(50),
  country u_country,
  since DATE,
  code INTEGER
)

CREATE TABLE t_author (
  id INTEGER NOT NULL PRIMARY KEY,
  first_name VARCHAR(50),
  last_name VARCHAR(50) NOT NULL,
  date_of_birth DATE,
  year_of_birth INTEGER,
  address u_address_type
)

INSERT INTO t_author VALUES (1, 'George', 'Orwell',
TO_DATE('1903-06-25', 'YYYY-MM-DD'), 1903, ROW(ROW('Parliament Hill',
'77'), 'NW31A9', 'Hampstead', 'England', '1980-01-01', null))
INSERT INTO t_author VALUES (2, 'Paulo', 'Coelho',
TO_DATE('1947-08-24', 'YYYY-MM-DD'), 1947, ROW(ROW('Caixa Postal',
'43.003'), null, 'Rio de Janeiro', 'Brazil', '1940-01-01', 2))

CREATE FUNCTION p_enhance_address2 (address OUT u_address_type)
AS $$
BEGIN
        SELECT t_author.address
        INTO address
        FROM t_author
        WHERE first_name = 'George';
END;
$$ LANGUAGE plpgsql;

Now the above works perfectly in postgres. I can also select the UDT column t_author.address with a SQL SELECT statement directly. But when I select from the stored function p_enhance_address2 via JDBC, I get a weird behaviour. I tried these two invocation schemes:

connection.prepareStatement("select * from p_enhance_address2()");
connection.prepareCall("{ call p_enhance_address2(?) }"); 
// the latter with an output parameter registered

Both calling schemes induce the same behaviour (actually the CallableStatement is nothing else than selecting from the function). There seem to be two very distinct problems:

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:

nr of columns: 6 ("(""Parliament Hill"",77)",NW31A9)

Why are there 6 columns? And why is the UDT incorrectly fetched (many fields are missing)

A little improvement can be achieved, when the nested UDT u_street_type is "flattened" to a varchar, which leads to the assumption that nested UDT's are poorly supported by the JDBC driver:

CREATE TYPE u_address_type AS (
  street VARCHAR(80),
  zip VARCHAR(50),
  city VARCHAR(50),
  country u_country,
  since DATE,
  code INTEGER
)

INSERT INTO t_author VALUES (1, 'George', 'Orwell',
TO_DATE('1903-06-25', 'YYYY-MM-DD'), 1903, ROW('Parliament Hill 77',
'NW31A9', 'Hampstead', 'England', '1980-01-01', null))
INSERT INTO t_author VALUES (2, 'Paulo', 'Coelho',
TO_DATE('1947-08-24', 'YYYY-MM-DD'), 1947, ROW('Caixa Postal 43.003',
null, 'Rio de Janeiro', 'Brazil', '1940-01-01', 2))

Then the results will be something like this:

nr of columns: 6 ("Parliament Hill 77",NW31A9,Hampstead,England,1980-01-01,)

The UDT record now looks correct (fetched from the result set at position 1). But there are still 6 columns in the result set.

Some facts:

• I do not experience these problems in pgAdmin III
• I use PostgreSQL 9.0.1, compiled by Visual C++ build 1500, 64-bit
• I use postgresql-9.0-801.jdbc4.jar

Does anyone have any idea what's wrong?

Answer 1

I can reproduce this and it seems that this is a bug.

I would suggest you post this to the PostgreSQL JDBC mailing list, so the developers can fix this.