Is there any way to find terms (column name, for example) that are present in triggers/functions/procedures in a Postgresql database?

I'm investigating an operation that is assigning a "null" value to a column and I suspect it's related to a database trigger, but I can't find it.

CodePudding user response：

I'm assuming you don't have your database schema code in version-control handily available.

If that is the case, then the simplest approach is probably pg_dump --schema-only and grep or your faviourite text editor. This would be my preferred option because you'll want to see the body of functions in any case.

You can always query the text of sql/plpgsql/plpython etc functions (they are in pg_proc) but that's probably more fiddly than just dumping the schema.

Finally, plpgsql-check offers a dependency listing tool for functions. That can show you what tables and other functions any particular plpgsql function uses.

If any of your functions generate dynamic SQL then of course you can't guarantee to be able to find the name of your table/column. In which case you could try to add an AFTER trigger (called "zzz_something" so it runs last) which can check if your column is NULL and if so log current_query() and any other details that might point you in the right direction.