This function resets all sequences to the max value + 1 in their respective tables.


CREATE OR REPLACE FUNCTION util_reset_sequences () RETURNS SETOF VARCHAR as $$
DECLARE
myrow RECORD;
max_id INTEGER;
seq_name VARCHAR;
mytable VARCHAR;
myquery VARCHAR;
BEGIN
FOR myrow IN select * from information_schema.columns where column_default like 'nextval(%' LOOP
mytable := myrow.table_schema || '.' || myrow.table_name;
myquery := 'SELECT coalesce(max(' || myrow.column_name || '),0)+1 FROM ' || mytable;
--RAISE NOTICE 'query = %', myquery;
EXECUTE myquery INTO max_id;
seq_name := SUBSTRING(substr(myrow.column_default, 10) FROM E'[[:alnum:]_\.]*');
----seq_name := SUBSTRING(myrow.column_default FROM '\\''([[:alnum:]_]*)');
--RAISE NOTICE 'seq_name = %', seq_name;
EXECUTE 'ALTER SEQUENCE ' || seq_name || ' RESTART ' || max_id;
RETURN NEXT '"' || seq_name || '","' || myrow.table_name || '","' || myrow.column_name || '",' || cast(max_id as varchar);
END LOOP;
RETURN;
END;
$$ language 'plpgsql';