Re: Re: BUG #4078: ERROR: operator does not exist: numeric = character varying

On Mon, Oct 13, 2008 at 09:10:41PM -0400, Tom Lane wrote:
> Eric Haszlakiewicz <erh(at)swapsimple(dot)com> writes:
> > I created this, which seems to solve the problem:
>
> > create function casting_eq_operator(integer, "char")
> > returns boolean as 'begin
> > return $1 = cast ($2 as integer);
> > end;' language plpgsql immutable strict;
>
> > CREATE OPERATOR = (PROCEDURE = casting_eq_operator,
> > LEFTARG = integer , RIGHTARG = "char",
> > COMMUTATOR = =, NEGATOR = !=, HASHES, MERGES
> > );
>
> > Can this be included by default?
>
> No. Even if we desired to reverse the decision about not having
> implicit casting behavior, this definition of the operator would not be
> appropriate because it provides the opposite of the old behavior.
> The pre-8.3 behavior would have been to cast the integer to text and
> apply a textual comparison; which gives different comparison behavior,
> eg leading zeroes in the string would affect the result. Not to mention
> that the cast to integer in this definition would fail outright if the
> string didn't look like an integer.
>
> A large part of the reasoning for getting rid of the implicit casts
> was exactly that it's not very clear what a comparison of this sort
> should act like, and most people who are accidentally invoking it
> haven't thought that through either.

hmm.. I was thinking that a comparison between a number and a string
would cause an error if the string wasn't parseable. Now that I
think about it more, I can see that having it just return false might
be reasonable too.

>
> (Some other problems: I'm pretty sure you meant to refer to text or
> varchar not "char"; you referenced commutator and negator operators
> without defining them; this operator certainly does not hash, and
> I don't think it merges either, though maybe you could make the latter
> work if you'd provided all the requisite btree-opfamily infrastructure.)

Yeah, you're right. I didn't think that through entirely.
Sorry for the noise.

eric