I am trying to write a function that returns a record consisting of a single column of a user defined type so that I can ingest them in java using the ResultSet.getObject(int colNumber, Class<T> class) method. If my function returns more than one column it works as expected, but if my function has just the one column then it expands the UDT back out into its constiuent parts so that I cannot use the desired method. e.g.
create type test as (x int, y int);
create or replace function test()
returns table(test test)
as $$
select (42, 7)::test;
$$ language sql;
create or replace function test2()
returns table(test test, junk boolean)
as $$
select (42, 7)::test, false;
$$ language sql;
Running test2() gives me data in the format I can ingest via the desired method but test() does not. How can I specify the return value of test to have the desired result?
postgres=# select * from test2();
test | junk
--------+------
(42,7) | f
(1 row)
postgres=# select * from test();
x | y
----+---
42 | 7
(1 row)
What I want is ...
postgres=# select * from test();
test
--------
(42,7)
(1 row)
I think * is the problem. We use star for each column.
select test()
use this.