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're also 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 prevent accidental 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'm guessing 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. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
