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

2011-10-03 18:12 keltezéssel, Boszormenyi Zoltan írta:
> 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
>
> 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
>
> create cast (varchar as varchar(255)) with inout;
> ERROR: source data type and target data type are the same
>
> This cast already exists:
> create cast (varchar as varchar(255)) with function pg_catalog.varchar(varchar, integer,
> boolean);
> ERROR: cast from type character varying to type character varying already exists
>
> I know, explicit cast to ::varchar(255) in the function solves this problem.
> But I would like to know why isn't the type conversion from unlimited varchar
> to varchar(255) invoked in the pl/pgsql function?

Two additions:

create function myfunc1() returns setof varchar(255) as $$
begin
return query select (t1 || t2)::varchar from mytest;
end;$$ language plpgsql;

select * from myfunc1();
myfunc1
---------
(0 rows)

create or replace function myfunc2(out id integer, out t varchar(255)) returns setof
record as $$
begin
return query select mytest.id, (t1 || t2)::varchar from mytest;
end;$$ language plpgsql;

select * from myfunc2();
id | t
----+---
(0 rows)

Only the conversion from anonymous record to composite type
causes a problem, individual output parameters or single-value return
values get the implicit cast.

>
> Thanks in advance,
> Zoltán Böszörményi
>

--
----------------------------------
Zoltán Böszörményi
Cybertec Schönig & Schönig GmbH
Gröhrmühlgasse 26
A-2700 Wiener Neustadt, Austria
Web: http://www.postgresql-support.de
http://www.postgresql.at/