PostgreSQL list of all tables/functions referencing a column name

Question

I am trying to clean up a Postgres database and create some consistency in the naming conventions.

I would like generate a list of the functions where the columns exist as a checklist to work from.

I was wondering if there was an easier way than dumping the function definitions to a file and searching each column individually?

Nick Barnes · Accepted Answer · 2018-12-21 04:27:44Z

3

To find tables and views containing ColumnName:

SELECT attrelid::regclass
FROM pg_attribute
WHERE attname = 'columnname'

To find functions which mention ColumnName somewhere in their code:

SELECT oid::regprocedure, prosrc
FROM pg_proc
WHERE prosrc ~* '\yColumnName\y'

(~* is a case-insensitive regex match, \y represents a word boundary.)

answered Dec 21, 2018 at 4:27

Nick Barnes

21.8k5 gold badges63 silver badges70 bronze badges

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

1 Answer 1