greater_wms/reset.sql

DO $$
DECLARE
    seq_record RECORD;
    v_table_name TEXT;
    max_id BIGINT;
    sql_text TEXT;
BEGIN
    -- 遍历所有public模式下的序列
    FOR seq_record IN SELECT sequencename FROM pg_sequences WHERE schemaname = 'public'
    LOOP
        -- 检查是否为特定序列，需要单独映射
        IF seq_record.sequencename = 'batch_log_id_seq' THEN
            v_table_name := 'batch_log_from_container_log';
        ELSIF seq_record.sequencename = 'batch_log_detail_logs_id_seq' THEN
            v_table_name := 'batch_log_from_container_log_detail_logs';
        ELSE
            -- 从序列名提取表名（假设序列名以_id_seq结尾）
            v_table_name := substring(seq_record.sequencename from '(.*)_id_seq');
        END IF;
        
        -- 如果成功获取表名
        IF v_table_name IS NOT NULL THEN
            -- 检查表是否存在
            IF EXISTS (SELECT 1 FROM information_schema.tables 
                      WHERE table_schema = 'public' AND table_name = v_table_name) THEN
                BEGIN
                    -- 动态查询最大ID值
                    EXECUTE 'SELECT COALESCE(MAX(id), 0) + 1 FROM ' || quote_ident(v_table_name) INTO max_id;
                    
                    -- 生成并执行重置序列的SQL
                    sql_text := 'SELECT setval(' || quote_literal(seq_record.sequencename) || ', ' || max_id || ', false);';
                    EXECUTE sql_text;
                    
                    -- 输出成功日志，显示序列名、表名和最大值
                    RAISE NOTICE '重置序列: % -> 对应表: % 最大值: %', seq_record.sequencename, v_table_name, max_id;
                    
                EXCEPTION WHEN OTHERS THEN
                    -- 处理错误情况
                    RAISE NOTICE '错误重置序列: % - %', seq_record.sequencename, SQLERRM;
                END;
            ELSE
                -- 输出跳过日志，显示序列名和对应的表名
                RAISE NOTICE '跳过序列: % (对应表 % 不存在)', seq_record.sequencename, v_table_name;
            END IF;
        ELSE
            -- 输出不符合命名约定的日志
            RAISE NOTICE '跳过序列: % (不符合命名约定)', seq_record.sequencename;
        END IF;
    END LOOP;
    
    RAISE NOTICE '序列重置完成';
END $$;