On Mon, Jan 17, 2011 at 12:00 AM, Oliver Jowett <oli...@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 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers