Re: Inconsistency: varchar is equivalent to varchar(255) and also not equivalent?

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.