On 17/01/11 17:27, Robert Haas wrote:
On Wed, Jan 12, 2011 at 5:12 AM, rsmogura<rsmog...@softperience.eu> wrote:
Dear hackers :) Could you look at this thread from General.
---
I say the backend if you have one "row type" output result treats it as the
full output result, it's really bad if you use STRUCT types (in your example
you see few columns, but this should be one column!). I think backend should
return ROWDESC(1), then per row data describe this row type data. In other
words result should be as in my example but without last column. Because
this funny behaviour is visible in psql in JDBC I think it's backend problem
or some far inconsistency. I don't see this described in select statement.
I've read this report over a few times now, and I'm still not
understanding exactly what is happening that you're unhappy about.
If I understand it correctly, the problem is this:
Given the schema and data from the OP
(summary:
t_author is a TABLE
t_author.address is of type u_address_type
u_address_type is a TYPE with fields: street, zip, city, country, since,
code
u_address_type.street is of type u_street_type
u_street_type is a TYPE with fields: street, no)
A bare SELECT works as expected:
test_udt=# SELECT t_author.address FROM t_author WHERE first_name = 'George';
address
-------------------------------------------------------------------
("(""Parliament Hill"",77)",NW31A9,Hampstead,England,1980-01-01,)
(1 row)
However, doing the same via a plpgsql function with an OUT parameter
produces something completely mangled:
test_udt=# 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;
CREATE FUNCTION
test_udt=# SELECT * FROM p_enhance_address2();
street | zip | city | country | since | code
-------------------------------------+-----+------+---------+-------+------
("(""Parliament Hill"",77)",NW31A9) | | | | |
(1 row)
Here, we've somehow got the first two fields of u_address_type - street
and zip - squashed together into one column named 'street', and all the
other columns nulled out.
Unsurprisingly the JDBC driver produces confusing results when faced
with this, so it was originally reported as a JDBC problem, but the
underlying problem can be seen via psql too.
Oliver
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
