Обсуждение: Inconsistency: varchar is equivalent to varchar(255) and also not equivalent?

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/

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/

On Oct 3, 2011, at 10:12 AM, Boszormenyi Zoltan wrote:

> But I would like to know why isn't the type conversion from unlimited varchar
> to varchar(255) invoked in the pl/pgsql function?

What if t1 || t2 is longer than 255? You need to explicitly specify.

--
Scott Ribe
scott_ribe@elevated-dev.com
http://www.elevated-dev.com/
(303) 722-0567 voice

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'realso 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
preventaccidental 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'mguessing 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.

2011-10-03 19:17 keltezéssel, Scott Ribe írta:
> On Oct 3, 2011, at 10:12 AM, Boszormenyi Zoltan wrote:
>
>> But I would like to know why isn't the type conversion from unlimited varchar
>> to varchar(255) invoked in the pl/pgsql function?
> What if t1 || t2 is longer than 255? You need to explicitly specify.

Yes, but then explicit casting would be needed everywhere
and it's not the case. My question is more like: why the
record -> record type conversion doesn't try to match
individual elements of the two record types?

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

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/