Peter Eisentraut, April 1 2008
> Am Dienstag, 1. April 2008 schrieb rupesh:
> > ERROR: operator does not exist: numeric = character varying at character
> > 675
> > HINT: No operator matches the given name and argument type(s). You might
> > need to add explicit type casts.
> > (0.735 sec)
> >
> >
> > This was previously working in 8.2.3 but not in 8.3
> This was an intentional change. Please read the release notes about fixing
> your code.
I read the release notes, and the idea of removing this to avoid problematic
automatic casts seems reasonable, but why not add an additional operator
so the useful cases actually still work?
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?
On a related note, I originally wrote this using 'character varying'
instead of '"char"', but that caused simple string comparisons in queries
to fail. e.g.
select * from foo where mycol = 'abc';
when mycol is a varchar column complained about "invalid input syntax
for integer". Is there a simple explanation for why it wouldn't just
try to do a plain string comparison instead of using my custom operator?
eric