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

On Mon, Jan 17, 2011 at 12:00 AM, Oliver Jowett <oliver@opencloud.com> wrote:
> 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.

I think this is the old problem of PL/pgsql having two forms of SELECT
INTO.  You can either say:

SELECT col1, col2, col3, ... INTO recordvar FROM ...

Or you can say:

SELECT col1, col2, col3, ... INTO nonrecordvar1, nonrecordvar2,
nonrecordvar3, ... FROM ...

In this case, since address is a recordvar, it's expecting the first
form - thus the first select-list item gets matched to the first
column of the address, rather than to address as a whole.  It's not
smart enough to consider the types of the items involved - only
whether they are records.  :-(

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company