호출 예시(트리거 설정)

CALL PREVENT_DROP_TABLES_IN_SCHEMA('schema_name');

트리거 해제

CALL PREVENT_DROP_TABLES_IN_SCHEMA('schema_name', false);

정의

--------------------------------------------------------------------------------------------
-- 지정 스키마 내 테이블을 제거하지 못하게 방지
--------------------------------------------------------------------------------------------
-- [예제]
-- 설정: CALL PREVENT_DROP_TABLES_IN_SCHEMA('schema_name');
-- 해제: CALL PREVENT_DROP_TABLES_IN_SCHEMA('schema_name', false);
--------------------------------------------------------------------------------------------
DROP PROCEDURE IF EXISTS PREVENT_DROP_TABLES_IN_SCHEMA;
        CREATE PROCEDURE PREVENT_DROP_TABLES_IN_SCHEMA
(
    _schema_name TEXT,
    _on BOOLEAN = true
)
AS $$
DECLARE
    _cmm TEXT;
BEGIN
    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_TABLES_IN_SCHEMA_%1$s CASCADE;
                CREATE FUNCTION FN_TRG_PREVENT_DROP_TABLES_IN_SCHEMA_%1$s()
        RETURNS event_trigger AS $_$
        DECLARE
            obj record;
        BEGIN
            FOR obj IN SELECT object_type, schema_name FROM pg_event_trigger_dropped_objects()
            LOOP
                IF     obj.object_type = ''table'' 
                   AND obj.schema_name = ''%1$s'' 
                THEN
                    RAISE EXCEPTION ''%1$s 스키마 내 테이블을 제거할 수 없습니다.'';
                END IF;
            END LOOP;
        END $_$
        LANGUAGE plpgsql;

        DROP EVENT TRIGGER IF EXISTS TRG_PREVENT_DROP_TABLES_IN_SCHEMA_%1$s;
        CREATE EVENT TRIGGER TRG_PREVENT_DROP_TABLES_IN_SCHEMA_%1$s ON sql_drop
        EXECUTE FUNCTION FN_TRG_PREVENT_DROP_TABLES_IN_SCHEMA_%1$s();
    ', _schema_name);
ELSE
    EXECUTE FORMAT('
        DROP FUNCTION IF EXISTS FN_TRG_PREVENT_DROP_TABLES_IN_SCHEMA_%1$s CASCADE;
    ', _schema_name);
END IF;

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

END;$$
LANGUAGE plpgsql;