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