Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

Introduction:

PostgreSQL database with several hundred of stored functions, including obsolete, not used etc.

Problem

I need to find out all the stored functions that have any relationship to the table X - as I want to change the table structure. Some of them might be not used, so I can't do that just by looking through the code.

The solution I have ATM is running psql's \df+ and grepping output, but I'd prefer more database-like solution, i.e. by using information schema. This will definitely be a repetitive task and I'd like to have it nice and clean.

Any suggestions?

share|improve this question
add comment

migrated from stackoverflow.com Jul 25 '13 at 15:11

This question came from our site for professional and enthusiast programmers.

1 Answer

up vote 4 down vote accepted

It has to be noted, that the body of a function is just stored as string. There is no list of referenced objects. That's different from views, for instance, where actual links to referenced tables are saved.

This query to the catalog tables should do the job.

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   (SELECT oid, * FROM pg_proc p WHERE NOT p.proisagg) p
JOIN   pg_namespace n ON n.oid = p.pronamespace
WHERE  n.nspname !~~ 'pg_%'
AND    n.nspname <> 'information_schema'
AND    pg_get_functiondef(p.oid) ~ '\mmy_table\M';

I am excluding aggregate functions and all functions from system tables from the search.

\m and \M mark the beginning and end of a word in the regexp match.

share|improve this answer
    
Yep... it's not totally robust, in the sense that it won't find EXECUTE expressions like 'mm_'||name_parameter, and it won't cope correctly with quoted names like "my""table"" or with case-folding, but it'll do most of what most people will want. –  Craig Ringer Jul 25 '13 at 5:38
    
@CraigRinger: Yeah, dynamic queries with EXECUTE are almost impossible to cover. But case-folding can be covered with ~* instead of ~ - or any other case-insensitive pattern-matching. –  Erwin Brandstetter Jul 25 '13 at 5:43
    
So long as the operator isn't crazy enough to actually create tables named "MyTable" and MyTable, at least... and honestly, that's a "well, that might be allowed but it isn't smart" move. –  Craig Ringer Jul 25 '13 at 6:12
    
Thanks for the answer! I actually don't use dynamic table name construction anywhere and all table names are lowercase. –  Sergey Kudriavtsev Jul 25 '13 at 11:16
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.