Search text of all functions in PGAdmin

Question

Using PostgreSQL with pgAdmin, and was wondering if there is a way to search ALL of the functions of a database for a particular text.

Is this possible?

In my case, I was specifically looking to see which functions used the `security definer` parameter, but couldn't find anything when I searched (with @Andreas's code) because those parameters aren't stored in prosrc. So, I ran `psql -E` argument to see what query it used under the hood for `\df+` and modified that query for the security column. A similar strategy could be used for other procedure attributes. — combinatorist, Jul 18 '22 at 22:03

Andreas · Accepted Answer · 2021-10-14T15:49:44.700

57

Something like this should work:

select proname, prosrc from pg_proc where prosrc like '%search text%';

see How to display the function, procedure, triggers source code in postgresql?

edited Oct 14 '21 at 15:49

answered Dec 02 '14 at 19:55

Andreas

4,937
2
25
35

7

You can also use ilike instead of like to search for case-insensitive text – Slye Jan 17 '19 at 08:16

score 5 · Answer 2 · answered Mar 08 '21 at 12:08

5

If schema info is required too (we work with many):

select
    nspname,
    proname,
    prosrc 
from pg_catalog.pg_proc pr
join pg_catalog.pg_namespace ns on ns.oid = pr.pronamespace
where prosrc ilike '%search text%'

answered Mar 08 '21 at 12:08

steevee

2,238
1
21
16

score 1 · Answer 3 · answered Dec 07 '21 at 18:04

Answers posted by @Andreas and @steevee didn't work out for me, so I had to do the following way:

First enabled extended display by running \x
Then \df+ to list down all stored procedures (in less mode by enabling extended display above) then press / key to search for a keyword.

Search text of all functions in PGAdmin

3 Answers3