List stored functions that reference a table in PostgreSQL
SqlPostgresqlMetaprogrammingSql Problem Overview
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:
https://stackoverflow.com/questions/119679/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?
Sql Solutions
Solution 1 - Sql
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 - Sql
SELECT proname, prosrc
FROM pg_catalog.pg_namespace n
JOIN pg_catalog.pg_proc p
ON pronamespace = n.oid
WHERE nspname = 'public';
Solution 3 - Sql
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 - Sql
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 - Sql
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.
Solution 6 - Sql
Excluding the system stuff:
select proname from pg_proc where proowner <> 1;
Solution 7 - Sql
Have a look at my recipe. It reads functions and triggers. It is based on informations from: Extracting META information from PostgreSQL (INFORMATION_SCHEMA)
Solution 8 - Sql
Please change the schema_name and table_name in the below query:
SELECT n.nspname AS schema_name
, p.proname AS function_name
, pg_get_function_arguments(p.oid) AS args
, pg_get_functiondef(p.oid) AS func_def
FROM pg_proc p
JOIN pg_namespace n ON n.oid = p.pronamespace
AND n.nspname = 'schema_name'
AND p.prosrc like '%table_name%'
Since the table name is case sensitive, so need to define the exact table name.
Solution 9 - Sql
For retrieving the argument types of the functions, which are required when referencing the function in ALTER -- using oldevectortypes worked well for me.