On 3 Oct 2011, at 18:12, Boszormenyi Zoltan wrote:
> Hi,
>
> here is the testcase:
>
> create type mytype as (id integer, t varchar(255));
> create table mytest (id serial, t1 varchar(255), t2 varchar(255));
> create or replace function myfunc () returns setof mytype as $$
> begin
> return query select id, (t1 || t2)::varchar from mytest;
> end;$$ language plpgsql;
>
> Now the problem is:
>
> select * from myfunc();
> ERROR: structure of query does not match function result type
> DETAIL: Returned type text does not match expected type character varying(255) in column 2.
> CONTEXT: PL/pgSQL function "myfunc" line 2 at RETURN QUERY
Yes, of course. It's safe to cast a varchar(255) to a varchar, but the other way around it could get truncated.
> But the types are said to be the same:
>
> create cast (varchar as varchar(255)) without function;
> ERROR: source data type and target data type are the same
They are the same type, but one version has a length constraint and the other does not.
The above is not a safe cast without specifying what to do with varchars that contain more than 255 chars. But...
you'realso specifying the cast without function.
> create cast (varchar as varchar(255)) with inout;
> ERROR: source data type and target data type are the same
If I understand the meaning of inout type casts correctly, this also doesn't create a safe type-cast. It doesn't
preventaccidental truncating.
If that's why the errors occur, they're at least a bit misleading. I can't say I have been creating casts so far, so
I'mguessing a bit here.
If you create a cast WITH function, does that work?
Alban Hertroys
--
Screwing up is an excellent way to attach something to the ceiling.
