Re: Fwd: Weird issues when reading UDT from stored function
От | Radosław Smogura |
---|---|
Тема | Re: Fwd: Weird issues when reading UDT from stored function |
Дата | |
Msg-id | 201101111946.33083.rsmogura@softperience.eu обсуждение исходный текст |
Ответ на | Fwd: 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 |
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.eder@gmail.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.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(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" > ===================================
В списке pgsql-jdbc по дате отправления:
Следующее
От: Lukas EderДата:
Сообщение: Re: Fwd: Weird issues when reading UDT from stored function