Warning: is_dir(): open_basedir restriction in effect. File(/tmp) is not within the allowed path(s): (/data/web/virtuals/98151/virtual) in /data/web/virtuals/98151/virtual/www/subdom/postgresql/wp-content/plugins/wp-simple-firewall/src/lib/vendor/fernleafsystems/wordpress-services/src/Core/Fs.php on line 465

Warning: Cannot modify header information - headers already sent by (output started at /data/web/virtuals/98151/virtual/www/subdom/postgresql/wp-content/plugins/wp-simple-firewall/src/lib/vendor/fernleafsystems/wordpress-services/src/Core/Fs.php:465) in /data/web/virtuals/98151/virtual/www/subdom/postgresql/wp-content/plugins/disable-xml-rpc-pingback/disable-xml-rpc-pingback.php on line 51
Repair referenced sequences on tables - PostgreSQL Candies

Lately I had to repair one interesting problem. One colleague created staging schema and copied existing table structures but did not change sequences referenced in default values for IDs. So I had to repair it. I also had to set properly current value of staging sequences. So I created following script:

do $$
declare
	_rec record;
	_query_orig text;
	_seq_orig text;
	_seq_value_orig bigint;
	_query_new text;
	_seq_new text;
	_seq_value_new bigint;
	_pos int;
	_query text;
	_ret text;
begin
	for _rec in (
	SELECT table_schema, table_name, column_name, column_default from
	information_schema.columns where column_default is not null
	and lower(column_default) like 'nextval(''bi.%'
	and table_schema = 'bi_staging'
	order by column_default, table_schema, table_name) 
	loop
		raise notice '%: >>> %.%.% -> %', clock_timestamp(), _rec.table_schema, _rec.table_name, _rec.column_name, _rec.column_default;
		_seq_orig := split_part(split_part(_rec.column_default,'''', 2),'''', 1);
		_query_orig := 'select last_value from '||_seq_orig;
		execute _query_orig into _seq_value_orig;
		raise notice '%: % -> %', clock_timestamp(), _query_orig, _seq_value_orig;
		_seq_new := replace(_seq_orig,'bi.','bi_staging.');
		_query_new := 'select last_value from '||_seq_new;
		execute _query_new into _seq_value_new;
		raise notice '%: % -> %', clock_timestamp(), _query_new, _seq_value_new;
		
		if _seq_value_orig > _seq_value_new then
			_query := 'SELECT setval('''||_seq_new||''', '||_seq_value_orig||')';
			raise notice '%: query: %', clock_timestamp(), _query;
			execute _query into _ret;
			raise notice '%: result: %', clock_timestamp(), _ret;
		end if;
		
		_query := 'alter table "'||_rec.table_schema||'"."'||_rec.table_name||'" alter column "'||_rec.column_name||
		'" set default nextval('''||_seq_new||'''::regclass)';
		raise notice '%: query: %', clock_timestamp(), _query;
		execute _query;
	end loop;
end;
$$ language plpgsql;