proposal: early casting in plpgsql
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 RETURN
date_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
Pavel Stehule <pavel.stehule@gmail.com> writes:
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:
What existing coding habits will this break? People have long been
accustomed to use plpgsql for end-runs around SQL casting behavior,
so I'm not really convinced by the idea that "make it more like SQL"
is automatically a good thing.
Also, it seems bizarre and inconsistent that it would work one way
for variables with a typmod and an entirely different way for those
without. How will you explain that to users who never heard of a
typmod?
regards, tom lane
2009/5/28 Tom Lane <tgl@sss.pgh.pa.us>:
Pavel Stehule <pavel.stehule@gmail.com> writes:
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:What existing coding habits will this break?
I don't know about any. Actually we don't have "variant datatype", so
this should not impact on existing applications.
People have long been
accustomed to use plpgsql for end-runs around SQL casting behavior,
so I'm not really convinced by the idea that "make it more like SQL"
is automatically a good thing.
for typmod others then -1 we should to use IO cast - but we should to
check, if it's one from known casts.
without "strict mode" this should be fully compatible (if we could to
expect so our casting functions are correct).
Also, it seems bizarre and inconsistent that it would work one way
for variables with a typmod and an entirely different way for those
without. How will you explain that to users who never heard of a
typmod?
Now I thing so this should be solved well too. We need two kind of
casting functions - what we have - CASTs with INOUT and CASTs with
functions. For variables with typmod we have to call CASTs with INOUT.
Show quoted text
regards, tom lane
Pavel Stehule <pavel.stehule@gmail.com> writes:
for typmod others then -1 we should to use IO cast - but we should to
check, if it's one from known casts.
I still think it's fundamentally wrong to be treating typmod -1 so
differently from other typmods. If this behavior is sane at all then
it should work in both cases.
regards, tom lane
2009/5/28 Tom Lane <tgl@sss.pgh.pa.us>:
Pavel Stehule <pavel.stehule@gmail.com> writes:
for typmod others then -1 we should to use IO cast - but we should to
check, if it's one from known casts.I still think it's fundamentally wrong to be treating typmod -1 so
differently from other typmods. If this behavior is sane at all then
it should work in both cases.
ok, I am sorry, you have a true. It should to add implicit cast (only
when it's necessary)
regards
Pavel Stehule
Show quoted text
regards, tom lane