Postgres Pro
Downloads
Home   >   mailing lists

Thread: Override PostgreSQL internal functions

Override PostgreSQL internal functions

From
Sameer Kumar
Date:
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

icons

 

Email patch

 

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

Attachment

Re: Override PostgreSQL internal functions

From
Vik Fearing
Date:
On 02/26/2014 10:36 AM, Sameer Kumar wrote:
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.

You would be much better off to use the citext extension and typing your columns appropriately.

http://www.postgresql.org/docs/current/static/citext.html
-- 
Vik

Re: Override PostgreSQL internal functions

From
Sameer Kumar
Date:

On Wed, Feb 26, 2014 at 6:54 PM, Vik Fearing <vik.fearing@dalibo.com> wrote:
You would be much better off to use the citext extension and typing your columns appropriately.

http://www.postgresql.org/docs/current/static/citext.html

That might be a huge effort while migrating from another database and might also introduce application level changes for type casting.


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

icons

 

Email patch

 

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

Attachment

Re: Override PostgreSQL internal functions

From
David Johnston
Date:
Sameer Kumar wrote
> 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:

Basic idea:

Create a schema called " overrides "
Put your function in " overrides "
Make sure the search path includes " overrides " before " pg_catalog "
This way lookup will always resolve to your function before it resolves to
the system supplied function.

David J.





--
View this message in context:
http://postgresql.1045698.n5.nabble.com/Override-PostgreSQL-internal-functions-tp5793670p5793703.html
Sent from the PostgreSQL - novice mailing list archive at Nabble.com.

Re: Override PostgreSQL internal functions

From
Sameer Kumar
Date:

Thanks!
I think that should work well. Don't know why it never occured to me.

Regards
Sameer

PS: Sent from my Mobile device. Pls ignore typo n abb

Re: Override PostgreSQL internal functions

From
Vik Fearing
Date:
On 02/26/2014 03:03 PM, Sameer Kumar wrote:

On Wed, Feb 26, 2014 at 6:54 PM, Vik Fearing <vik.fearing@dalibo.com> wrote:
You would be much better off to use the citext extension and typing your columns appropriately.

http://www.postgresql.org/docs/current/static/citext.html

That might be a huge effort while migrating from another database and might also introduce application level changes for type casting.

Not nearly as much effort as what you're trying to do, but good luck.

David's solution is what I would have suggested had he not said it already.
-- 
Vik

Re: Override PostgreSQL internal functions

From
Sameer Kumar
Date:

On Thu, Feb 27, 2014 at 9:42 AM, Vik Fearing <vik.fearing@dalibo.com> wrote:
Not nearly as much effort as what you're trying to do, but good luck.

Scanning the application code to add casting info could be a pain and I may not be sure if I have done that for all the function calls/string comparisons. Moreover I need to repeat this process from scratch for each customer/migration project.

Whereas if I have something handy (a set of override functions) I can do it easily for every project. Moreover, PostgreSQL has a very organized list of functions and decent documentation about arguments and return type. This makes my task easy for building this one time set of override function. :-)

David's solution is what I would have suggested had he not said it already.

Thanks both of you for your help and suggestions :-)

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

icons

 Email patch

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

Attachment

Re: Override PostgreSQL internal functions

From
Simon Riggs
Date:
On 27 February 2014 01:42, Vik Fearing <vik.fearing@dalibo.com> wrote:
> On 02/26/2014 03:03 PM, Sameer Kumar wrote:
>
>
> On Wed, Feb 26, 2014 at 6:54 PM, Vik Fearing <vik.fearing@dalibo.com> wrote:
>>
>> You would be much better off to use the citext extension and typing your
>> columns appropriately.
>>
>> http://www.postgresql.org/docs/current/static/citext.html
>
>
> That might be a huge effort while migrating from another database and might
> also introduce application level changes for type casting.
>
>
> Not nearly as much effort as what you're trying to do, but good luck.
>
> David's solution is what I would have suggested had he not said it already.

David's solution seems to be the right solution for general schema override.

Vik's comments about using citext seem good for the specific case
mentioned. Using citext has all the same issues as overriding existing
operators, so its better to use something that works than something
home-grown.

--
 Simon Riggs                   http://www.2ndQuadrant.com/
 PostgreSQL Development, 24x7 Support, Training & Services