CREATE OR REPLACE FUNCTION "public"."update_sequence_values"() 
  RETURNS TABLE("var_table_schema" text, "var_table_name" text, "old_max_id" int4, "new_max_id" int4) AS $$
DECLARE
  table_rec RECORD;
  max_id INTEGER;
BEGIN
  -- 创建临时表以保存更新的序列值
    DROP TABLE IF EXISTS temp_sequence_updates;
  CREATE TEMP TABLE temp_sequence_updates (
    var_table_schema TEXT,
    var_table_name TEXT,
    old_max_id INTEGER,
    new_max_id INTEGER
  );

  -- 遍历指定模式下所有包含自增主键的表
  FOR table_rec IN (
        SELECT DISTINCT(table_schema), table_name, is_identity, column_default, column_name FROM information_schema.columns 
        WHERE table_schema = 'public'AND (is_identity= 'YES' OR column_default Like 'nextval%')
    ) LOOP
        -- 获取表的主键最大值
    EXECUTE format('SELECT MAX(%I) FROM %I.%I;', table_rec.column_name, table_rec.table_schema, table_rec.table_name) INTO max_id;
    -- 更新序列
    IF max_id IS NOT NULL THEN
      EXECUTE format('SELECT setval(pg_get_serial_sequence(''%I'', ''%s''), %s, false);', table_rec.table_name, replace(table_rec.column_name, '"','') , max_id + 1);
      -- 记录更新操作的日志信息
      INSERT INTO temp_sequence_updates (var_table_schema, var_table_name, old_max_id, new_max_id) VALUES (table_rec.table_schema, table_rec.table_name , max_id, max_id + 1);
        ELSE
            -- 主键最大值为空则不更新
            INSERT INTO temp_sequence_updates (var_table_schema, var_table_name, old_max_id, new_max_id) VALUES (table_rec.table_schema, table_rec.table_name , null, null);
    END IF;
  END LOOP;

  -- 返回更新操作的日志信息
  RETURN QUERY SELECT * FROM temp_sequence_updates ORDER BY old_max_id;
END;

$$ LANGUAGE plpgsql;
    
SELECT * FROM update_sequence_values();