Re: Redefining LIKE operator

Pablo Sajnn <psajnin(at)bejerman(dot)com(dot)ar> writes:
> I need the LIKE operator to be case insensitive.
> What I did is to redefine the ~~ so it behaves as the ~~* operator:

> DROP OPERATOR ~~ (name, text);
> CREATE OPERATOR ~~ ( leftarg=name, rightarg=text, procedure=nameiclike,
> negator='!~~');

> The operator is created, but when I execute a query, LIKE is still being
> case-sensitive.

There are five operators named '~~'; are you sure your LIKE is invoking
the "name" variant? I'd bet against that, if your tables are declared
with MSSQL-compatible datatypes.

I'd suggest renaming the standard operators out of the way, rather than
dropping them altogether. Also your CREATE is missing the selectivity
estimators. But in general this should work if you do it right.

regards, tom lane