List stored functions that reference a table in PostgreSQL

user_144270 0
Viewed 82688

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?

postgresql metaprogramming sql
9 Answers

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.

user_30494190
Related