> -----Original Message----- > From: pgsql-general-owner@xxxxxxxxxxxxxx [mailto:pgsql-general- > owner@xxxxxxxxxxxxxx] On Behalf Of Thalis Kalfigkopoulos > Sent: Tuesday, October 30, 2012 3:55 PM > To: pgsql-general@xxxxxxxxxxxxxx > Subject: 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. -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
