I have a database with a bunch of tables that all have "username" columns (actual varchars, not foreign keys). Two or three times a year, I end up having to change that for a user (usernames are email addresses, and users change their email accounts, or switch jobs or whatever). Previously I'd relied on a bunch of update statements that blindly attempted to change that field in all tables, but I finally decided today to write a script to automate that task.
Finding all the tables with username as a column:
select c.relname from pg_class c join pg_attribute a on (c.oid = a.attrelid) join pg_roles r2 on (c.relowner = r2.oid) where a.attname = 'username' and c.relkind = 'r' and r2.rolname = 'pkf';
Time to write the script (with error checking, tests, etc): about an hour. Time I usually spend changing usernames without the script: about 10 minutes a year. So, a net win, or net loss?