Override PostgreSQL internal functions - Mailing list pgsql-novice
Hi,
Is there a way I can override (not overload) PostgreSQL internal functions e.g. replace()?
By default replace() will replace string based on case sensitivity. I want to override this behavior and make it case insensitive. For Operators I can do that easily as:
CREATE FUNCTION caseinsen_regexp_like(varchar, varchar) RETURNS boolean
AS $$
SELECT UPPER($1)::text ~ UPPER($2)::text;
$$
LANGUAGE sql;
CREATE OPERATOR ~(
PROCEDURE = caseinsen_regexp_like,
LEFTARG = varchar,
RIGHTARG = varchar,
NEGATOR = !~
);
CREATE FUNCTION caseinsen_regexp_not_like(varchar, varchar) RETURNS boolean
AS $$
SELECT UPPER($1)::text !~ UPPER($2)::text;
$$
LANGUAGE sql;
CREATE OPERATOR !~(
PROCEDURE = caseinsen_regexp_not_like,
LEFTARG = varchar,
RIGHTARG = varchar,
NEGATOR = ~
);
This will make sure that the where clause match is case insensitive.
select name from employee;
output
-----------
abc
xyz
Steve1
steve2
STEVE3
sTEVE4
select name from employee where name~'Steve%'
output:
-----------
Steve1
steve2
STEVE3
sTEVE4
select name from employee where name !~ 'Steve%'
outout
-----------
abc
xyz
I know I could have used ~* but the purpose here was to override the existing operator. I can do a similar thing for = and <>.
Best Regards,
Sameer Kumar | Database Consultant
ASHNIK PTE. LTD.
101 Cecil Street, #11-11 Tong Eng Building, Singapore 069533
M: +65 8110 0350 T: +65 6438 3504 | www.ashnik.com
This email may contain confidential, privileged or copyright material and is solely for the use of the intended recipient(s).
Attachment
pgsql-novice by date: