2

for a given table how can i find all functions that uses ANY fields from that table? say i have student table with (studentid, studentname)

i want a list of all functions and triggers that uses studentid and studentname from student.

my question is similar to List stored functions that reference a table in PostgreSQL but the code given there isn't working... n.nspname which is given in the condition there is related to Schema not to table.

Community
  • 1
  • 1
John
  • 1,724
  • 6
  • 25
  • 48

1 Answers1

3

Perhaps is not very precise, but this query should work.

SELECT routine_schema, routine_name 
FROM information_schema.routines 
WHERE routine_definition ~ 'student' 
AND routine_definition ~ 'studentid' 
AND routine_definition ~ 'studentname';

Depending on how you wrote the procedure, can you apply more precise regex expressions. In example, if you always wrote tables and columns in this form: table.column could you use this expression:

... WHERE routine_definition ~ 'student\.studentid' 
AND routine_definition ~ 'student\.studentname'
Tom-db
  • 6,528
  • 3
  • 30
  • 44
  • Since sql and plpgsql is case insensitive it's wise to use case insensitive operator `~*` to search. Also please keep in mind that function can depend on table using dynamic executed sql which may be very difficult to identify without human review. – Radek Postołowicz Jun 03 '15 at 10:33