Hi Dave,
I am in process of implementing java.sql.Struct to support composite types as OUT/IN parameters in postgres JDBC Driver. I am almost done with that, but while writing test cases, I came across an issue with return values. The Struct parameters are successfully being passed and returned as OUT parameters (I have tested that with multiple out and in parameters of type Struct as well). But when it comes to returning a struct value, server returns two fields in RowDescription which makes driver believe that these are actually two parameters. Initially I thought I am doing something wrong at the Driver level, but pgAdminIII showed the same behavior. I'll show both behaviors in pgAdminIII.
SQL Script:
create type city as (city_id int4, city_name text);
create type address as (street_address text, city city);
create or replace function udt_f1(a in varchar, b out city, c out address) returns RECORD AS $$
DECLARE
c_city city;
BEGIN
select 1,a into b;
select 2, 'City Name' into c_city;
select 'Street address',c_city into c;
END;$$ language 'plpgsql';
create or replace function udt_f2(a in int4, b in city) returns city AS $$
declare
c_city city;
BEGIN
select a,b.city_name into c_city;
return c_city;
END;$$ language 'plpgsql';
Now, in pgAdmin, call udt_f1
select * from udt_f1('Test City');
This will display two columns with appropriate city and address details.
Now call udt_f2
select * from udt_f2(100, '(4, "Washington")');
Instead of displaying a single city column, it displays two columns. The first as integer (city_id) and the second being text (city_name).
Can you please confirm I am not correct and something needs to be done while calling this function to get it working?
