【openGauss】openGauss根据序列命名规则批量刷序列的值为表主键字段的最大值

---

/* openGauss根据序列命名规则批量刷序列的值为表主键字段的最大值 */
/* 切换到要刷的数据库的schema下，执行下面的SQL，把查询结果复制出来，粘贴到另外的窗口执行 */
with config as (
  -- 序列值为主键字段的最大值加 1000，可自行调整下面这个参数
  select 1000 as add_count
)
,t as (
select substring(version() from '^[^ ]+') as database_type
      ,ic.table_schema as schema
      ,ic.table_name
      ,tab_com.description as table_comment
      ,ic.ordinal_position as column_num
      ,ic.column_name
      ,case
         when ic.data_type = 'bigint' 
           then 'INT8'
         when ic.data_type in ('nvarchar2') 
           then upper(ic.data_type) || '(' || ic.character_maximum_length || ')'
         when ic.data_type in ('character varying') 
           then 'VARCHAR(' || ic.character_maximum_length || ')'
         when ic.data_type = 'timestamp without time zone'
           then 'TIMESTAMP(' || ic.datetime_precision || ')'
         when ic.data_type = 'time without time zone'
           then 'TIME(' || ic.datetime_precision || ')'
         when ic.data_type = 'numeric' 
           then 'NUMERIC(' || ic.numeric_precision || ',' || ic.numeric_scale || ')'
         when ic.data_type = 'text' 
           then 'TEXT'
         when ic.data_type = 'date' 
           then 'DATE'
         else ic.data_type
       end as data_type
      ,col_com.description as column_comment
      ,ic.column_default
      ,ic.is_nullable
      ,case 
         when pc.conname is null then null::text
         else 'Y'
       end as pkey
  from information_schema.columns ic
  join pg_class c
    on ic.table_name = c.relname
  join pg_namespace n
    on c.relnamespace = n.oid
  left join pg_description tab_com
    on tab_com.objoid = c.oid
   and tab_com.objsubid = 0
  left join pg_description col_com
    on col_com.objoid = c.oid
   and col_com.objsubid = ic.ordinal_position
  left join (
    SELECT conname, conrelid , unnest(conkey) as column_num
      FROM pg_constraint) as pc
    on pc.conrelid = c.oid
   and pc.column_num = ic.ordinal_position
 where ic.table_catalog = CURRENT_CATALOG
   and ic.table_schema = CURRENT_SCHEMA
   and ic.table_name !~ '^act\_'
   and n.nspname = CURRENT_USER
   and c.relkind in ('r','p')
   -- 把分区表过滤掉
   -- 查结尾不是"p+4位数字"的，例如：p2023
   and ic.table_name !~* '^.*[p][0-9]{4}$'
   -- 查结尾不是"p+5位数字"的，例如：p20231
   and ic.table_name !~* '^.*[p][0-9]{5}$'
   -- 查结尾6位字符不是"others"的
   and ic.table_name !~* '^.*[others]{6}$'
   -- 查结尾5位字符不是"other"的
   and ic.table_name !~* '^.*[other]{5}$'
   -- 查结尾不是数字或者不是_bak的表名
   and (ic.table_name !~ '^.*[0-9]{1}.*$' and ic.table_name !~ '^.*\_bak$')
 order by ic.table_name, ic.ordinal_position)
select 'select setval(''' || sequencename || ''',(select COALESCE(max(' ||
       column_name || '),0) from ' || table_name || ') + ' ||
       (select add_count from config)
       || ', true);' as sequence_scripts
      ,sequencename
  from (select /* 自己调整序列命名规则 */
               -- 序列命名规则：表名中的 “_t” 换 “_s”
               -- substr(table_name, 1, length(table_name)-1) || 's' as sequencename
               -- 序列命名规则：“seq_” + 表名
               'seq_' || table_name as sequencename  
              ,table_name, column_name -- , * 
          from t
         where pkey = 'Y'
           and table_name ~ '.*_t$') t1
 where t1.sequencename in (
     select sequence_name from db_sequences)
 order by sequencename
;