From: | Lukas Eder <lukas(dot)eder(at)gmail(dot)com> |
---|---|
To: | pgsql-jdbc(at)postgresql(dot)org |
Subject: | Weird issues when reading UDT from stored function |
Date: | 2011-01-10 23:06:31 |
Message-ID: | c63c0e3d-cd3b-4b82-b891-50f8ef43bfdb@j25g2000vbs.googlegroups.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-hackers 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?
From | Date | Subject | |
---|---|---|---|
Next Message | Hannu Krosing | 2011-01-10 23:21:28 | Re: pl/python custom exceptions for SPI |
Previous Message | Joel Jacobson | 2011-01-10 22:59:48 | Add function dependencies |
From | Date | Subject | |
---|---|---|---|
Next Message | Lukas Eder | 2011-01-10 23:20:52 | Re: Support for SQLInput / SQLOutput |
Previous Message | Radosław Smogura | 2011-01-07 18:49:04 | Re: Strange behaviour of rs.getTimestamp |