In Postgres, how do I re-validate ("type-check") functions and procedures after tables are altered?

user_1473169 0
Viewed 426

In Postgres, functions are validated on creation: for instance, accessing a column that doesn't exist results in a helpful error, even before the function is run. But altering a table doesn't verify consistency with function definitions, i.e., dropping a column used by some function doesn't result in an error. Is there any way of re-running the same verifications that are already applied to functions at creation time, without deleting and re-creating every function?

stored-functions alter postgresql stored-procedures function
1 Answers

There is nothing built-in, but there is an extension that can do that:

plpgsql_check

From the project's readme:

Features

  • check fields of referenced database objects and types inside embedded SQL
  • using correct types of function parameters
  • unused variables and function argumens, unmodified OUT argumens
  • partially detection of dead code (due RETURN command)
  • detection of missing RETURN command in function
  • try to identify unwanted hidden casts, that can be performance issue like unused indexes
  • possibility to collect relations and functions used by function
  • possibility to check EXECUTE stmt agaist SQL injection vulnerability
user_3303150
Related