Abp使用Postgres,迁移数据后序列重置修复

在网上找了一些 postgres迁移数据后,序列丢失(为1)的问题..发现不太适用Abp的项目.

找了一些资料,Abp的约束,用了一些投机取巧的方法,总算实现了功能.

现在把代码贴出来

do --check seq not in sync
$$
declare
 _r record;
 _i bigint;
 _m bigint;
begin
  for _r in (
    Select
      DISTINCT(constraint_column_usage.table_name) as tablename,
      constraint_column_usage.column_name as idname,
			pg_class.relname as seqname,
            columns.table_schema as schamename
      from information_schema.constraint_column_usage, information_schema.columns,pg_class 
      where constraint_column_usage.table_schema ='public' AND
			 pg_class.relkind='S' AND
			 
      columns.table_schema = 'public' AND columns.table_name=constraint_column_usage.table_name
      AND constraint_column_usage.column_name = columns.column_name
			and concat(constraint_column_usage.table_name ,'_Id_seq') = pg_class.relname
      --AND columns.column_default is not null
   ) loop
		 execute format('select last_value from %I."%s"',_r.schamename,_r.seqname) into _i;
			execute format('select max(%I) from %I.%I',_r.idname,_r.schamename,_r.tablename) into _m;
			if coalesce(_m,0) > _i then
				raise info '%',concat('changed: ',_r.tablename||'_Id_seq',' from:',_i,' to:',_m+1);
				execute format('alter sequence %I."%s" restart with %s',_r.schamename,_r.seqname,_m+1);
			end if;
    
  end loop;

end;
$$
;

在网上找的demo中,添加了pg_class,由于找不到pg_class和table的关联,根据abp命名的特性,在代码的第20行"_Id_seq",使用了字符串的拼接进行了匹配..这里需要注意一下.