Obtaining schema name of PostgreSQL stored procedures

Question

I'm trying to obtain schema names of stored procedures in PostGRESQL as I'm writing a query to scan through all existing stored procedures on the server and return those that contain a specific keyword.

I couldn't find any references to schema names in pg_catalog.pg_proc. Is there any other place where I could obtain this information?

This is my current code so far -

select  'Stored Procedure' as ObjectType,
        NULL  as schemaname,
        p.proname as ObjectName,
        u.usename as ObjectOwner
from pg_catalog.pg_proc p
join pg_catalog.pg_user u 
    on p.proowner = u.usesysid
where 1=1
and p.prosrc like '%keyword%'

Answer 1

You can cast pronamespace to get the schema name. This modified version of your query should do the trick:

select  'Stored Procedure' as ObjectType,
        p.pronamespace::regnamespace::text as schemaname,
        p.proname as ObjectName,
        u.usename as ObjectOwner
from pg_catalog.pg_proc p
join pg_catalog.pg_user u
    on p.proowner = u.usesysid
where 1=1
and p.prosrc like '%keyword%'

For the record, this query matches both functions and stored procedures; you would have to filter on prokind='p' to limit it to just stored procedures.