Re: Inconsistency: varchar is equivalent to varchar(255) and also not equivalent? - Mailing list pgsql-general

2011-10-03 19:31 keltezéssel, Boszormenyi Zoltan írta:
> 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.

They actually don't. Let's add a row ensuring t1||t2 is longer than 255:

=# insert into mytest (t1, t2) values (repeat('a', 250), repeat('b', 250));
INSERT 0 1
=# select length(t1), length(t2) from mytest;
 length | length
--------+--------
    250 |    250
(1 row)

=# select length(myfunc1) from myfunc1();
 length
--------
    500
(1 row)

=# select length(t) from myfunc2();
 length
--------
    500
(1 row)

So, although the functions look like they accept and would
perform the implicit type conversion, they actually do not. But:

=# select 'aaaa'::varchar(3);
 varchar
---------
 aaa
(1 row)

I would expect either the accepted type conversion implicitly
truncates or gives me a runtime error just like this below:

zozo=# insert into mytest (t1, t2) values (now()::text, '');
INSERT 0 1
zozo=# select t1::timestamp from mytest where id = 2;
             t1
----------------------------
 2011-10-03 21:23:52.423667
(1 row)

zozo=# select t1::timestamp from mytest;
ERROR:  invalid input syntax for type timestamp:

"aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa"

I forgot to report the version:

=# select version();

version
-----------------------------------------------------------------------------------------------------------------
 PostgreSQL 9.0.4 on x86_64-redhat-linux-gnu, compiled by GCC gcc (GCC) 4.6.0 20110530
(Red Hat 4.6.0-9), 64-bit
(1 row)

This is on Fedora 15. I just checked, it's the same on 9.1.1 compiled fresh.

Of course, the explicit type conversion truncates correctly.

=# select id, length((t1 || t2)::varchar(255)) from mytest;
 id | length
----+--------
  1 |    255
  2 |     29
(2 rows)

Now I start to think that pl/pgsql simply lacks some type checks and
should be stricter.

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