Skip site navigation (1) Skip section navigation (2)

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

From: Robert Haas <robertmhaas(at)gmail(dot)com>
To: Oliver Jowett <oliver(at)opencloud(dot)com>
Cc: rsmogura <rsmogura(at)softperience(dot)eu>, Lukas Eder <lukas(dot)eder(at)gmail(dot)com>, pgsql-jdbc(at)postgresql(dot)org, pgsql-hackers(at)postgresql(dot)org
Subject: Re: [HACKERS] Fwd: Weird issues when reading UDT from stored function
Date: 2011-01-17 13:15:04
Message-ID: AANLkTin_UwaAjVKOqY3ZGpHtEt4WiX8f-ERJhA16JWX+@mail.gmail.com (view raw or flat)
Thread:
Lists: pgsql-hackerspgsql-jdbc
On Mon, Jan 17, 2011 at 12:00 AM, Oliver Jowett <oliver(at)opencloud(dot)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

In response to

Responses

pgsql-hackers by date

Next:From: Shigeru HANADADate: 2011-01-17 13:17:22
Subject: Re: review: FDW API
Previous:From: Shigeru HANADADate: 2011-01-17 13:13:19
Subject: Re: review: FDW API

pgsql-jdbc by date

Next:From: Satish Burnwal (sburnwal)Date: 2011-01-17 21:38:16
Subject: ERROR: transaction is read-only
Previous:From: Oliver JowettDate: 2011-01-17 05:00:48
Subject: Re: [HACKERS] Fwd: Weird issues when reading UDT from stored function

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group