Re: operator varchar = integer

Tom Lane schrieb:
> David Fetter <david@fetter.org> writes:
>
>> On Mon, May 05, 2008 at 05:26:40PM +0200, Daniel Schuchardt wrote:
>>
>>> our db has about 500 functions, 300 tables, 1000 indexes, 1200 Views
>>> that all use implicit casting.  and: everything is working fine ;-)
>>> :-P
>>>
>
>
>> How do you know?  8.3 removed the implicit casts precisely because
>> they were producing results that could most generously be describe as
>> "surprising."
>>
>
> This should not be underestimated.  From the reports we've seen so far,
> a very sizable fraction of people who find this kind of failure with 8.3
> find out that their application was doing something unexpected in the
> cases where it happened.  If you've got as many failures as you suggest,
> I'd be willing to bet that some of them are bugs in your code, not just
> reliance on an implicit feature.
>
>             regards, tom lane
>
>
yes true your right but lets make a calculation:

our application runs about 6 Years now so lets say there are 5% queries
that run still in a mistake caus of auto cast.
We have to check about 1200 views, 500 functions, every runtime created
query and so on. Lets say 95% of them run fine now. Now lets think we
check and rewrite all of them. If you let out the time (our customers
wont pay for such changes, they will have more errors the first time
than the last years), we should expect about X % errors because of the
changes (wrong parantheses and so on). So it would be a horror for us.
thats the problem.

examples:

RAISE EXCEPTION "error during validation % :",
'ks:"'||ks||'"@"'||loopdate||'"';  (KS is DECLARED VARCHAR, LoopDate is
a TIMESTAMP);

stempz:=Round(SUM(COALESCE(ba_efftime, timediff(ba_anf, CAST(now() AS
TIMESTAMP(0) WITHOUT TIME ZONE))))) FROM bdea WHERE
timestamp_to_date(ba_anf)=current_date AND ba_ks=oks AND
*ba_ix||'~'||ba_op* IN (SELECT *a2_ab_ix||'~'||a2_n* FROM ab2_wkstplan
JOIN ab2 ON a2_id=a2w_a2_id WHERE a2w_oks=oks AND a2w_ks=ks AND
a2w_planweek=week);

this are integer fields. but they are unique with "*a2_ab_ix||'~'||a2_n*".

another one:

here we need to add 4 CASTS.
CREATE OR REPLACE FUNCTION date_to_yearmonth_dec(TIMESTAMP) RETURNS
INTEGER AS $$
DECLARE R INTEGER;
BEGIN
 IF extract(month FROM $1)<11 THEN
     R:=extract(year FROM $1)||0||extract(month FROM $1)-1;
 ELSE
     R:=extract(year FROM $1)||extract(month FROM $1)-1;
 END IF;
 RETURN  R;
END$$LANGUAGE plpgsql IMMUTABLE;