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

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 <rsmogura(at)softperience(dot)eu>

> 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 <lukas(dot)eder(at)gmail(dot)com> 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) <=BE AuthenticationReqMD5(salt=335c1a87)
> > 08:15:44.968 (1) FE=>
> > Password(md5digest=md5ea57d63c7d2afaed5abb3f0bb88ae7b8)
> > 08:15:44.970 (1) <=BE AuthenticationOk
> > 08:15:44.980 (1) <=BE ParameterStatus(application_name = )
> > 08:15:44.980 (1) <=BE ParameterStatus(client_encoding = UNICODE)
> > 08:15:44.980 (1) <=BE ParameterStatus(DateStyle = ISO, DMY)
> > 08:15:44.980 (1) <=BE ParameterStatus(integer_datetimes = on)
> > 08:15:44.981 (1) <=BE ParameterStatus(IntervalStyle = postgres)
> > 08:15:44.981 (1) <=BE ParameterStatus(is_superuser = on)
> > 08:15:44.981 (1) <=BE ParameterStatus(server_encoding = UTF8)
> > 08:15:44.981 (1) <=BE ParameterStatus(server_version = 9.0.1)
> > 08:15:44.981 (1) <=BE ParameterStatus(session_authorization = postgres)
> > 08:15:44.981 (1) <=BE ParameterStatus(standard_conforming_strings = off)
> > 08:15:44.981 (1) <=BE ParameterStatus(TimeZone = CET)
> > 08:15:44.981 (1) <=BE BackendKeyData(pid=2980,ckey=465709852)
> > 08:15:44.981 (1) <=BE ReadyForQuery(I)
> > 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($1) as result",oids={2278})
> > 08:15:45.037 (1) FE=> Bind(stmt=null,portal=null,$1=<'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) <=BE ParseComplete [null]
> > 08:15:45.044 (1) <=BE BindComplete [null]
> > 08:15:45.045 (1) <=BE RowDescription(6)
> > 08:15:45.046 (1) <=BE DataRow
> > 08:15:45.046 (1) <=BE CommandStatus(SELECT 1)
> > 08:15:45.062 (1) <=BE ReadyForQuery(I)
> > 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"
> > ===================================
>