Hello
current plpgsql cannot detect early some errors based on unknown
casting. Other problem is IO casting.
The reason is an late casting:
current_code is some like:
val = eval_expr(query, &result_type);
if (result_type != expected_type)
{ str = convert_to_string(val, result_type); val = convert_from_string(val, expected_type);
}
I propose for types with typmod -1 early casting - etc casting to
target type on planner level. We cannot use this method for defined
typmod, because we would to raise exception for following situation:
varchar(3) := 'ABCDE'; - casting do quietly necessary truncation
This should be everywhere, where we know an target type.
What this needs?
* new SPI function SPI_prepare_function_with_target_types, that calls
coerce_to_target_type function.
* add new field to PLpgSQL_expr - Oid *target_type
benefits:
* possible some strict mode - that use only predefined cast functions
(without I/O general conversion)
* some minor speed
* fix some strange issues
http://archives.postgresql.org/pgsql-hackers/2008-12/msg01932.php
* consistent behave with SQL
postgres=# create function fot(i numeric) returns date as $$begin
return i;end; $$ language plpgsql;
CREATE FUNCTION
Time: 2,346 ms
postgres=# select extract (year from fot(20081010));
CONTEXT: PL/pgSQL function "fot" line 1 at RETURNdate_part
----------- 2008
(1 row)
what is nonsense
postgres=# select extract(year from 20081010::numeric::date);
ERROR: cannot cast type numeric to date
LINE 1: select extract(year from 20081010::numeric::date); ^
Issues:
* current casting functions doesn't raise exception when we lost some detail :(
postgres=# select 'abc'::varchar(2), 10.22::numeric(10,1), 10.22::integer;varchar | numeric | int4
---------+---------+------ab | 10.2 | 10
(1 row)
* current integer input functions are too simple:
ERROR: invalid input syntax for integer: "10.00"
LINE 1: select int '10.00'; ^
Possible enhancing:
when target variable has attypmod, then we could add to plan IO
casting via some new functions - this should simplify plpgsql code -
any casting should be removed
Ideas, comments?
regards
Pavel Stehule