Adam BookLaugh
BookLaugh
/ set_sequence_values.py
Last active
October 27, 2016 11:26
PostgreSQL procedure for updating all sequences used as default values for table columns. Once called, it will update values of sequences to max value present in column plus 5 (why 5? So you can find out which sequences were altered). Function returns list of names of updated sequences.
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| CREATE OR REPLACE FUNCTION set_seq_vals(schema_name TEXT) | |
| RETURNS TEXT[] | |
| AS $$ | |
| schemaname = schema_name if schema_name else 'public' | |
| plpy.execute("SET SEARCH_PATH=%s,public" % schemaname) | |
| tables = plpy.execute("SELECT tablename FROM pg_tables WHERE schemaname = '%s'" % schemaname); | |
| altered_seqs = [] | |
| for t in tables: | |
| cols = plpy.execute("SELECT column_name, column_default, data_type, is_nullable FROM information_schema.columns WHERE table_name = '%s' AND data_type in ('integer', 'bigint') AND column_default IS NOT NULL" % t["tablename"]) | |
| for c in cols: |