Fwd: Weird issues when reading UDT from stored function

Поиск
Список
Период
Сортировка
От Lukas Eder
Тема Fwd: Weird issues when reading UDT from stored function
Дата
Msg-id AANLkTi=pTFGV+vVFgKwB6+ZNWb+r9LrYpJXKEytL5P02@mail.gmail.com
обсуждение исходный текст
Ответ на Weird issues when reading UDT from stored function  (Lukas Eder <lukas.eder@gmail.com>)
Ответы Re: Fwd: Weird issues when reading UDT from stored function
Список pgsql-jdbc
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.postgresql.Driver@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(AbstractJdbc2Statement.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(Procedures.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"
===================================

В списке pgsql-jdbc по дате отправления:

Предыдущее
От: Oliver Jowett
Дата:
Сообщение: Re: Weird issues when reading UDT from stored function
Следующее
От: Radosław Smogura
Дата:
Сообщение: Re: Fwd: Weird issues when reading UDT from stored function