호출 예시

CALL RESET_ALL_SERIALS_IN_SCHEMA('스키마명');

정의

DROP PROCEDURE IF EXISTS RESET_ALL_SERIALS_IN_SCHEMA;
CREATE PROCEDURE RESET_ALL_SERIALS_IN_SCHEMA
(
    _schema_name TEXT
)
LANGUAGE plpgsql
AS $$
DECLARE
    sequence_record record;
    table_nm text;
    column_nm text;
    max_id bigint;
BEGIN
    FOR sequence_record IN
        SELECT sequence_name
        FROM information_schema.sequences
        WHERE sequence_schema = _schema_name
    LOOP
        -- [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;
        
        -- [2] 해당 테이블에서 최대값 탐색
        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
        -- [3-1] 시퀀스 값을 1로 재시작
            EXECUTE(FORMAT('ALTER SEQUENCE %s.%s RESTART WITH 1;', _schema_name, sequence_record.sequence_name));
            RAISE NOTICE '%.%.% => Reset (1)', _schema_name, table_nm, column_nm;
        ELSE
        -- [3-2] 시퀀스 값을 최대 ID 값으로 변경
            EXECUTE 'SELECT setval(' || quote_literal(_schema_name || '.' || sequence_record.sequence_name) || ', ' || max_id || ', true)';
            RAISE NOTICE '%.%.% => %', _schema_name, table_nm, column_nm, max_id;
        END IF;
    END LOOP;
END; $$;