sequence处理用例

####查询所有表对应列上的sequence
方法一:
  select *
  from (
    select table_schema,
           table_name,
           column_name,
           pg_get_serial_sequence(format('%I.%I', table_schema, table_name), column_name) as col_sequence
    from information_schema.columns
    where table_schema  in ('public', 'upayfn')
  ) t
  where col_sequence is not null;
  
  
  

####查询所有表对应列上的sequence
方法二:

  select *
  from (
    select table_schema,
           table_name,
           column_name,
           pg_get_serial_sequence(table_schema||'.'||table_name, column_name) as col_sequence
    from information_schema.columns
    where table_schema  in ('public', 'upayfn')
  ) t
  where col_sequence is not null;
  
  

####批量生成查询最大值的sql(进查询对应列上有sequence的列)  
  
with sequences as (
  select *
  from (
    select table_schema,
           table_name,
           column_name,
           pg_get_serial_sequence(table_schema||'.'||table_name, column_name) as col_sequence
    from information_schema.columns
    where table_schema  in ('public', 'upayfn')
  ) t
  where col_sequence is not null
)
  select table_schema, table_name, column_name, col_sequence,
          'select max('||column_name||') from '||table_schema||'.'||table_name||';' as max_val
  from sequences;
  
  
##生成批量重置sequence为表列上的最大值
 with sequences as (
  select *
  from (
    select table_schema,
           table_name,
           column_name,
           pg_get_serial_sequence(table_schema||'.'||table_name, column_name) as col_sequence
    from information_schema.columns
    where table_schema  in ('public', 'upayfn')
  ) t
  where col_sequence is not null
)
  select table_schema, table_name, column_name, col_sequence,
  'select max('||column_name||') from '||table_schema||'.'||table_name||';' as max_val,
  'select setval('''||col_sequence||'''   ,(select max('||column_name||') from '||table_schema||'.'||table_name||'))' as  reset_seq
  from sequences;
  
  
####生成批量重置sequence为表列上的最大值
 with sequences as (
  select *
  from (
    select table_schema,
           table_name,
           column_name,
           pg_get_serial_sequence(table_schema||'.'||table_name, column_name) as col_sequence
    from information_schema.columns
    where table_schema  in ('public', 'upayfn')
  ) t
  where col_sequence is not null
)
  select
  'select setval('''||col_sequence||'''   ,(select max('||column_name||') from '||table_schema||'.'||table_name||'));' as  reset_seq
  from sequences;