Re: Pg/SQL returning composite type

On 16/11/2012 09:46, P. Broennimann wrote:
> Hi there
> 
> I have in schema "core":
> 
>    CREATE OR REPLACE FUNCTION core.f_read
>    (
>      ...
>    )
>    RETURNS core.c_result_type AS
>    $$
>    declare
>      c_result core.c_result_type%rowtype;
>    begin
>      ...
>      return c_result;  
>    end
>    ...
> 
>    CREATE TYPE core.c_result_type AS (
>      a_value        text,
>      a_result_code  integer,
>      a_result       text,
>      a_time         integer
>    );
> 
> And in schema "canu":
> 
>    CREATE OR REPLACE FUNCTION canu.f_read
>    (
>      ...
>    )
>    RETURNS canu.c_result_type AS
>    $$
>    declare
>      c_result canu.c_result_type%rowtype;
>    begin
>      select core.f_read(...) into c_result; 
>      return c_result;  
>    end
>    ...
> 
>    CREATE TYPE canu.c_result_type AS (
>      a_value        text,
>      a_result_code  integer,
>      a_result       text,
>      a_time         integer
>    );
> 
> When calling core.f_read() the result is fine. When
> calling canu.f_read() I get all values back together/concatenated in the
> first "a_value" field!?
> 
> What am I doing wrong here? I am using PG 9.1.6 under Ubuntu 10.04/64

I think in canu.f_read you need to do:

  select * from core.f_read(...) into c_result;

The way to call a function returning a composite type is with with
"select * from my_function() ..." in order to get the individual
columns; otherwise they come back concatenated, as you saw.

HTH,

Ray.

-- 
Raymond O'Donnell :: Galway :: Ireland
rod@iol.ie