Skip to content

Instantly share code, notes, and snippets.

@GeorgeErickson
Last active December 30, 2015 10:19
Show Gist options
  • Save GeorgeErickson/7815427 to your computer and use it in GitHub Desktop.
Save GeorgeErickson/7815427 to your computer and use it in GitHub Desktop.
I needed to rename columns that were in every table to switch from Rails to Sails.js.
CREATE OR REPLACE FUNCTION bulk_rename_column(old_name text, new_name text) RETURNS SETOF text AS $$
DECLARE
r RECORD;
BEGIN
FOR r IN SELECT table_schema, table_name, column_name
FROM information_schema.columns As c
WHERE c.table_schema NOT IN('information_schema', 'pg_catalog')
AND c.column_name = old_name
ORDER BY c.table_schema, c.table_name, c.column_name
LOOP
EXECUTE 'ALTER TABLE ' || quote_ident(r.table_schema) || '.' || quote_ident(r.table_name) || ' RENAME "' || r.column_name || '" TO ' || quote_ident(new_name) || ';';
RETURN NEXT format('%s.%s %s', r.table_schema, r.table_name, r.column_name);
END LOOP;
RETURN;
END;
$$ LANGUAGE plpgsql;
select * from bulk_rename_column('updated_at', 'updatedAt');
select * from bulk_rename_column('created_at', 'createdAt');
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment