Is it possible to override a Postgres system function with user-defined function?

I have created a user-defined function 'substr' but it works only if I specify the schema name, else it uses the system function 'substr'.

Is there a way to override the system function 'substr' and use my user-defined function 'substr' without having to use schema name?

The reason is I want the same functionality of Oracle substr in PG for the existing code base.

CodePudding user response：

Yes, you can do that, but you have to configure search_path so that it explicitly contains pg_catalog after the schema with your function:

SET search_path = myfuncs, pg_catalog;

Note that search_path can be changed by everyone, so your solution relies on nobody changing the parameter. Also, functions can be defined with a custom search_path, which would override your setting.

This could lead to surprises in combination with restoring a pg_dump, which forces search_path empty. I think that your idea is not very robust, and it would be better to name the schema explicitly.