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