On Sat, Oct 20, 2012 at 11:44 PM, Muhammad Altaf <mmalik_altaf@yahoo.com> wrote:
> 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?
>
> Thanks,
> Altaf Malik
Seems consistent with the way postgresql would do things. What were
you expecting it to return ?