Thread: 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 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

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.

>                        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

>                        regards, tom lane
>