Pages

Friday, June 15, 2012

Reset all sequences' current values + PostgreSQL

Following function allows to reset starting values of all sequences in particular PostgreSQL schema. In data migration process, this function can be used to reset all sequence values after importing data to destination database.
CREATE OR REPLACE FUNCTION seq_update()
  RETURNS void AS
$BODY$
Declare
tab1 varchar;
col1 varchar;
seqname1 varchar;
maxcolval integer;
ssql varchar;
BEGIN
FOR tab1, col1, seqname1 in Select distinct constraint_column_usage.table_name, 
constraint_column_usage.column_name,
replace(replace(columns.column_default,'''::regclass)',''),'nextval(''','')
 From information_schema.constraint_column_usage, information_schema.columns
 where constraint_column_usage.table_schema ='public' AND 
   columns.table_schema = 'public' 
    AND columns.table_name=constraint_column_usage.table_name
AND constraint_column_usage.column_name = columns.column_name
AND columns.column_default is not null 
AND constraint_column_usage.table_name not in ('user', 'usermodulespages')
--AND constraint_column_usage.table_name = 'role'
order by 1 
LOOP
ssql := 'select max(' || col1 || ') from ' || tab1 ;
RAISE NOTICE 'SQL : %', ssql;
execute ssql into maxcolval;
RAISE NOTICE 'max value : %', maxcolval;
EXECUTE 'alter sequence ' || seqname1 ||' restart  with ' || maxcolval;
END LOOP;
END;
$BODY$
LANGUAGE plpgsql VOLATILE
Following command can be used to run the function
select * from seq_update()