Re: Fwd: Weird issues when reading UDT from stored function

Dear hackers :) Could you look at this thread from General.
---
I say the backend if you have one "row type" output result treats it as
the full output result, it's really bad if you use STRUCT types (in your
example you see few columns, but this should be one column!). I think
backend should return ROWDESC(1), then per row data describe this row
type data. In other words result should be as in my example but without
last column. Because this funny behaviour is visible in psql in JDBC I
think it's backend problem or some far inconsistency. I don't see this
described in select statement.

Kind regards,
Radek

On Tue, 11 Jan 2011 23:54:19 +0100, Lukas Eder wrote:
> Hmm, you're right, the result seems slightly different. But still the
> UDT record is not completely fetched as if it were selected directly
> from T_AUTHOR in a PreparedStatement...
>
> 2011/1/11 Radosław Smogura
>
>> I've done:
>> test=# CREATE FUNCTION p_enhance_address3 (address OUT
>> u_address_type, i1 OUT
>> int)
>>
>> AS $$
>> BEGIN
>>        SELECT t_author.address
>>        INTO address
>>        FROM t_author
>>        WHERE first_name = 'George';
>> i1 = 12;
>> END;
>> $$ LANGUAGE plpgsql;
>> test=# select *
>> from p_enhance_address3();
>>                      address                  
>>     | i1
>> ----------------------------------------------------+----
>>  ("(""(""""Parliament Hill"""",77)"",NW31A9)",,,,,) | 12
>> (1 row)
>>
>> Result is ok. Because UDT is described in same way as row, it's
>> looks like
>> that backand do this nasty thing and instead of 1 column, it sends
>> 6 in your
>> case.
>>
>> Forward to hackers. Maybe they will say something, because I don;t
>> see this in
>> docs.
>>
>> Radek
>> Lukas Eder Tuesday 11 January 2011 16:55:52
>>
>>> > Looks to me like you're getting each field of the UDT as a
>> separate
>> > > column. You printed only the first column i.e. the 'street'
>> part.
>> >
>> > Exactly, that's what I'm getting
>> >
>> >
>> > It might be informative to run with loglevel=2 and see how the
>> server is
>> >
>> > > returning results. If the driver is reporting 6 columns, that
>> means that
>> > > the server is reporting 6 fields in its RowDescription message.
>> >
>> > Here's what I get (there really is a RowDescription(6)):
>> >
>> > ===================================
>> > 08:15:44.914 (1) PostgreSQL 9.0 JDBC4 (build 801)
>> > 08:15:44.923 (1) Trying to establish a protocol version 3
>> connection to
>> > localhost:5432
>> > 08:15:44.941 (1)  FE=> StartupPacket(user=postgres,
>> database=postgres,
>> > client_encoding=UNICODE, DateStyle=ISO, extra_float_digits=2)
>> > 08:15:44.962 (1)   08:15:44.968 (1)  FE=>
>> > Password(md5digest=md5ea57d63c7d2afaed5abb3f0bb88ae7b8)
>> > 08:15:44.970 (1)   08:15:44.980 (1)   08:15:44.980 (1)  
>> 08:15:44.980 (1)   08:15:44.980 (1)   08:15:44.981 (1)  
>> 08:15:44.981 (1)   08:15:44.981 (1)   08:15:44.981 (1)  
>> 08:15:44.981 (1)   08:15:44.981 (1)   08:15:44.981 (1)  
>> 08:15:44.981 (1)   08:15:44.981 (1)   08:15:44.981 (1)    
>> compatible = 9.0
>> > 08:15:44.981 (1)     loglevel = 2
>> > 08:15:44.981 (1)     prepare threshold = 5
>> > getConnection returning
>> >
>>
>
> driver[className=org.postgresql.Driver,org(dot)postgresql(dot)Driver(at)77ce3fc5]
>> > 08:15:45,021        DEBUG [org.jooq.impl.StoredProcedureImpl
>> > ] - Executing query : { call public.p_enhance_address2(?) }
>> > 08:15:45.035 (1) simple execute,
>> >
>>
>
> handler=org.postgresql.jdbc2.AbstractJdbc2Statement$StatementResultHandler@
>> > 2eda2cef, maxRows=0, fetchSize=0, flags=17
>> > 08:15:45.036 (1)  FE=> Parse(stmt=null,query="select * from
>> > public.p_enhance_address2()  as result",oids={2278})
>> > 08:15:45.037 (1)  FE=> Bind(stmt=null,portal=null,=)
>> > 08:15:45.038 (1)  FE=> Describe(portal=null)
>> > 08:15:45.038 (1)  FE=> Execute(portal=null,limit=0)
>> > 08:15:45.038 (1)  FE=> Sync
>> > 08:15:45.043 (1)   08:15:45.044 (1)   08:15:45.045 (1)  
>> 08:15:45.046 (1)   08:15:45.046 (1)   08:15:45.062 (1)  
>> org.postgresql.util.PSQLException: Ein CallableStatement wurde mit
>> einer
>> > falschen Anzahl Parameter ausgeführt.
>> >     at
>> >
>>
>
> org.postgresql.jdbc2.AbstractJdbc2Statement.executeWithFlags(AbstractJdbc2S
>> > tatement.java:408) at
>> >
>>
>
> org.postgresql.jdbc2.AbstractJdbc2Statement.execute(AbstractJdbc2Statement.
>> > java:381) at
>> >
>>
>
> org.jooq.impl.StoredProcedureImpl.execute(StoredProcedureImpl.java:125)
>> >     at
>> >
>>
>
> org.jooq.test.postgres.generatedclasses.Procedures.pEnhanceAddress2(Procedu
>> > res.java:91) [...]
>> > SQLException: SQLState(42601)
>> > 08:15:45.074 (1)  FE=> Terminate
>> > ===================================
>> >
>> >
>> > Oops, looking closer I see what you mean, that's actually 2
>> columns of the
>> >
>> > > surrounding type - street + zip?
>> >
>> > Yes, exactly. Somehow the driver stops at the second type element
>> of the
>> > surrounding type. This may be correlated to the fact that the
>> inner type
>> > has exactly 2 elements?
>> >
>> > > What are the values of the other 5 columns reported by the
>> driver?
>> >
>> > The other 5 columns are reported as null (always).
>> > In pgAdmin III, I correctly get a single column in the result
>> set. Also,
>> > the postgres information_schema only holds one parameter:
>> >
>> > ===================================
>> > select parameter_mode, parameter_name, udt_name
>> > from information_schema.parameters
>> > where specific_name like 'p_enhance_address2%'
>> >
>> > yields:
>> >
>> > "OUT";"address";"u_address_type"
>> > ===================================
>
>
>
> Links:
> ------
> [1] mailto:lukas(dot)eder(at)gmail(dot)com
> [2] mailto:rsmogura(at)softperience(dot)eu