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.
3
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
ESCAPEclause. To match the escape character itself, write two escape characters.
It’s also possible to select no escape character by writingESCAPE ''.
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'