Weird issues when reading UDT from stored function - Mailing list pgsql-jdbc

From Lukas Eder
Subject Weird issues when reading UDT from stored function
Date
Msg-id c63c0e3d-cd3b-4b82-b891-50f8ef43bfdb@j25g2000vbs.googlegroups.com
Whole thread Raw
Responses Re: Weird issues when reading UDT from stored function
List pgsql-jdbc
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(?) }"); // 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:
# 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:

# 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?

pgsql-jdbc by date:

Previous
From: Lukas Eder
Date:
Subject: Re: Support for SQLInput / SQLOutput
Next
From: Oliver Jowett
Date:
Subject: Re: Weird issues when reading UDT from stored function