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(at)iol(dot)ie