Thread

Weird issues when reading UDT from stored function

Lukas Eder <lukas.eder@gmail.com> — 2011-01-10T23:06:31Z

I can't seem to read a UDT properly from a stored function with the
postgres JDBC driver. This is some sample code:

====================================
CREATE TYPE u_country AS ENUM ('Brazil', 'England', 'Germany')

CREATE TYPE u_street_type AS (
  street VARCHAR(100),
  no VARCHAR(30)
)

CREATE TYPE u_address_type AS (
  street u_street_type,
  zip VARCHAR(50),
  city VARCHAR(50),
  country u_country,
  since DATE,
  code INTEGER
)

CREATE TABLE t_author (
  id INTEGER NOT NULL PRIMARY KEY,
  first_name VARCHAR(50),
  last_name VARCHAR(50) NOT NULL,
  date_of_birth DATE,
  year_of_birth INTEGER,
  address u_address_type
)

INSERT INTO t_author VALUES (1, 'George', 'Orwell',
TO_DATE('1903-06-25', 'YYYY-MM-DD'), 1903, ROW(ROW('Parliament Hill',
'77'), 'NW31A9', 'Hampstead', 'England', '1980-01-01', null))
INSERT INTO t_author VALUES (2, 'Paulo', 'Coelho',
TO_DATE('1947-08-24', 'YYYY-MM-DD'), 1947, ROW(ROW('Caixa Postal',
'43.003'), null, 'Rio de Janeiro', 'Brazil', '1940-01-01', 2))

CREATE FUNCTION p_enhance_address2 (address OUT u_address_type)
AS $$
BEGIN
	SELECT t_author.address
	INTO address
	FROM t_author
	WHERE first_name = 'George';
END;
$$ LANGUAGE plpgsql;
====================================

Now the above works perfectly in postgres. I can also select the UDT
column t_author.address with a SQL SELECT statement directly. But when
I select from the stored function p_enhance_address2 via JDBC, I get a
weird behaviour. I tried these two invocation schemes:

====================================
connection.prepareStatement("select * from p_enhance_address2()");
connection.prepareCall("{ call p_enhance_address2(?) }"); // with an
output parameter registered
====================================

Both calling schemes induce the same behaviour (actually the
CallableStatement is nothing else than selecting from the function).
There seem to be two very distinct problems:

The nested UDT structure completely screws up fetching results. This
is what I get with JDBC:
====================================
        PreparedStatement stmt = connection.prepareStatement("select *
from p_enhance_address2()");
        ResultSet rs = stmt.executeQuery();

        while (rs.next()) {
            System.out.println("# of columns: " +
rs.getMetaData().getColumnCount());
            System.out.println(rs.getObject(1));
        }
====================================
Output:
# of columns: 6
("(""Parliament Hill"",77)",NW31A9)

Why are there 6 columns? And why is the UDT incorrectly fetched (many
fields are missing)
A little improvement can be achieved, when the nested UDT
u_street_type is "flattened" to a varchar, which leads to the
assumption that nested UDT's are poorly supported by the JDBC driver:
====================================
CREATE TYPE u_address_type AS (
  street VARCHAR(80),
  zip VARCHAR(50),
  city VARCHAR(50),
  country u_country,
  since DATE,
  code INTEGER
)

INSERT INTO t_author VALUES (1, 'George', 'Orwell',
TO_DATE('1903-06-25', 'YYYY-MM-DD'), 1903, ROW('Parliament Hill 77',
'NW31A9', 'Hampstead', 'England', '1980-01-01', null))
INSERT INTO t_author VALUES (2, 'Paulo', 'Coelho',
TO_DATE('1947-08-24', 'YYYY-MM-DD'), 1947, ROW('Caixa Postal 43.003',
null, 'Rio de Janeiro', 'Brazil', '1940-01-01', 2))
====================================

Then the results will be something like this:

# of columns: 6
("Parliament Hill 77",NW31A9,Hampstead,England,1980-01-01,)

The UDT record now looks correct (fetched from the result set at
position 1). But there are still 6 columns in the result set.

Some facts:
- I do not experience these problems in pgAdmin III
- I use PostgreSQL 9.0.1, compiled by Visual C++ build 1500, 64-bit
- I use postgresql-9.0-801.jdbc4.jar

Does anyone have any idea what's wrong?

Re: Weird issues when reading UDT from stored function

Oliver Jowett <oliver@opencloud.com> — 2011-01-11T00:24:16Z

On 11/01/11 12:06, Lukas Eder wrote:

> CREATE TYPE u_street_type AS (
>   street VARCHAR(100),
>   no VARCHAR(30)
> )
> 
> CREATE TYPE u_address_type AS (
>   street u_street_type,
>   zip VARCHAR(50),
>   city VARCHAR(50),
>   country u_country,
>   since DATE,
>   code INTEGER
> )

> ====================================
> Output:
> # of columns: 6
> ("(""Parliament Hill"",77)",NW31A9)
> 
> Why are there 6 columns? And why is the UDT incorrectly fetched (many
> fields are missing)

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.

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.

Oliver

Re: Weird issues when reading UDT from stored function

Oliver Jowett <oliver@opencloud.com> — 2011-01-11T01:13:47Z

On 11/01/11 13:24, Oliver Jowett wrote:
> On 11/01/11 12:06, Lukas Eder wrote:

>> ====================================
>> Output:
>> # of columns: 6
>> ("(""Parliament Hill"",77)",NW31A9)
>>
>> Why are there 6 columns? And why is the UDT incorrectly fetched (many
>> fields are missing)
>
> 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.

Oops, looking closer I see what you mean, that's actually 2 columns of 
the surrounding type - street + zip? What are the values of the other 5 
columns reported by the driver?

A loglevel=2 trace would still be useful here.

Oliver

Fwd: Weird issues when reading UDT from stored function

Lukas Eder <lukas.eder@gmail.com> — 2011-01-11T15:55:52Z

>
> 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"
===================================

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

Radosław Smogura <rsmogura@softperience.eu> — 2011-01-11T18:46:33Z

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"
> ===================================

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

Lukas Eder <lukas.eder@gmail.com> — 2011-01-11T22:54:19Z

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@softperience.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.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"
> > ===================================
>

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

Radosław Smogura <rsmogura@softperience.eu> — 2011-01-12T10:12:13Z

 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.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()  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.eder@gmail.com
> [2] mailto:rsmogura@softperience.eu

Re: [HACKERS] Fwd: Weird issues when reading UDT from stored function

Robert Haas <robertmhaas@gmail.com> — 2011-01-17T04:27:41Z

On Wed, Jan 12, 2011 at 5:12 AM, rsmogura <rsmogura@softperience.eu> wrote:
> 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.

I've read this report over a few times now, and I'm still not
understanding exactly what is happening that you're unhappy about.

-- 
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

Re: [HACKERS] Fwd: Weird issues when reading UDT from stored function

Oliver Jowett <oliver@opencloud.com> — 2011-01-17T05:00:48Z

On 17/01/11 17:27, Robert Haas wrote:
> On Wed, Jan 12, 2011 at 5:12 AM, rsmogura<rsmogura@softperience.eu>  wrote:
>> 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.
>
> I've read this report over a few times now, and I'm still not
> understanding exactly what is happening that you're unhappy about.

If I understand it correctly, the problem is this:

Given the schema and data from the OP

(summary:
t_author is a TABLE
t_author.address is of type u_address_type
u_address_type is a TYPE with fields: street, zip, city, country, since, 
code
u_address_type.street is of type u_street_type
u_street_type is a TYPE with fields: street, no)

A bare SELECT works as expected:

> test_udt=# SELECT t_author.address FROM t_author WHERE first_name = 'George';
>                               address
> -------------------------------------------------------------------
>  ("(""Parliament Hill"",77)",NW31A9,Hampstead,England,1980-01-01,)
> (1 row)

However, doing the same via a plpgsql function with an OUT parameter 
produces something completely mangled:

> test_udt=# CREATE FUNCTION p_enhance_address2 (address OUT u_address_type) AS $$ BEGIN SELECT t_author.address INTO address FROM t_author WHERE first_name = 'George'; END; $$ LANGUAGE plpgsql;
> CREATE FUNCTION

> test_udt=# SELECT * FROM p_enhance_address2();
>                street                | zip | city | country | since | code
> -------------------------------------+-----+------+---------+-------+------
>  ("(""Parliament Hill"",77)",NW31A9) |     |      |         |       |
> (1 row)

Here, we've somehow got the first two fields of u_address_type - street 
and zip - squashed together into one column named 'street', and all the 
other columns nulled out.

Unsurprisingly the JDBC driver produces confusing results when faced 
with this, so it was originally reported as a JDBC problem, but the 
underlying problem can be seen via psql too.

Oliver

Re: [HACKERS] Fwd: Weird issues when reading UDT from stored function

Robert Haas <robertmhaas@gmail.com> — 2011-01-17T13:15:04Z

On Mon, Jan 17, 2011 at 12:00 AM, Oliver Jowett <oliver@opencloud.com> wrote:
> However, doing the same via a plpgsql function with an OUT parameter
> produces something completely mangled:
>
>> test_udt=# CREATE FUNCTION p_enhance_address2 (address OUT u_address_type)
>> AS $$ BEGIN SELECT t_author.address INTO address FROM t_author WHERE
>> first_name = 'George'; END; $$ LANGUAGE plpgsql;
>> CREATE FUNCTION
>
>> test_udt=# SELECT * FROM p_enhance_address2();
>>               street                | zip | city | country | since | code
>>
>> -------------------------------------+-----+------+---------+-------+------
>>  ("(""Parliament Hill"",77)",NW31A9) |     |      |         |       |
>> (1 row)
>
> Here, we've somehow got the first two fields of u_address_type - street and
> zip - squashed together into one column named 'street', and all the other
> columns nulled out.

I think this is the old problem of PL/pgsql having two forms of SELECT
INTO.  You can either say:

SELECT col1, col2, col3, ... INTO recordvar FROM ...

Or you can say:

SELECT col1, col2, col3, ... INTO nonrecordvar1, nonrecordvar2,
nonrecordvar3, ... FROM ...

In this case, since address is a recordvar, it's expecting the first
form - thus the first select-list item gets matched to the first
column of the address, rather than to address as a whole.  It's not
smart enough to consider the types of the items involved - only
whether they are records.  :-(

-- 
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

Re: [HACKERS] Fwd: Weird issues when reading UDT from stored function

Lukas Eder <lukas.eder@gmail.com> — 2011-01-25T07:39:51Z

>
> > Here, we've somehow got the first two fields of u_address_type - street
> and

 > zip - squashed together into one column named 'street', and all the other
> > columns nulled out.
>
> I think this is the old problem of PL/pgsql having two forms of SELECT
> INTO.  You can either say:
>
> SELECT col1, col2, col3, ... INTO recordvar FROM ...
>
> Or you can say:
>
> SELECT col1, col2, col3, ... INTO nonrecordvar1, nonrecordvar2,
> nonrecordvar3, ... FROM ...
>
> In this case, since address is a recordvar, it's expecting the first
> form - thus the first select-list item gets matched to the first
> column of the address, rather than to address as a whole.  It's not
> smart enough to consider the types of the items involved - only
> whether they are records.  :-(
>

So what you're suggesting is that the plpgsql code is causing the issues?
Are there any indications about how I could re-write this code? The
important thing for me is to have the aforementioned signature of the
plpgsql function with one UDT OUT parameter. Even if this is a bit awkward
in general, in this case, I don't mind rewriting the plpgsql function
content to create a workaround for this problem...

Re: [HACKERS] Fwd: Weird issues when reading UDT from stored function

Radosław Smogura <rsmogura@softperience.eu> — 2011-01-25T09:06:10Z

 Hi,
 I don't know if this is a bug, but at least I haven't found any clear 
 statement in documentation about; this should be wrote with big and bold 
 letters.

 In any way I think this is bug or big inconsistency, because of, as was 
 stated in previous mail
 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),
 but if you will create above function without last, i1 parameter 
 (SELECT * FROM p_enhance_address2();) then result will be
                street                | zip | city | country | since | 
 code
 -------------------------------------+-----+------+---------+-------+------
  ("(""Parliament Hill"",77)",NW31A9) |     |      |         |       |
 In last case, I think, result should be "packed" in one column, because 
 of it clearly "unpacked" record.

 On Tue, 25 Jan 2011 14:39:51 +0700, Lukas Eder wrote:
>>> Here, we've somehow got the first two fields of u_address_type -
>> street and
>
>>> zip - squashed together into one column named 'street', and all
>> the other
>>> columns nulled out.
>>  
>> I think this is the old problem of PL/pgsql having two forms of
>> SELECT
>> INTO.  You can either say:
>>  
>> SELECT col1, col2, col3, ... INTO recordvar FROM ...
>>  
>> Or you can say:
>>  
>> SELECT col1, col2, col3, ... INTO nonrecordvar1, nonrecordvar2,
>> nonrecordvar3, ... FROM ...
>>  
>> In this case, since address is a recordvar, it's expecting the first
>>
>> form - thus the first select-list item gets matched to the first
>> column of the address, rather than to address as a whole.  It's not
>>
>> smart enough to consider the types of the items involved - only
>> whether they are records.  :-(
>
>  
> So what you're suggesting is that the plpgsql code is causing the
> issues? Are there any indications about how I could re-write this
> code? The important thing for me is to have the aforementioned
> signature of the plpgsql function with one UDT OUT parameter. Even
> if this is a bit awkward in general, in this case, I don't mind
> rewriting the plpgsql function content to create a workaround for
> this problem...

Re: Fwd: [JDBC] Weird issues when reading UDT from stored function

Robert Haas <robertmhaas@gmail.com> — 2011-02-11T02:56:16Z

On Tue, Jan 25, 2011 at 2:39 AM, Lukas Eder <lukas.eder@gmail.com> wrote:
> So what you're suggesting is that the plpgsql code is causing the issues?
> Are there any indications about how I could re-write this code? The
> important thing for me is to have the aforementioned signature of the
> plpgsql function with one UDT OUT parameter. Even if this is a bit awkward
> in general, in this case, I don't mind rewriting the plpgsql function
> content to create a workaround for this problem...

Possibly something like address := (SELECT ...) rather than SELECT ...
INTO address?

-- 
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

Re: Fwd: [JDBC] Weird issues when reading UDT from stored function

Lukas Eder <lukas.eder@gmail.com> — 2011-02-12T11:16:10Z

I had tried that before. That doesn't seem to change anything. JDBC still
expects 6 OUT parameters, instead of just 1...

2011/2/11 Robert Haas <robertmhaas@gmail.com>

> On Tue, Jan 25, 2011 at 2:39 AM, Lukas Eder <lukas.eder@gmail.com> wrote:
> > So what you're suggesting is that the plpgsql code is causing the issues?
> > Are there any indications about how I could re-write this code? The
> > important thing for me is to have the aforementioned signature of the
> > plpgsql function with one UDT OUT parameter. Even if this is a bit
> awkward
> > in general, in this case, I don't mind rewriting the plpgsql function
> > content to create a workaround for this problem...
>
> Possibly something like address := (SELECT ...) rather than SELECT ...
> INTO address?
>
> --
> Robert Haas
> EnterpriseDB: http://www.enterprisedb.com
> The Enterprise PostgreSQL Company
>

Re: Fwd: [JDBC] Weird issues when reading UDT from stored function

Robert Haas <robertmhaas@gmail.com> — 2011-02-15T15:36:36Z

On Sat, Feb 12, 2011 at 6:16 AM, Lukas Eder <lukas.eder@gmail.com> wrote:
> I had tried that before. That doesn't seem to change anything. JDBC still
> expects 6 OUT parameters, instead of just 1...

Oh, hrm.  I thought you were trying to fix the return value, rather
than the signature.

I am not sure how to fix the signature.  Can you just make it return RECORD?

-- 
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

Re: Fwd: [JDBC] Weird issues when reading UDT from stored function

Lukas Eder <lukas.eder@gmail.com> — 2011-02-16T08:30:43Z

I'm not trying to fix the signature. I want exactly that signature. I want
to return 1 UDT as an OUT parameter from a function.

Somewhere between JDBC and the database, this signature is lost, and JDBC's
internal code tells me that I have to bind 6 OUT parameters, instead of 1.
It happens to be so, because the UDT contains 6 attributes, so somehow the
JDBC/database protocol flattens the UDT, and I think that's a bug, either in
JDBC or in the protocol or in the database. My findings were that I can
correctly read the UDT OUT parameter using the pgAdmin III tool, so I
excluded the database as a bug holder candidate.

Cheers
Lukas

2011/2/15 Robert Haas <robertmhaas@gmail.com>

> On Sat, Feb 12, 2011 at 6:16 AM, Lukas Eder <lukas.eder@gmail.com> wrote:
> > I had tried that before. That doesn't seem to change anything. JDBC still
> > expects 6 OUT parameters, instead of just 1...
>
> Oh, hrm.  I thought you were trying to fix the return value, rather
> than the signature.
>
> I am not sure how to fix the signature.  Can you just make it return
> RECORD?
>
> --
> Robert Haas
> EnterpriseDB: http://www.enterprisedb.com
> The Enterprise PostgreSQL Company
>

Re: Fwd: [JDBC] Weird issues when reading UDT from stored function

Robert Haas <robertmhaas@gmail.com> — 2011-02-16T11:58:12Z

On Wed, Feb 16, 2011 at 3:30 AM, Lukas Eder <lukas.eder@gmail.com> wrote:
> I'm not trying to fix the signature. I want exactly that signature. I want
> to return 1 UDT as an OUT parameter from a function.
>
> Somewhere between JDBC and the database, this signature is lost, and JDBC's
> internal code tells me that I have to bind 6 OUT parameters, instead of 1.
> It happens to be so, because the UDT contains 6 attributes, so somehow the
> JDBC/database protocol flattens the UDT, and I think that's a bug, either in
> JDBC or in the protocol or in the database. My findings were that I can
> correctly read the UDT OUT parameter using the pgAdmin III tool, so I
> excluded the database as a bug holder candidate.

Oh, OK.  Sorry, I can't help you any with the JDBC side...

-- 
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

Re: Fwd: [JDBC] Weird issues when reading UDT from stored function

Oliver Jowett <oliver@opencloud.com> — 2011-02-16T12:03:38Z

On 17/02/11 00:58, Robert Haas wrote:
> On Wed, Feb 16, 2011 at 3:30 AM, Lukas Eder <lukas.eder@gmail.com> wrote:
>> I'm not trying to fix the signature. I want exactly that signature. I want
>> to return 1 UDT as an OUT parameter from a function.
>>
>> Somewhere between JDBC and the database, this signature is lost, and JDBC's
>> internal code tells me that I have to bind 6 OUT parameters, instead of 1.
>> It happens to be so, because the UDT contains 6 attributes, so somehow the
>> JDBC/database protocol flattens the UDT, and I think that's a bug, either in
>> JDBC or in the protocol or in the database. My findings were that I can
>> correctly read the UDT OUT parameter using the pgAdmin III tool, so I
>> excluded the database as a bug holder candidate.
> 
> Oh, OK.  Sorry, I can't help you any with the JDBC side...

Well, the underlying problem is that "SELECT * from
function_with_one_out_parameter()" is returning *6* columns, not 1
column. I don't know if that's expected or not on the plpgsql side, but
the JDBC driver has no way of distinguishing that sort of result from a
function that has 6 OUT parameters.

Oliver

Re: Fwd: [JDBC] Weird issues when reading UDT from stored function

Lukas Eder <lukas.eder@gmail.com> — 2011-02-16T12:07:18Z

So what should I do? File a bug to the main Postgres mailing list? Or just
not support that feature?

2011/2/16 Oliver Jowett <oliver@opencloud.com>

> On 17/02/11 00:58, Robert Haas wrote:
> > On Wed, Feb 16, 2011 at 3:30 AM, Lukas Eder <lukas.eder@gmail.com>
> wrote:
> >> I'm not trying to fix the signature. I want exactly that signature. I
> want
> >> to return 1 UDT as an OUT parameter from a function.
> >>
> >> Somewhere between JDBC and the database, this signature is lost, and
> JDBC's
> >> internal code tells me that I have to bind 6 OUT parameters, instead of
> 1.
> >> It happens to be so, because the UDT contains 6 attributes, so somehow
> the
> >> JDBC/database protocol flattens the UDT, and I think that's a bug,
> either in
> >> JDBC or in the protocol or in the database. My findings were that I can
> >> correctly read the UDT OUT parameter using the pgAdmin III tool, so I
> >> excluded the database as a bug holder candidate.
> >
> > Oh, OK.  Sorry, I can't help you any with the JDBC side...
>
> Well, the underlying problem is that "SELECT * from
> function_with_one_out_parameter()" is returning *6* columns, not 1
> column. I don't know if that's expected or not on the plpgsql side, but
> the JDBC driver has no way of distinguishing that sort of result from a
> function that has 6 OUT parameters.
>
> Oliver
>

Re: Fwd: [JDBC] Weird issues when reading UDT from stored function

Robert Haas <robertmhaas@gmail.com> — 2011-02-16T12:10:40Z

On Wed, Feb 16, 2011 at 7:03 AM, Oliver Jowett <oliver@opencloud.com> wrote:
> On 17/02/11 00:58, Robert Haas wrote:
>> On Wed, Feb 16, 2011 at 3:30 AM, Lukas Eder <lukas.eder@gmail.com> wrote:
>>> I'm not trying to fix the signature. I want exactly that signature. I want
>>> to return 1 UDT as an OUT parameter from a function.
>>>
>>> Somewhere between JDBC and the database, this signature is lost, and JDBC's
>>> internal code tells me that I have to bind 6 OUT parameters, instead of 1.
>>> It happens to be so, because the UDT contains 6 attributes, so somehow the
>>> JDBC/database protocol flattens the UDT, and I think that's a bug, either in
>>> JDBC or in the protocol or in the database. My findings were that I can
>>> correctly read the UDT OUT parameter using the pgAdmin III tool, so I
>>> excluded the database as a bug holder candidate.
>>
>> Oh, OK.  Sorry, I can't help you any with the JDBC side...
>
> Well, the underlying problem is that "SELECT * from
> function_with_one_out_parameter()" is returning *6* columns, not 1
> column. I don't know if that's expected or not on the plpgsql side, but
> the JDBC driver has no way of distinguishing that sort of result from a
> function that has 6 OUT parameters.

If you do SELECT function_with_one_out_parameter() rather than SELECT
* FROM function_with_one_out_parameter(), you'll get just one
argument.  Does that help at all?

-- 
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

Re: Fwd: [JDBC] Weird issues when reading UDT from stored function

Robert Haas <robertmhaas@gmail.com> — 2011-02-16T12:11:32Z

On Wed, Feb 16, 2011 at 7:07 AM, Lukas Eder <lukas.eder@gmail.com> wrote:
> So what should I do? File a bug to the main Postgres mailing list? Or just
> not support that feature?

Well, I thought you just said you'd ruled out a PG bug?

-- 
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

Re: Fwd: [JDBC] Weird issues when reading UDT from stored function

Radosław Smogura <rsmogura@softperience.eu> — 2011-02-16T12:12:22Z

 If I may give some suggestion, I was tried to investigate this, and 
 maybe some this will help
 When you create procedure with out parameters then return type of this 
 is implicit calculated and may be
 record or base type (if exactly one out param is defined).

 In many places I saw comparison of return type to recordoid or complex 
 type, but check against complex type is through pg_types only, if 
 typtype is marked 'c'. Unfortunately both rows and STRUCT (complex) has 
 there 'c' - and this is OK for situation when procedure will return 
 "table". But for complex types not being recordoid I think additional 
 check should go. I mean to use get_rel_relkind() and e.g. check if it is 
 pure complex type.

 By the way,
 Actually, based on above I saw funny things - I can create table with 
 column type being other table :) And now If my one output parameter will 
 be of complex type and relkind row type, what should I get?

 On Wed, 16 Feb 2011 09:30:43 +0100, Lukas Eder wrote:
> I'm not trying to fix the signature. I want exactly that signature. I
> want to return 1 UDT as an OUT parameter from a function.
>
> Somewhere between JDBC and the database, this signature is lost, and
> JDBC's internal code tells me that I have to bind 6 OUT parameters,
> instead of 1. It happens to be so, because the UDT contains 6
> attributes, so somehow the JDBC/database protocol flattens the UDT,
> and I think that's a bug, either in JDBC or in the protocol or in the
> database. My findings were that I can correctly read the UDT OUT
> parameter using the pgAdmin III tool, so I excluded the database as a
> bug holder candidate.
>
> Cheers
> Lukas
>
> 2011/2/15 Robert Haas
>
>> On Sat, Feb 12, 2011 at 6:16 AM, Lukas Eder wrote:
>> > I had tried that before. That doesn't seem to change anything.
>> JDBC still
>> > expects 6 OUT parameters, instead of just 1...
>>
>> Oh, hrm.  I thought you were trying to fix the return value,
>> rather
>> than the signature.
>>
>> I am not sure how to fix the signature.  Can you just make it
>> return RECORD?
>>
>> --
>>
>> Robert Haas
>> EnterpriseDB: http://www.enterprisedb.com [2]
>> The Enterprise PostgreSQL Company
>
>
>
> Links:
> ------
> [1] mailto:lukas.eder@gmail.com
> [2] http://www.enterprisedb.com
> [3] mailto:robertmhaas@gmail.com

Re: Fwd: [JDBC] Weird issues when reading UDT from stored function

Lukas Eder <lukas.eder@gmail.com> — 2011-02-16T12:12:42Z

That was my opinion, but you're saying that JDBC is not the cause either?

2011/2/16 Robert Haas <robertmhaas@gmail.com>

> On Wed, Feb 16, 2011 at 7:07 AM, Lukas Eder <lukas.eder@gmail.com> wrote:
> > So what should I do? File a bug to the main Postgres mailing list? Or
> just
> > not support that feature?
>
> Well, I thought you just said you'd ruled out a PG bug?
>
> --
> Robert Haas
> EnterpriseDB: http://www.enterprisedb.com
> The Enterprise PostgreSQL Company
>

Re: Fwd: [JDBC] Weird issues when reading UDT from stored function

Lukas Eder <lukas.eder@gmail.com> — 2011-02-16T12:16:32Z

Hmm, good point. I should try that. I have only tried these syntaxes:

====================================
connection.prepareStatement("select * from p_enhance_address2()");
connection.prepareCall("{ call p_enhance_address2(?) }"); // with an
output parameter registered
====================================

Since I'm doing this for my database abstraction tool
http://jooq.sourceforge.net, I could add a specialised Postgres stored
procedures abstraction and hide these details from the outside world...
Thanks for the hint!

2011/2/16 Robert Haas <robertmhaas@gmail.com>

> On Wed, Feb 16, 2011 at 7:03 AM, Oliver Jowett <oliver@opencloud.com>
> wrote:
> > On 17/02/11 00:58, Robert Haas wrote:
> >> On Wed, Feb 16, 2011 at 3:30 AM, Lukas Eder <lukas.eder@gmail.com>
> wrote:
> >>> I'm not trying to fix the signature. I want exactly that signature. I
> want
> >>> to return 1 UDT as an OUT parameter from a function.
> >>>
> >>> Somewhere between JDBC and the database, this signature is lost, and
> JDBC's
> >>> internal code tells me that I have to bind 6 OUT parameters, instead of
> 1.
> >>> It happens to be so, because the UDT contains 6 attributes, so somehow
> the
> >>> JDBC/database protocol flattens the UDT, and I think that's a bug,
> either in
> >>> JDBC or in the protocol or in the database. My findings were that I can
> >>> correctly read the UDT OUT parameter using the pgAdmin III tool, so I
> >>> excluded the database as a bug holder candidate.
> >>
> >> Oh, OK.  Sorry, I can't help you any with the JDBC side...
> >
> > Well, the underlying problem is that "SELECT * from
> > function_with_one_out_parameter()" is returning *6* columns, not 1
> > column. I don't know if that's expected or not on the plpgsql side, but
> > the JDBC driver has no way of distinguishing that sort of result from a
> > function that has 6 OUT parameters.
>
> If you do SELECT function_with_one_out_parameter() rather than SELECT
> * FROM function_with_one_out_parameter(), you'll get just one
> argument.  Does that help at all?
>
> --
> Robert Haas
> EnterpriseDB: http://www.enterprisedb.com
> The Enterprise PostgreSQL Company
>

Re: Fwd: [JDBC] Weird issues when reading UDT from stored function

Oliver Jowett <oliver@opencloud.com> — 2011-02-16T12:43:09Z

On 17/02/11 01:10, Robert Haas wrote:

> If you do SELECT function_with_one_out_parameter() rather than SELECT
> * FROM function_with_one_out_parameter(), you'll get just one
> argument.  Does that help at all?

Unfortunately, not really, because it doesn't work for cases where
there's more than one OUT parameter (if you use the SELECT f() form in
that case, you get one gigantic result column, not one column per OUT
parameter)

I dug into the code and it's actually slightly different to what I
originally described. Currently given a JDBC escape of the form

  "{ call f(?,?,?,?) }"

it will rewrite that to:

  "SELECT * FROM f($1,$2,$3,$4) AS RESULT"

and this rewriting happens before we know which parameters are bound as
OUT parameters. So we can't special-case the one-OUT-parameter case
without quite a rewrite (no pun intended).

Once we get to the point of query execution, we know which parameters
are OUT parameters, and we bind void parameter values to those (v3
protocol). You have to do a PREPARE/EXECUTE to pass in void parameter
types to get the equivalent via psql, as far as I can tell.

Anyway, it's a bit counterintuitive that

  SELECT * FROM f($1,$2) AS RESULT

where f() takes two OUT parameters always returns two columns, but

  SELECT * FROM f($1) AS RESULT

might return any number of columns! Is that really the correct behavior
here?

Oliver

Re: Fwd: [JDBC] Weird issues when reading UDT from stored function

Florian G. Pflug <fgp@phlo.org> — 2011-02-16T13:36:49Z

On Feb16, 2011, at 13:43 , Oliver Jowett wrote:
> Anyway, it's a bit counterintuitive that
> 
>  SELECT * FROM f($1,$2) AS RESULT
> 
> where f() takes two OUT parameters always returns two columns, but
> 
>  SELECT * FROM f($1) AS RESULT
> 
> might return any number of columns! Is that really the correct behavior
> here?

Hm, I've browsed through the code and it seems that the current behaviour
was implemented on purpose. 

build_function_result_tupdesc_d() in funcapi.c explicitly does

  /*
   * If there is no output argument, or only one, the function does not
   * return tuples.
   */
  if (numoutargs < 2)
    return NULL;

and examine_parameter_list() in functioncmds.c takes care to set 
requiredResultType to RECORDOID only if there is more than one OUT
parameter, otherwise it gets set to the (one) OUT parameter's type.

Might make sense to check the list archives, maybe there is something
there that elucidates the reasoning behind this...

best regards,
Florian Pflug

Re: Fwd: [JDBC] Weird issues when reading UDT from stored function

Tom Lane <tgl@sss.pgh.pa.us> — 2011-02-16T15:23:43Z

Florian Pflug <fgp@phlo.org> writes:
> Hm, I've browsed through the code and it seems that the current behaviour
> was implemented on purpose. 

Yes, it's 100% intentional.  The idea is to allow function authors to
use OUT-parameter notation (in particular, the convention of assigning
to a named variable to set the result) without forcing them into the
overhead of returning a record when all they want is to return a scalar.
So a single OUT parameter is *supposed* to work just like a function
that does "returns whatever" without any OUT parameters.

Even if you think this was a bad choice, which I don't, it's far too
late to change it.

			regards, tom lane

Re: Fwd: [JDBC] Weird issues when reading UDT from stored function

Oliver Jowett <oliver@opencloud.com> — 2011-02-17T00:14:46Z

On 17/02/11 04:23, Tom Lane wrote:
> Florian Pflug <fgp@phlo.org> writes:
>> Hm, I've browsed through the code and it seems that the current behaviour
>> was implemented on purpose. 
> 
> Yes, it's 100% intentional.  The idea is to allow function authors to
> use OUT-parameter notation (in particular, the convention of assigning
> to a named variable to set the result) without forcing them into the
> overhead of returning a record when all they want is to return a scalar.
> So a single OUT parameter is *supposed* to work just like a function
> that does "returns whatever" without any OUT parameters.
> 
> Even if you think this was a bad choice, which I don't, it's far too
> late to change it.

Any suggestions about how the JDBC driver can express the query to get
the behavior that it wants? Specifically, the driver wants to call a
particular function with N OUT or INOUT parameters (and maybe some other
IN parameters too) and get a resultset with N columns back.

The current approach is to say "SELECT * FROM f(params) AS RESULT" which
works in all cases *except* for the case where there is exactly one OUT
parameter and it has a record/UDT type.

Oliver

Re: Fwd: [JDBC] Weird issues when reading UDT from stored function

Radosław Smogura <rsmogura@softperience.eu> — 2011-02-17T09:30:51Z

 Something like this,

 Everything must be done on call, due to polymorphic signatures, this 
 can be kept in short living cache, but bear in mind user can alter 
 procedure in meantime.

 When JDBC driver will detect if procedure call statement is created.
 1. Determine procedure oid - how? procedures may have not qualified 
 name. Is any function on backend that will deal with schema search path? 
 You may need to pass procedure parameters or at least types? or we need 
 to mirror backend code to Java?
 2. Download procedure signature and parse, determine what is input and 
 what is output.
 3. Determine how many output parameters user registered, if 1st 
 parameter is ? = exec(?, ?)
 4. If only 1 parameter is output (and its UDT, pure UDT due to relkind) 
 use SELECT (RESULT) as "your_param_name" FROM f(params) AS RESULT, if I 
 remember well using () puts all in on record

 Above will resolve some other problems in JDBC.

 Ad 3. Problem is with 1st parameter, actually result of such procedure 
 may be record, so I think I should get in our address example, when call 
 ? = ench(addres ?), result set like
 address, address
 But this is to discussion.

 Postgresql has own roads, far away from support of any standard.

 On Thu, 17 Feb 2011 13:14:46 +1300, Oliver Jowett wrote:
> On 17/02/11 04:23, Tom Lane wrote:
>> Florian Pflug <fgp@phlo.org> writes:
>>> Hm, I've browsed through the code and it seems that the current 
>>> behaviour
>>> was implemented on purpose.
>>
>> Yes, it's 100% intentional.  The idea is to allow function authors 
>> to
>> use OUT-parameter notation (in particular, the convention of 
>> assigning
>> to a named variable to set the result) without forcing them into the
>> overhead of returning a record when all they want is to return a 
>> scalar.
>> So a single OUT parameter is *supposed* to work just like a function
>> that does "returns whatever" without any OUT parameters.
>>
>> Even if you think this was a bad choice, which I don't, it's far too
>> late to change it.
>
> Any suggestions about how the JDBC driver can express the query to 
> get
> the behavior that it wants? Specifically, the driver wants to call a
> particular function with N OUT or INOUT parameters (and maybe some 
> other
> IN parameters too) and get a resultset with N columns back.
>
> The current approach is to say "SELECT * FROM f(params) AS RESULT" 
> which
> works in all cases *except* for the case where there is exactly one 
> OUT
> parameter and it has a record/UDT type.
>
> Oliver

Re: Fwd: [JDBC] Weird issues when reading UDT from stored function

Florian G. Pflug <fgp@phlo.org> — 2011-02-17T09:33:11Z

On Feb17, 2011, at 01:14 , Oliver Jowett wrote:
> Any suggestions about how the JDBC driver can express the query to get
> the behavior that it wants? Specifically, the driver wants to call a
> particular function with N OUT or INOUT parameters (and maybe some other
> IN parameters too) and get a resultset with N columns back.

There's no sane way to do that, I fear. You could of course look up the
function definition in the catalog before actually calling it, but with
overloading and polymorphic types finding the right pg_proc entry seems
awfully complex.

Your best option is probably to just document this caveat...

best regards,
Florian Pflug

Re: Fwd: [JDBC] Weird issues when reading UDT from stored function

Florian G. Pflug <fgp@phlo.org> — 2011-02-17T09:39:17Z

On Feb17, 2011, at 10:30 , rsmogura wrote:
> When JDBC driver will detect if procedure call statement is created.
> 1. Determine procedure oid - how? procedures may have not qualified name. Is any function on backend that will deal with schema search path? You may need to pass procedure parameters or at least types? or we need to mirror backend code to Java?

That change of getting this correct without help from the backend is exactly zero. (Hint: You need to consider overloaded functions and implicit casts of parameters...)

best regards,
Florian Pflug

Re: Fwd: [JDBC] Weird issues when reading UDT from stored function

Lukas Eder <lukas.eder@gmail.com> — 2011-02-17T09:45:06Z

2011/2/17 Florian Pflug <fgp@phlo.org>

> On Feb17, 2011, at 01:14 , Oliver Jowett wrote:
> > Any suggestions about how the JDBC driver can express the query to get
> > the behavior that it wants? Specifically, the driver wants to call a
> > particular function with N OUT or INOUT parameters (and maybe some other
> > IN parameters too) and get a resultset with N columns back.
>
> There's no sane way to do that, I fear. You could of course look up the
> function definition in the catalog before actually calling it, but with
> overloading and polymorphic types finding the right pg_proc entry seems
> awfully complex.
>
> Your best option is probably to just document this caveat...
>

But there still is a bug in the JDBC driver as I originally documented it.
Even if you say it's not simple to know whether the signature is actually a
single UDT with 6 attributes or just 6 OUT parameters, the result is wrong
(as stated in my original mail):

The nested UDT structure completely screws up fetching results. This
> is what I get with JDBC:
> ====================================
>
>        PreparedStatement stmt = connection.prepareStatement("select *
> from p_enhance_address2()");
>        ResultSet rs = stmt.executeQuery();
>
>        while (rs.next()) {
>            System.out.println("# of columns: " +
> rs.getMetaData().getColumnCount());
>            System.out.println(rs.getObject(1));
>        }
> ====================================
> Output:
> # of columns: 6
> ("(""Parliament Hill"",77)",NW31A9)
>

The result set meta data correctly state that there are 6 OUT columns. But
only the first 2 are actually fetched (because of a nested UDT)...

Re: Fwd: [JDBC] Weird issues when reading UDT from stored function

Pavel Stehule <pavel.stehule@gmail.com> — 2011-02-17T09:53:19Z

2011/2/17 Florian Pflug <fgp@phlo.org>:
> On Feb17, 2011, at 10:30 , rsmogura wrote:
>> When JDBC driver will detect if procedure call statement is created.
>> 1. Determine procedure oid - how? procedures may have not qualified name. Is any function on backend that will deal with schema search path? You may need to pass procedure parameters or at least types? or we need to mirror backend code to Java?
>
> That change of getting this correct without help from the backend is exactly zero. (Hint: You need to consider overloaded functions and implicit casts of parameters...)
>

There is only one way - implementation of CALL statement. Any
emulation on JDBC level is just way to hell. Now, we have to say -
PostgreSQL doesn't support a CALL statement, support only functions -
and everybody has to use a different pattern than in other databases.
Any emulation on JDBC means, it will be slowly, it will be
unpredictable.

Regards

Pavel Stehule


> best regards,
> Florian Pflug
>
>
> --
> Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-hackers
>

Re: Fwd: [JDBC] Weird issues when reading UDT from stored function

Radosław Smogura <rsmogura@softperience.eu> — 2011-02-17T10:03:18Z

 Yes new node should be created and added for 8.x and 9.x releases...

 On Thu, 17 Feb 2011 10:53:19 +0100, Pavel Stehule wrote:
> 2011/2/17 Florian Pflug <fgp@phlo.org>:
>> On Feb17, 2011, at 10:30 , rsmogura wrote:
>>> When JDBC driver will detect if procedure call statement is 
>>> created.
>>> 1. Determine procedure oid - how? procedures may have not qualified 
>>> name. Is any function on backend that will deal with schema search 
>>> path? You may need to pass procedure parameters or at least types? or 
>>> we need to mirror backend code to Java?
>>
>> That change of getting this correct without help from the backend is 
>> exactly zero. (Hint: You need to consider overloaded functions and 
>> implicit casts of parameters...)
>>
>
> There is only one way - implementation of CALL statement. Any
> emulation on JDBC level is just way to hell. Now, we have to say -
> PostgreSQL doesn't support a CALL statement, support only functions -
> and everybody has to use a different pattern than in other databases.
> Any emulation on JDBC means, it will be slowly, it will be
> unpredictable.
>
> Regards
>
> Pavel Stehule
>
>
>> best regards,
>> Florian Pflug
>>
>>
>> --
>> Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
>> To make changes to your subscription:
>> http://www.postgresql.org/mailpref/pgsql-hackers
>>

Re: Fwd: [JDBC] Weird issues when reading UDT from stored function

Oliver Jowett <oliver@opencloud.com> — 2011-02-17T10:08:13Z

Lukas Eder wrote:

> The result set meta data correctly state that there are 6 OUT columns. 
> But only the first 2 are actually fetched (because of a nested UDT)...

The data mangling was just a plpgsql syntactic issue, wasn't it?

Oliver

Re: Fwd: [JDBC] Weird issues when reading UDT from stored function

Radosław Smogura <rsmogura@softperience.eu> — 2011-02-17T10:13:49Z

 Maybe change in backend to treat complex types marked in relation as 
 COMPLEX in same way as scalar values is solution, actually I don't know. 
 This can be determined by GUC variable so every one can be happy :)

 On Thu, 17 Feb 2011 23:08:13 +1300, Oliver Jowett wrote:
> Lukas Eder wrote:
>
>> The result set meta data correctly state that there are 6 OUT 
>> columns. But only the first 2 are actually fetched (because of a 
>> nested UDT)...
>
> The data mangling was just a plpgsql syntactic issue, wasn't it?
>
> Oliver

Re: Fwd: [JDBC] Weird issues when reading UDT from stored function

Oliver Jowett <oliver@opencloud.com> — 2011-02-17T10:15:07Z

Florian Pflug wrote:
> On Feb17, 2011, at 01:14 , Oliver Jowett wrote:
>> Any suggestions about how the JDBC driver can express the query to get
>> the behavior that it wants? Specifically, the driver wants to call a
>> particular function with N OUT or INOUT parameters (and maybe some other
>> IN parameters too) and get a resultset with N columns back.
> 
> There's no sane way to do that, I fear. You could of course look up the
> function definition in the catalog before actually calling it, but with
> overloading and polymorphic types finding the right pg_proc entry seems
> awfully complex.
> 
> Your best option is probably to just document this caveat...

Well, the JDBC driver does know how many OUT parameters there are before 
execution happens, so it could theoretically do something different for 
1 OUT vs. many OUT parameters.

The problem is that currently the translation of the JDBC "{ call }" 
escape happens early on, well before we know which parameters are OUT 
parameters. Moving that translation later is, at best, tricky, so I was 
hoping there was one query form that would handle all cases.

Oliver

Re: Fwd: [JDBC] Weird issues when reading UDT from stored function

Pavel Stehule <pavel.stehule@gmail.com> — 2011-02-17T10:16:31Z

2011/2/17 rsmogura <rsmogura@softperience.eu>:
> Yes new node should be created and added for 8.x and 9.x releases...

what node?

Pavel

>
> On Thu, 17 Feb 2011 10:53:19 +0100, Pavel Stehule wrote:
>>
>> 2011/2/17 Florian Pflug <fgp@phlo.org>:
>>>
>>> On Feb17, 2011, at 10:30 , rsmogura wrote:
>>>>
>>>> When JDBC driver will detect if procedure call statement is created.
>>>> 1. Determine procedure oid - how? procedures may have not qualified
>>>> name. Is any function on backend that will deal with schema search path? You
>>>> may need to pass procedure parameters or at least types? or we need to
>>>> mirror backend code to Java?
>>>
>>> That change of getting this correct without help from the backend is
>>> exactly zero. (Hint: You need to consider overloaded functions and implicit
>>> casts of parameters...)
>>>
>>
>> There is only one way - implementation of CALL statement. Any
>> emulation on JDBC level is just way to hell. Now, we have to say -
>> PostgreSQL doesn't support a CALL statement, support only functions -
>> and everybody has to use a different pattern than in other databases.
>> Any emulation on JDBC means, it will be slowly, it will be
>> unpredictable.
>>
>> Regards
>>
>> Pavel Stehule
>>
>>
>>> best regards,
>>> Florian Pflug
>>>
>>>
>>> --
>>> Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
>>> To make changes to your subscription:
>>> http://www.postgresql.org/mailpref/pgsql-hackers
>>>
>
>

Re: Fwd: [JDBC] Weird issues when reading UDT from stored function

Radosław Smogura <rsmogura@softperience.eu> — 2011-02-17T10:18:17Z

 Yes, but driver checks number of declared out parameters and number of 
 resulted parameters (even check types of those), to prevent programming 
 errors.

 On Thu, 17 Feb 2011 23:15:07 +1300, Oliver Jowett wrote:
> Florian Pflug wrote:
>> On Feb17, 2011, at 01:14 , Oliver Jowett wrote:
>>> Any suggestions about how the JDBC driver can express the query to 
>>> get
>>> the behavior that it wants? Specifically, the driver wants to call 
>>> a
>>> particular function with N OUT or INOUT parameters (and maybe some 
>>> other
>>> IN parameters too) and get a resultset with N columns back.
>> There's no sane way to do that, I fear. You could of course look up 
>> the
>> function definition in the catalog before actually calling it, but 
>> with
>> overloading and polymorphic types finding the right pg_proc entry 
>> seems
>> awfully complex.
>> Your best option is probably to just document this caveat...
>
> Well, the JDBC driver does know how many OUT parameters there are
> before execution happens, so it could theoretically do something
> different for 1 OUT vs. many OUT parameters.
>
> The problem is that currently the translation of the JDBC "{ call }"
> escape happens early on, well before we know which parameters are OUT
> parameters. Moving that translation later is, at best, tricky, so I
> was hoping there was one query form that would handle all cases.
>
> Oliver

Re: Fwd: [JDBC] Weird issues when reading UDT from stored function

Oliver Jowett <oliver@opencloud.com> — 2011-02-17T11:06:22Z

On 17/02/11 23:18, rsmogura wrote:
> Yes, but driver checks number of declared out parameters and number of
> resulted parameters (even check types of those), to prevent programming
> errors.

And..?

Oliver

Re: Fwd: [JDBC] Weird issues when reading UDT from stored function

Radosław Smogura <rsmogura@softperience.eu> — 2011-02-17T11:37:38Z

 On Fri, 18 Feb 2011 00:06:22 +1300, Oliver Jowett wrote:
> On 17/02/11 23:18, rsmogura wrote:
>> Yes, but driver checks number of declared out parameters and number 
>> of
>> resulted parameters (even check types of those), to prevent 
>> programming
>> errors.
>
> And..?
>
> Oliver

 And it will throw exception when result will income. If you will remove 
 this then you will lose check against programming errors, when number of 
 expected parameters is different that number of actual parameters. Bear 
 in mind that you will get result set of 6 columns, but only 1 is 
 expected. I think you can't determine what should be returned and how to 
 fix result without signature.

Re: Fwd: [JDBC] Weird issues when reading UDT from stored function

Oliver Jowett <oliver@opencloud.com> — 2011-02-17T11:44:07Z

On 18/02/11 00:37, rsmogura wrote:
> On Fri, 18 Feb 2011 00:06:22 +1300, Oliver Jowett wrote:
>> On 17/02/11 23:18, rsmogura wrote:
>>> Yes, but driver checks number of declared out parameters and number of
>>> resulted parameters (even check types of those), to prevent programming
>>> errors.
>>
>> And..?
>>
>> Oliver
> 
> And it will throw exception when result will income. If you will remove
> this then you will lose check against programming errors, when number of
> expected parameters is different that number of actual parameters. Bear
> in mind that you will get result set of 6 columns, but only 1 is
> expected. I think you can't determine what should be returned and how to
> fix result without signature.

You've completely missed the point. I am not suggesting we change those
checks at all. I am suggesting we change how the JDBC driver translates
call escapes to queries so that for N OUT parameters, we always get
exactly N result columns, without depending on the datatypes of the
parameters in any way.

Oliver

Re: Fwd: [JDBC] Weird issues when reading UDT from stored function

Radosław Smogura <rsmogura@softperience.eu> — 2011-02-17T11:52:36Z

 On Fri, 18 Feb 2011 00:44:07 +1300, Oliver Jowett wrote:
> On 18/02/11 00:37, rsmogura wrote:
>> On Fri, 18 Feb 2011 00:06:22 +1300, Oliver Jowett wrote:
>>> On 17/02/11 23:18, rsmogura wrote:
>>>> Yes, but driver checks number of declared out parameters and 
>>>> number of
>>>> resulted parameters (even check types of those), to prevent 
>>>> programming
>>>> errors.
>>>
>>> And..?
>>>
>>> Oliver
>>
>> And it will throw exception when result will income. If you will 
>> remove
>> this then you will lose check against programming errors, when 
>> number of
>> expected parameters is different that number of actual parameters. 
>> Bear
>> in mind that you will get result set of 6 columns, but only 1 is
>> expected. I think you can't determine what should be returned and 
>> how to
>> fix result without signature.
>
> You've completely missed the point. I am not suggesting we change 
> those
> checks at all. I am suggesting we change how the JDBC driver 
> translates
> call escapes to queries so that for N OUT parameters, we always get
> exactly N result columns, without depending on the datatypes of the
> parameters in any way.
>
> Oliver

 May You provide example select for this, and check behaviour with below 
 procedure, too.

 CREATE OR REPLACE FUNCTION p_enhance_address3(OUT address 
 u_address_type, OUT i1 integer)
   RETURNS record AS
 $BODY$
 BEGIN
         SELECT t_author.address
         INTO address
         FROM t_author
         WHERE first_name = 'George';
 i1 = 12;
 END;
 $BODY$
   LANGUAGE plpgsql

Re: Fwd: [JDBC] Weird issues when reading UDT from stored function

Oliver Jowett <oliver@opencloud.com> — 2011-02-17T12:02:18Z

On 18/02/11 00:52, rsmogura wrote:
> On Fri, 18 Feb 2011 00:44:07 +1300, Oliver Jowett wrote:
>> On 18/02/11 00:37, rsmogura wrote:
>>> On Fri, 18 Feb 2011 00:06:22 +1300, Oliver Jowett wrote:
>>>> On 17/02/11 23:18, rsmogura wrote:
>>>>> Yes, but driver checks number of declared out parameters and number of
>>>>> resulted parameters (even check types of those), to prevent
>>>>> programming
>>>>> errors.
>>>>
>>>> And..?
>>>>
>>>> Oliver
>>>
>>> And it will throw exception when result will income. If you will remove
>>> this then you will lose check against programming errors, when number of
>>> expected parameters is different that number of actual parameters. Bear
>>> in mind that you will get result set of 6 columns, but only 1 is
>>> expected. I think you can't determine what should be returned and how to
>>> fix result without signature.
>>
>> You've completely missed the point. I am not suggesting we change those
>> checks at all. I am suggesting we change how the JDBC driver translates
>> call escapes to queries so that for N OUT parameters, we always get
>> exactly N result columns, without depending on the datatypes of the
>> parameters in any way.
>>
>> Oliver
> 
> May You provide example select for this, and check behaviour with below
> procedure, too.
> 
> CREATE OR REPLACE FUNCTION p_enhance_address3(OUT address
> u_address_type, OUT i1 integer)
>   RETURNS record AS
> $BODY$
> BEGIN
>         SELECT t_author.address
>         INTO address
>         FROM t_author
>         WHERE first_name = 'George';
> i1 = 12;
> END;
> $BODY$
>   LANGUAGE plpgsql

Oh god I'm going round and round in circles repeating myself!

There are two problems.

The first problem is a plpgsql problem in that particular function. It's
broken regardless of how you call it. Here's how to fix it:

> testdb=# CREATE FUNCTION p_enhance_address4 (address OUT u_address_type) AS $$ BEGIN address := (SELECT t_author.address FROM t_author WHERE first_name = 'George'); END; $$ LANGUAGE plpgsql;
> CREATE FUNCTION
> testdb=# SELECT * FROM p_enhance_address4();
>          street         |  zip   |   city    | country |   since    | code 
> ------------------------+--------+-----------+---------+------------+------
>  ("Parliament Hill",77) | NW31A9 | Hampstead | England | 1980-01-01 |     
> (1 row)

The second problem is that the JDBC driver always generates calls in the
"SELECT * FROM ..." form, but this does not work correctly for
one-OUT-parameter-that-is-a-UDT, as seen in the example immediately
above. Here's how to do the call for that particular case:

> testdb=# SELECT p_enhance_address4();
>                         p_enhance_address4                         
> -------------------------------------------------------------------
>  ("(""Parliament Hill"",77)",NW31A9,Hampstead,England,1980-01-01,)
> (1 row)

The challenge is that the bare SELECT form doesn't work for multiple OUT
parameters, so the driver has to select one form or the other based on
the number of OUT parameters.

Any questions? (I'm sure there will be questions. Sigh.)

Oliver

Re: Fwd: [JDBC] Weird issues when reading UDT from stored function

Florian G. Pflug <fgp@phlo.org> — 2011-02-17T12:08:06Z

On Feb17, 2011, at 11:15 , Oliver Jowett wrote:
> Florian Pflug wrote:
>> On Feb17, 2011, at 01:14 , Oliver Jowett wrote:
>>> Any suggestions about how the JDBC driver can express the query to get
>>> the behavior that it wants? Specifically, the driver wants to call a
>>> particular function with N OUT or INOUT parameters (and maybe some other
>>> IN parameters too) and get a resultset with N columns back.
>> There's no sane way to do that, I fear. You could of course look up the
>> function definition in the catalog before actually calling it, but with
>> overloading and polymorphic types finding the right pg_proc entry seems
>> awfully complex.
>> Your best option is probably to just document this caveat...
> 
> Well, the JDBC driver does know how many OUT parameters there are before execution happens, so it could theoretically do something different for 1 OUT vs. many OUT parameters.

Right, I had forgotten that JDBC must be told about OUT parameter with registerOutputType()

> The problem is that currently the translation of the JDBC "{ call }" escape happens early on, well before we know which parameters are OUT parameters. Moving that translation later is, at best, tricky, so I was hoping there was one query form that would handle all cases.

Hm, now I'm confused. Even leaving the single-OUT-parameter problem aside, the JDBC statement {call f(?,?)} either translates to
  SELECT * FROM f($1)
or
  SELECT * FROM f($1, $2)
depending on whether one of the parameter is OUT. Without knowing the number of output parameters, how do you distinguish these two cases?

best regards,
Florian Pflug

Re: Fwd: [JDBC] Weird issues when reading UDT from stored function

Oliver Jowett <oliver@opencloud.com> — 2011-02-17T12:15:36Z

On 18/02/11 01:08, Florian Pflug wrote:

>> Well, the JDBC driver does know how many OUT parameters there are before execution happens, so it could theoretically do something different for 1 OUT vs. many OUT parameters.
> 
> Right, I had forgotten that JDBC must be told about OUT parameter with registerOutputType()
> 
>> The problem is that currently the translation of the JDBC "{ call }" escape happens early on, well before we know which parameters are OUT parameters. Moving that translation later is, at best, tricky, so I was hoping there was one query form that would handle all cases.
> 
> Hm, now I'm confused. Even leaving the single-OUT-parameter problem aside, the JDBC statement {call f(?,?)} either translates to
>   SELECT * FROM f($1)
> or
>   SELECT * FROM f($1, $2)
> depending on whether one of the parameter is OUT. Without knowing the number of output parameters, how do you distinguish these two cases?

Currently it always includes *all* parameters in the call, regardless of
the number of OUT parameters (as mentioned, it doesn't even know how
many OUT parameters there are at that point). As we discover OUT
parameters, we bind void types to them, and the server does the rest of
the heavy lifting. Something roughly equivalent to this:

> testdb=# PREPARE s1(void) AS SELECT * FROM p_enhance_address4($1); -- function has no IN parameters, one OUT parameter
> PREPARE
> testdb=# EXECUTE s1(null);
>          street         |  zip   |   city    | country |   since    | code 
> ------------------------+--------+-----------+---------+------------+------
>  ("Parliament Hill",77) | NW31A9 | Hampstead | England | 1980-01-01 |     
> (1 row)

Oliver

Re: Fwd: [JDBC] Weird issues when reading UDT from stored function

Radosław Smogura <rsmogura@softperience.eu> — 2011-02-17T12:18:30Z

 <snip>
>> testdb=# CREATE FUNCTION p_enhance_address4 (address OUT 
>> u_address_type) AS $$ BEGIN address := (SELECT t_author.address FROM 
>> t_author WHERE first_name = 'George'); END; $$ LANGUAGE plpgsql;
>> CREATE FUNCTION
>> testdb=# SELECT * FROM p_enhance_address4();
>>          street         |  zip   |   city    | country |   since    
>> | code
>> 
>> ------------------------+--------+-----------+---------+------------+------
>>  ("Parliament Hill",77) | NW31A9 | Hampstead | England | 1980-01-01 
>> |
>> (1 row)
>
> The second problem is that the JDBC driver always generates calls in 
> the
> "SELECT * FROM ..." form, but this does not work correctly for
> one-OUT-parameter-that-is-a-UDT, as seen in the example immediately
> above. Here's how to do the call for that particular case:
>
>> testdb=# SELECT p_enhance_address4();
>>                         p_enhance_address4
>> -------------------------------------------------------------------
>>  ("(""Parliament Hill"",77)",NW31A9,Hampstead,England,1980-01-01,)
>> (1 row)
>
> The challenge is that the bare SELECT form doesn't work for multiple 
> OUT
> parameters, so the driver has to select one form or the other based 
> on
> the number of OUT parameters.
>
> Any questions? (I'm sure there will be questions. Sigh.)
>
> Oliver

 I don't want to blame or anything similar, any idea is good, as any 
 effort as well, but if user will register one output parameter, but 
 procedure will have two will it be possible to check this? I'm little 
 lost in this nested records. If there will be no such check I suggest to 
 configure this by connection parameter, because in any way UDTs aren't 
 such popular, user should have choice to decide "I want better checks", 
 or "I need this! Everything is on my side".

Re: Fwd: [JDBC] Weird issues when reading UDT from stored function

Lukas Eder <lukas.eder@gmail.com> — 2011-02-17T17:31:04Z

Hi Oliver

There are two problems.
>
> The first problem is a plpgsql problem in that particular function. It's
> broken regardless of how you call it. Here's how to fix it [...]
>

Thanks for insisting! I missed that fact. In the end, it looked like the
same error, but you're right about the plpgsql syntax error.


> The second problem is that the JDBC driver always generates calls in the
> "SELECT * FROM ..." form, but this does not work correctly for
> one-OUT-parameter-that-is-a-UDT, as seen in the example immediately
> above. Here's how to do the call for that particular case [...]
>

Knowing these things, I think I can live with the status quo in my case. As
I'm writing a database abstraction library (http://jooq.sourceforge.net),
with generated source code, I can hide these Postgres-specific details from
end-user code easily and assemble the UDT myself when reading the 6 return
values.


> Any questions? (I'm sure there will be questions. Sigh.)
>

Thanks again for the patience! :-)