호출 예시

CALL RESET_ALL_SERIALS();

CALL RESET_ALL_SERIALS('스키마명');

CALL RESET_ALL_SERIALS('스키마명', '테이블명');

정의

-- ---------------------------------------------------------------------------------------------
-- 스키마(or 테이블) 내 모든 Serial 시퀀스 리셋
-- ---------------------------------------------------------------------------------------------
-- 예시
--   - CALL RESET_ALL_SERIALS();
--   - CALL RESET_ALL_SERIALS('스키마명');
--   - CALL RESET_ALL_SERIALS('스키마명', '테이블명');
-- ---------------------------------------------------------------------------------------------
DROP PROCEDURE IF EXISTS RESET_ALL_SERIALS;
CREATE PROCEDURE RESET_ALL_SERIALS
(
    _schema_name TEXT DEFAULT 'public', 
    _table_name  TEXT DEFAULT NULL
)
LANGUAGE plpgsql
AS $$
DECLARE
    sequence_record record;
    table_nm text;
    column_nm text;
    sequence_full_nm text;
    max_id bigint;
    last_seq_val int;
    is_modified text;
BEGIN
    _schema_name = LOWER(_schema_name);
    _table_name = LOWER(_table_name);

    FOR sequence_record IN
        SELECT sequence_name
        FROM information_schema.sequences
        WHERE sequence_schema = _schema_name
    LOOP
        is_modified = ' ';
                
        -- [1] 시퀀스가 사용되는 테이블, 컬럼 이름 탐색
        SELECT column_name, table_name INTO column_nm, table_nm
        FROM information_schema.columns
        WHERE column_default LIKE 'nextval(%' || (sequence_record.sequence_name) || '%)'
          AND table_schema = _schema_name
          AND CASE WHEN _table_name IS NULL THEN (TRUE) 
                   ELSE (table_name = _table_name) END;
        
        -- 해당하는 테이블이 아니면 생략
        CONTINUE WHEN (column_nm IS NULL);
        
        -- 스키마.시퀀스 이름 초기화
        sequence_full_nm := _schema_name || '.' || sequence_record.sequence_name;
        
        -- [2] 현재 값 확인
        EXECUTE FORMAT('SELECT last_value FROM %s', sequence_full_nm) INTO last_seq_val;
        
        -- [3] 해당 테이블에서 최대값 탐색
        EXECUTE 'SELECT COALESCE(MAX(' || quote_ident(column_nm) || '), 0) FROM ' || quote_ident(_schema_name) || '.' || quote_ident(table_nm) INTO max_id;

        IF max_id = 0 THEN
        -- [4-1] 시퀀스 값을 1로 재시작
            IF last_seq_val > 1 THEN
                is_modified = '*';
            END IF;
            EXECUTE(FORMAT('ALTER SEQUENCE %s RESTART WITH 1;', sequence_full_nm));
            RAISE NOTICE '% %.%.% : % => Reset (1)', is_modified, _schema_name, table_nm, column_nm, last_seq_val;
        ELSE
        -- [4-2] 시퀀스 값을 최대값으로 변경
            IF last_seq_val != max_id THEN
                is_modified = '*';
            END IF;
            EXECUTE 'SELECT setval(' || quote_literal(sequence_full_nm) || ', ' || max_id || ', true)';
            RAISE NOTICE '% %.%.% : % => %', is_modified, _schema_name, table_nm, column_nm, last_seq_val, max_id;
        END IF;
    END LOOP;
END; $$;