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?
SELECT p.proname FROM pg_catalog.pg_namespace n JOIN pg_catalog.pg_proc p ON p.pronamespace = n.oid WHERE n.nspname = 'public';
SELECT proname, prosrc FROM pg_catalog.pg_namespace n JOIN pg_catalog.pg_proc p ON pronamespace = n.oid WHERE nspname = 'public';
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.
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.
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.
Excluding the system stuff:
select proname from pg_proc where proowner <> 1;
Have a look at my recipe. It reads functions and triggers. It is based on informations from: Extracting META information from PostgreSQL (INFORMATION_SCHEMA)
For retrieving the argument types of the functions, which are required when referencing the function in ALTER -- using oldevectortypes worked well for me.