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/ -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
