How to get a list of stored procedures using a specific table in PostgreSQL?

Question

In PostgreSQL (9.3) is there a simple way to get a list of the stored procedures that use a specific table?

I'm changing several tables and need to fix the stored procedures that use them.

Answer 1

Functions which have text 'thetable' in their body.

The query returns function name, line number and line containg 'thetable':

select *
from (
    select proname, row_number() over (partition by proname) as line, textline
    from (
        select proname, unnest(string_to_array(prosrc, chr(10))) textline
        from pg_proc p
        join pg_namespace n on n.oid = p.pronamespace
        where nspname = 'public'
        and prosrc ilike '%thetable%'
        ) lines
    ) x
    where textline ilike '%thetable%';

Functions which have any argument or return value of type associated with thetable.

For example:

create function f2(rec thetable)...
create function f1() returns setof thetable... 

This query gives name, return type and types of arguments of the functions:

with rtype as (
    select reltype 
    from pg_class
    where relname = 'thetable')
select distinct on (proname) proname, prorettype, proargtypes
from pg_proc p
join pg_namespace n on n.oid = p.pronamespace
cross join rtype
where nspname = 'public'
and (
    prorettype = reltype 
    or reltype::text = any(string_to_array(proargtypes::text, ' '))) 

Of course, you can merge the queries into one. I am using them for different purposes.