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