Redefining LIKE operator

--On Friday, March 14, 2003 09:06:29 -0300 Pablo Sajnín
<psajnin@bejerman.com.ar> wrote:

Hi,

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.
Am I doing anything wrong?
Is it not possible to redefine this operator?
I am using the 7.2.2 version.

What's wrong with ILIKE?

Thanks in advance

Pablo Sajnin
psajnin@bejerman.com.ar

---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql.org so that your
message can get through to the mailing list cleanly

--
Larry Rosenman http://www.lerctr.org/~ler
Phone: +1 972-414-9812 E-Mail: ler@lerctr.org
US Mail: 1905 Steamboat Springs Drive, Garland, TX 75044-6749

The problem is that I am migrating a system from MS Sql Server (which was
installed with the case-insensitive option) and the system will have to
still keep working with this RDBMS after the migration, so I have to change
the code as less as possible...

-----Mensaje original-----
De: Larry Rosenman [mailto:ler@lerctr.org]
Enviado el: Viernes, 14 de Marzo de 2003 09:16 a.m.
Para: Pablo Sajn�n; pgsql-general@postgresql.org
Asunto: Re: [GENERAL] Redefining LIKE operator

--On Friday, March 14, 2003 09:06:29 -0300 Pablo Sajn�n
<psajnin@bejerman.com.ar> wrote:

Hi,

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.
Am I doing anything wrong?
Is it not possible to redefine this operator?
I am using the 7.2.2 version.

What's wrong with ILIKE?

Thanks in advance

Pablo Sajnin
psajnin@bejerman.com.ar

---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql.org so that your
message can get through to the mailing list cleanly

--
Larry Rosenman http://www.lerctr.org/~ler
Phone: +1 972-414-9812 E-Mail: ler@lerctr.org
US Mail: 1905 Steamboat Springs Drive, Garland, TX 75044-6749

Pablo Sajn�n <psajnin@bejerman.com.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