PostgreSQL能否使用用户自定义函数覆盖系统内置函数？

核心原理 #

PostgreSQL调用不带schema前缀的函数时，会按照search_path配置的schema顺序依次检索匹配的函数。系统内置函数默认存放在pg_catalog schema下，该schema默认优先级最高，因此你自建的同名函数不会被优先匹配。

具体实现步骤 #

1. 建议单独创建兼容专用schema存放Oracle适配函数，避免和业务schema混淆：

CREATE SCHEMA oracle_compat;

将你编写的符合Oracle substr逻辑的自定义函数创建在该schema下即可。
2. 调整search_path优先级，将兼容schema放在pg_catalog之前：

• 仅当前会话临时生效：
  SET search_path TO oracle_compat, pg_catalog, public;
• 对指定数据库用户永久生效：
  ALTER ROLE 你的业务用户名 SET search_path TO oracle_compat, pg_catalog, public;
• 对当前数据库所有用户永久生效：
  ALTER DATABASE 你的业务库名 SET search_path TO oracle_compat, pg_catalog, public;

关键注意事项 #

• 必须显式将pg_catalog写在search_path的自定义兼容schema之后，如果省略pg_catalog配置，PostgreSQL会自动将其优先级调整到所有schema最前，无法实现自定义函数优先调用的效果
• 该规则仅对不带schema前缀的函数调用生效，如果调用时显式指定pg_catalog.substr，仍然会调用系统原生函数
• 如果不想自行开发兼容函数，也可以直接使用pg_orafce扩展内置的Oracle兼容函数集，已经实现了对齐Oracle逻辑的substr等常用函数

效果验证 #

执行测试语句：SELECT substr('teststr', 0, 3);
如果返回结果为tes，说明自定义兼容函数已生效，原生PostgreSQL的substr对起始下标0会返回空值。

内容的提问来源于stack exchange，提问作者Mano