Thread: Struct Support

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 ?

> -----Original Message-----
> From: pgsql-jdbc-owner@postgresql.org [mailto:pgsql-jdbc-
> owner@postgresql.org] On Behalf Of Dave Cramer
> Sent: Sunday, October 21, 2012 6:42 AM
> To: Muhammad Altaf
> Cc: pgsql-jdbc@postgresql.org
> Subject: Re: [JDBC] Struct Support
>
> 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 ?
>

Specifically, if the only output from a function call is a single output
type, and that function call is performed in the "FROM" clause of a query,
then the result is treated just like a view or table reference and each
top-level component of the composite type is treated as an individual
column.  This applies to the set-returning form ...RETURNS TABLE ( c city
)... as well.  It also applies to using an explicit OUT parameter in the
function parameter declaration.

The cleanest way to get the behavior you want is to call the function
directly from the SELECT list and not via the FROM clause:

SELECT udt_f2(100, '(4, "Washington")') AS city_composite

When done this way the entire result of the function is interpreted as a
single column.

You could also write:

SELCET (udt_f2.*)::city AS city_composite FROM udt_f2(...)

Adding additional output columns will also cause the composite type to
revert to being a single output column.

I do see where there could be value in declaring whether the function should
return a single composite column or an expanded "table" but the expanded
form is very useful as well and at this point would remain the default no
matter what changes are considered.

Something like:

CREATE FUNCTION name(...)
RETURNS composite_type
SINGLE COLUMN ONLY
AS $$
....
$$ LANGUAGE ...
;

David J.