List stored functions that reference a table in PostgreSQL

sql

database-management

postgresql

sql-queries

byAnton Shumikhin·Nov 7, 2024

SELECT 
  proname 
FROM 
  pg_proc p 
JOIN 
  pg_namespace n ON p.pronamespace = n.oid 
WHERE 
  prosrc LIKE '%your_table_name%'
  AND n.nspname = 'public';

To identify relevant PostgreSQL functions for a specific table, have a dive into the pg_proc catalogue. Here, we'll screen out the function source code prosrc for mentions of your_table_name. Alas, don't forget to responsively tailor n.nspname as per your schema needs.

Handling false positives

You don't want to cast your net wide and haul unwanted stuff. To avoid getting bogged down by false positives, primarily from table names appearing in comments, we tune our query:

SELECT 
  proname 
FROM 
  pg_proc p 
JOIN 
  pg_namespace n ON p.pronamespace = n.oid 
WHERE 
  prosrc ~ ('[^a-z_]' || 'your_table_name' || '[^a_z_]') 
  AND n.nspname = 'public';

The updated query wields the mighty sword of regular expressions to scout only for table name mentions not embedded within larger strings. Because, catching a "table_in_comment" is like catching a "ghost_in_shell"!

Databases are like spiders, always spinning more interconnected webs. So, your quest may encounter triggers and constraints. To find these critters, use our trusty friends, the pg_trigger and pg_constraint catalogs, joining them with pg_class and fishing by table name.

Investigating routine details

Meet the dependable wizard information_schema.routines. Armed with this cross-database trove, inspect routine names, types, and character. Also, thanks to proargnames within pg_proc, you can nerf functions as easily as summoning them. It's almost like playing an RPG - just with less dragons and more databases.

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

Holding this map, you can gently navigate the labyrinth of code and come out unscathed. And should you need to DROP FUNCTION, you've got it in your cheatsheet!