Thread: overloading LIKE operator to handle integer + text

Hi all,

I'd like to be able to operate LIKE using as arguments an integer and
a text value.

In postgresql 9.0 the following raises an error:
# SELECT 123 LIKE '123';
ERROR:  operator does not exist: integer ~~ unknown
LINE 1: select 123 like '123';                   ^
HINT:  No operator matches the given name and argument type(s). You
might need to add explicit type casts.

This can be easily solved as the HINT suggests as:
# SELECT 123::text LIKE '123';

But I cannot touch the SQL queries generated by the application, which
generates queries like:
SELECT * from tabname WHERE "id" LIKE '%34%';

Thus I thought I might overload the LIKE operator to be able to handle
the case where args are integer and text.

So I create a function:
CREATE OR REPLACE FUNCTION public.my_like(leftop integer, rightop text)
 RETURNS boolean
 LANGUAGE sql
AS $function$
SELECT $1::text LIKE $2;
$function$

But then I can't create the operator:
#  CREATE OPERATOR "LIKE" (LEFTARG=integer, RIGHTARG=text, PROCEDURE=my_like);
ERROR:  syntax error at or near "("
LINE 1: CREATE OPERATOR "LIKE" (LEFTARG=integer, RIGHTARG=text, PROC...

Any ideas what I'm missing? The doc's examples have a "(".

TIA,
Thalis K.

> -----Original Message-----
> From: pgsql-general-owner@postgresql.org [mailto:pgsql-general-
> owner@postgresql.org] On Behalf Of Thalis Kalfigkopoulos
> Sent: Tuesday, October 30, 2012 3:55 PM
> To: pgsql-general@postgresql.org
> Subject: [GENERAL] overloading LIKE operator to handle integer + text
>
> Hi all,
>
> I'd like to be able to operate LIKE using as arguments an integer and a
text
> value.
>
> In postgresql 9.0 the following raises an error:
> # SELECT 123 LIKE '123';
> ERROR:  operator does not exist: integer ~~ unknown
> LINE 1: select 123 like '123';                   ^
> HINT:  No operator matches the given name and argument type(s). You
> might need to add explicit type casts.
>
> This can be easily solved as the HINT suggests as:
> # SELECT 123::text LIKE '123';
>
> But I cannot touch the SQL queries generated by the application, which
> generates queries like:
> SELECT * from tabname WHERE "id" LIKE '%34%';
>
> Thus I thought I might overload the LIKE operator to be able to handle the
> case where args are integer and text.
>
> So I create a function:
> CREATE OR REPLACE FUNCTION public.my_like(leftop integer, rightop text)
> RETURNS boolean  LANGUAGE sql AS $function$ SELECT $1::text LIKE $2;
> $function$
>
> But then I can't create the operator:
> #  CREATE OPERATOR "LIKE" (LEFTARG=integer, RIGHTARG=text,
> PROCEDURE=my_like);
> ERROR:  syntax error at or near "("
> LINE 1: CREATE OPERATOR "LIKE" (LEFTARG=integer, RIGHTARG=text,
> PROC...
>
> Any ideas what I'm missing? The doc's examples have a "(".
>
> TIA,
> Thalis K.
>

"LIKE" is apparently not an operator but a special SQL construct.  However,
from the error message it appears that internally LIKE is transformed to the
~~ operator (which is defined as being equivalent).  You should try
overloading the ~~ operator (and probably the NOT version equivalents - see
section 9.7.1 in the PostgreSQL 9.2 documentation)  and see if that works.

David J.

On 10/30/2012 03:14 PM, David Johnston wrote:

> "LIKE" is apparently not an operator but a special SQL construct.

Almost. :) The real problem is this, from the manual:

"The operator name is a sequence of up to NAMEDATALEN-1 (63 by default)
characters from the following list:

+ - * / < > = ~ ! @ # % ^ & | ` ?

Grandparent might also want to mark his function as immutable so its
execution can be optimized. Aside from that, this works:

CREATE OPERATOR ~~ (LEFTARG=integer, RIGHTARG=text, PROCEDURE=my_like);

However, this only creates it in the current schema. If this really
should be usable for everyone, this should be the final statement:

CREATE OPERATOR PUBLIC.~~ (LEFTARG=integer, RIGHTARG=text,
PROCEDURE=my_like);

--
Shaun Thomas
OptionsHouse | 141 W. Jackson Blvd. | Suite 500 | Chicago IL, 60604
312-444-8534
sthomas@optionshouse.com

______________________________________________

See http://www.peak6.com/email_disclaimer/ for terms and conditions related to this email