Re: [HACKERS] Fwd: Weird issues when reading UDT from stored function - Mailing list pgsql-jdbc

On 18/02/11 00:52, rsmogura wrote:
> On Fri, 18 Feb 2011 00:44:07 +1300, Oliver Jowett wrote:
>> On 18/02/11 00:37, rsmogura wrote:
>>> On Fri, 18 Feb 2011 00:06:22 +1300, Oliver Jowett wrote:
>>>> On 17/02/11 23:18, rsmogura wrote:
>>>>> Yes, but driver checks number of declared out parameters and number of
>>>>> resulted parameters (even check types of those), to prevent
>>>>> programming
>>>>> errors.
>>>>
>>>> And..?
>>>>
>>>> Oliver
>>>
>>> And it will throw exception when result will income. If you will remove
>>> this then you will lose check against programming errors, when number of
>>> expected parameters is different that number of actual parameters. Bear
>>> in mind that you will get result set of 6 columns, but only 1 is
>>> expected. I think you can't determine what should be returned and how to
>>> fix result without signature.
>>
>> You've completely missed the point. I am not suggesting we change those
>> checks at all. I am suggesting we change how the JDBC driver translates
>> call escapes to queries so that for N OUT parameters, we always get
>> exactly N result columns, without depending on the datatypes of the
>> parameters in any way.
>>
>> Oliver
>
> May You provide example select for this, and check behaviour with below
> procedure, too.
>
> CREATE OR REPLACE FUNCTION p_enhance_address3(OUT address
> u_address_type, OUT i1 integer)
>   RETURNS record AS
> $BODY$
> BEGIN
>         SELECT t_author.address
>         INTO address
>         FROM t_author
>         WHERE first_name = 'George';
> i1 = 12;
> END;
> $BODY$
>   LANGUAGE plpgsql

Oh god I'm going round and round in circles repeating myself!

There are two problems.

The first problem is a plpgsql problem in that particular function. It's
broken regardless of how you call it. Here's how to fix it:

> testdb=# CREATE FUNCTION p_enhance_address4 (address OUT u_address_type) AS $$ BEGIN address := (SELECT
t_author.addressFROM t_author WHERE first_name = 'George'); END; $$ LANGUAGE plpgsql; 
> CREATE FUNCTION
> testdb=# SELECT * FROM p_enhance_address4();
>          street         |  zip   |   city    | country |   since    | code
> ------------------------+--------+-----------+---------+------------+------
>  ("Parliament Hill",77) | NW31A9 | Hampstead | England | 1980-01-01 |
> (1 row)

The second problem is that the JDBC driver always generates calls in the
"SELECT * FROM ..." form, but this does not work correctly for
one-OUT-parameter-that-is-a-UDT, as seen in the example immediately
above. Here's how to do the call for that particular case:

> testdb=# SELECT p_enhance_address4();
>                         p_enhance_address4
> -------------------------------------------------------------------
>  ("(""Parliament Hill"",77)",NW31A9,Hampstead,England,1980-01-01,)
> (1 row)

The challenge is that the bare SELECT form doesn't work for multiple OUT
parameters, so the driver has to select one form or the other based on
the number of OUT parameters.

Any questions? (I'm sure there will be questions. Sigh.)

Oliver