为何PL/pgSQL查询含pro_1的存储过程时pro_2被列出？

存储过程定义 #

• 存储过程pro_1：

create or replace procedure pro_1()
as 
$$
begin   
    raise info 'pro1';
end
$$
language plpgsql;

• 存储过程pro_2：

create or replace procedure pro_2()
as 
$$
begin   
    raise info 'pro.1';
    raise info 'pro.2';
end
$$
language plpgsql;

执行的查询语句 #

用于查找包含pro_1的存储过程：

select proname 
from pg_proc 
where lower(pg_get_functiondef(oid)) like '%pro_1%' and prokind = 'p'

查询输出结果 #

pro_1
pro_2

原因及解决方案 #

问题出在LIKE操作符的通配符规则上：SQL中LIKE里的_是匹配单个任意字符的通配符，而非普通下划线。你的查询条件%pro_1%会匹配所有包含pro+任意单个字符+1的内容，pro_2定义里的pro.1正好符合这个模式（.被_匹配），所以被误命中。

要精确匹配下划线_，可以用以下几种修正方式：

方法1：使用转义符匹配字面下划线 #

select proname 
from pg_proc 
where lower(pg_get_functiondef(oid)) like '%pro\_1%' and prokind = 'p'

方法2：自定义转义符 #

select proname 
from pg_proc 
where lower(pg_get_functiondef(oid)) like '%pro!_1%' escape '!' and prokind = 'p'

方法3：用正则表达式精准匹配 #

select proname 
from pg_proc 
where lower(pg_get_functiondef(oid)) ~ 'pro_1' and prokind = 'p'

以上修正后的查询只会返回真正包含pro_1的存储过程pro_1。

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