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.postgresql.dri...@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.e...@gmail.com
[2] mailto:rsmog...@softperience.eu
