Find procedure name in all other procedures

Question

procedure 1:

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

procedure 2:

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

Finding the procedure name in other procedures:

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

output:

pro_1
pro_2

Didn't understand why has pro_2 got listed even though pro.1 is mentioned instead of pro_1.

Answer 1

From 9.7. Pattern Matching on the LIKE operator:

An underscore (_) in pattern stands for (matches) any single character; a percent sign (%) matches any sequence of zero or more characters.

So '%pro_1%' matches 'pro.1' as well and 'pro_1', 'pro#1' and many more, with any other character on that position. To match a literal underscore, escape it: %pro\_1%

The default escape character is the backslash but a different one can be selected by using the ESCAPE clause. To match the escape character itself, write two escape characters.
It's also possible to select no escape character by writing ESCAPE ''.

pg_get_functiondef() isn't just the body, so you're also matching against the name of the routine. If the example you showed doesn't have a typo, it's not pro1 in the body of the first procedure pro_1 that got matched by the pattern, but rather the name of that procedure.

If you're just checking for presence of a substring, it might be an overkill to use either plain SQL or POSIX regex engine. You can use a much simpler strpos()<>0:
_{demo at db<>fiddle}

select proname 
from pg_proc 
where strpos(lower(pg_get_functiondef(oid)),'pro_1')<>0 and prokind = 'p'