Обсуждение: Pg/SQL returning composite type
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
Thanks & cheers,
Peter
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