From: | Thalis Kalfigkopoulos <tkalfigo(at)gmail(dot)com> |
---|---|
To: | pgsql-general(at)postgresql(dot)org |
Subject: | overloading LIKE operator to handle integer + text |
Date: | 2012-10-30 19:54:48 |
Message-ID: | CAEkCx9GGQbPLAdJd+agdLqv0mYYJ9VjpZ_cKHgJX0t8MmAWA1w@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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.
From | Date | Subject | |
---|---|---|---|
Next Message | David Johnston | 2012-10-30 20:14:57 | Re: overloading LIKE operator to handle integer + text |
Previous Message | David Johnston | 2012-10-30 19:11:58 | Re: Average Balance "life" |