Обсуждение: Pg/SQL returning composite type

Поиск
Список
Период
Сортировка

Pg/SQL returning composite type

От
"P. Broennimann"
Дата:
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

Re: Pg/SQL returning composite type

От
Raymond O'Donnell
Дата:
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