List stored functions that reference a table in PostgreSQL

Just a quick and simple question: in PostgreSQL, how do you list the names of all stored functions/stored procedures using a table using just a SELECT statement, if possible? If a simple SELECT is insufficient, I can make do with a stored function.

My question, I think, is somewhat similar to this other question, but this other question is for SQL Server 2005:
List of Stored Procedure from Table

(optional) For that matter, how do you also list the triggers and constraints that use the same table in the same manner?

Solution 1:

SELECT  p.proname
FROM    pg_catalog.pg_namespace n
JOIN    pg_catalog.pg_proc p
ON      p.pronamespace = n.oid
WHERE   n.nspname = 'public';

Solution 2:

SELECT  proname, prosrc
FROM    pg_catalog.pg_namespace n
JOIN    pg_catalog.pg_proc p
ON      pronamespace = n.oid
WHERE   nspname = 'public';

Solution 3:

If you are using psql, try \df

From the man page:

Tip
To look up functions taking arguments or returning values of a specific type, use your pager's search capability to scroll through the \df output.

Running \set ECHO_HIDDEN will reveal what \df is running behind the scenes.

Solution 4:

Same as @quassnoi and @davidwhthomas, except I added the argument names in there:

SELECT  proname, proargnames, prosrc 
FROM    pg_catalog.pg_namespace n
JOIN    pg_catalog.pg_proc p
ON      pronamespace = n.oid
WHERE   nspname = 'public';

If the purpose behind listing the functions is to clean them up or iterate a new function with a changing params list, you will frequently need to drop functions:

DROP FUNCTION <name>(<args>);

By adding proargnames, I am able to construct the applicable function name for the drop.

Additionally, it's nice to see a more complete picture when evaluating the functions.

Solution 5:

You can use the standard information_schema schema to get metadata about your database (it's in the SQL standard, so it should work the same way in different database systems). In this case you want information_schema.routines.