호출 예시(트리거 설정)

CALL PREVENT_DROP_TABLE('schema_name.table_name');

트리거 해제

CALL PREVENT_DROP_TABLE('schema_name.table_name', false);

정의

--------------------------------------------------------------------------------------------
-- 지정 테이블을 제거하지 못하게 방지
--------------------------------------------------------------------------------------------
-- [예제]
-- 설정: CALL PREVENT_DROP_TABLE('schema_name.table_name');
-- 해제: CALL PREVENT_DROP_TABLE('schema_name.table_name', false);
--------------------------------------------------------------------------------------------
DROP PROCEDURE IF EXISTS PREVENT_DROP_TABLE;
        CREATE PROCEDURE PREVENT_DROP_TABLE
(
    _schema_table_name TEXT,
    _on BOOLEAN = true
)
AS $$
DECLARE
    _schema_name TEXT;
    _table_name TEXT;
    _cmm TEXT;
BEGIN
    _schema_table_name = LOWER(_schema_table_name);
    
    -- 스키마/테이블명 분리
    IF strpos(_schema_table_name, '.') > 0 THEN
        _schema_name := split_part(_schema_table_name, '.', 1);
        _table_name  := split_part(_schema_table_name, '.', 2);
    ELSE
        _schema_name := 'public';
        _table_name  := _schema_table_name;
    END IF;

    -- Suppress Warning
    SHOW client_min_messages INTO _cmm;
    EXECUTE ('SET client_min_messages = ''error''');

IF _on THEN
    EXECUTE FORMAT('
        DROP FUNCTION IF EXISTS FN_TRG_PREVENT_DROP_TABLE_%1$s_%2$s CASCADE;
                CREATE FUNCTION FN_TRG_PREVENT_DROP_TABLE_%1$s_%2$s()
        RETURNS event_trigger AS $_$
        DECLARE
            obj record;
        BEGIN
            FOR obj IN SELECT objid, object_type, object_name, schema_name FROM pg_event_trigger_dropped_objects()
            LOOP
                IF     obj.object_type = ''table''
                   AND obj.schema_name = ''%1$s''
                   AND obj.object_name = ''%2$s''
                THEN
                    RAISE EXCEPTION ''%1$s.%2$s 테이블을 제거할 수 없습니다.'';
                END IF;
            END LOOP;
        END $_$
        LANGUAGE plpgsql;
                   
        DROP EVENT TRIGGER IF EXISTS TRG_PREVENT_DROP_TABLE_%1$s_%2$s;
        CREATE EVENT TRIGGER TRG_PREVENT_DROP_TABLE_%1$s_%2$s ON sql_drop
        EXECUTE FUNCTION FN_TRG_PREVENT_DROP_TABLE_%1$s_%2$s();
    ', _schema_name, _table_name);
ELSE
    EXECUTE FORMAT('
        DROP FUNCTION IF EXISTS FN_TRG_PREVENT_DROP_TABLE_%1$s_%2$s CASCADE;
    ', _schema_name, _table_name);
END IF;

    EXECUTE FORMAT('SET client_min_messages = ''%s''', _cmm);
    RAISE NOTICE '%.% 테이블 제거 방지 %', _schema_name, _table_name, (CASE WHEN _on THEN 'ON' ELSE 'OFF' END);

END;$$
LANGUAGE plpgsql;