Обсуждение: 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