PostgreSQL: How to list all stored functions that access specific table

Question

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.

Your table name is in the column prosrc, you have to query that column using a like or regex. This has nothing to do with the schema where the function is located. — Frank Heikens
– Frank Heikens, Commented Jun 3, 2015 at 9:00
Tables have columns, not fields... BTW, information_schema.ROUTINE_TABLE_USAGE? — jarlh
– jarlh, Commented Jun 3, 2015 at 9:01
@jarlh postgresql doesn't have information_schema.routine_table_usage. — Radek Postołowicz
– Radek Postołowicz, Commented Jun 3, 2015 at 10:10

Tom-db · Accepted Answer · 2015-06-03 10:13:35Z

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'

answered Jun 3, 2015 at 10:13

Tom-db

6,9584 gold badges33 silver badges51 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Radek Postołowicz Over a year ago

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.

Collectives™ on Stack Overflow

PostgreSQL: How to list all stored functions that access specific table

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related