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?
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/