Re: [HACKERS] Fwd: Weird issues when reading UDT from stored function

On 17/01/11 17:27, Robert Haas wrote:
> On Wed, Jan 12, 2011 at 5:12 AM, rsmogura<rsmogura(at)softperience(dot)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