Override PostgreSQL internal functions

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

*[image: icons]*

[image: Email patch] <http://www.ashnik.com/>

This email may contain confidential, privileged or copyright material and
is solely for the use of the intended recipient(s).