호출 예시

CALL ADD_THREE_TIME_COLS_ON_TABLE('테이블명');

정의

-- ---------------------------------------------------------------------------------------------
-- 대상 테이블에 CRTD_AT 컬럼 추가
-- ---------------------------------------------------------------------------------------------
DROP PROCEDURE IF EXISTS ADD_CRT_ON_TABLE;
CREATE PROCEDURE ADD_CRT_ON_TABLE
(
    _tableName TEXT,
    _created_at TEXT default 'CRTD_AT'
)
AS $$
    DECLARE _fullName TEXT;
BEGIN

    _fullName := REPLACE(_tableName, '.', '_'); -- 테이블명에 스키마가 포함된 경우, 점 치환
    
    EXECUTE FORMAT('ALTER TABLE %s ADD COLUMN %s TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP', _tableName, _created_at);
    EXECUTE FORMAT('CREATE INDEX IDX_%s_%s ON %s USING BRIN (%s)', _fullName, _created_at, _tableName, _created_at);
    
END; $$
LANGUAGE plpgsql;

-- ---------------------------------------------------------------------------------------------
-- 대상 테이블에 CRTD_AT, UPTD_AT 컬럼 추가
-- ---------------------------------------------------------------------------------------------
DROP PROCEDURE IF EXISTS ADD_CRT_UPT_ON_TABLE;
CREATE PROCEDURE ADD_CRT_UPT_ON_TABLE
(
    _tableName TEXT,
    _created_at TEXT default 'CRTD_AT',
    _updated_at TEXT default 'UPTD_AT'
)
AS $$
    DECLARE _fullName TEXT;
BEGIN

    _fullName := REPLACE(_tableName, '.', '_'); -- 테이블명에 스키마가 포함된 경우, 점 치환
    
    EXECUTE FORMAT(
        'CREATE OR REPLACE FUNCTION AUTO_UPDATE__%s() '
        '    RETURNS TRIGGER AS $_$ '
        'BEGIN '
        '    NEW.%s = now(); '
        '    RETURN NEW; '
        'END; $_$ '
        'LANGUAGE ''plpgsql'';'
    , _updated_at, _updated_at);
    
    EXECUTE FORMAT('ALTER TABLE %s ADD COLUMN %s TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP', _tableName, _created_at);
    EXECUTE FORMAT('ALTER TABLE %s ADD COLUMN %s TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP', _tableName, _updated_at);
    
    EXECUTE FORMAT('CREATE TRIGGER %s BEFORE UPDATE ON %s FOR EACH ROW EXECUTE PROCEDURE AUTO_UPDATE__%s()', _updated_at, _tableName, _updated_at);
    EXECUTE FORMAT('CREATE INDEX IDX_%s_%s ON %s USING BRIN (%s)', _fullName, _created_at, _tableName, _created_at);
    EXECUTE FORMAT('CREATE INDEX IDX_%s_%s ON %s USING BRIN (%s)', _fullName, _updated_at, _tableName, _updated_at);
    
END; $$
LANGUAGE plpgsql;

-- ---------------------------------------------------------------------------------------------
-- 대상 테이블에 DLTD_AT 컬럼 추가 + Soft Delete (Hard Delete 방지)
-- ---------------------------------------------------------------------------------------------
DROP PROCEDURE IF EXISTS ADD_DLT_ON_TABLE;
CREATE PROCEDURE ADD_DLT_ON_TABLE
(
    _tableName TEXT,
    _deleted_at TEXT default 'DLTD_AT',
    _makeSoftDelete BOOLEAN default FALSE
)
AS $$
    DECLARE _fullName TEXT;
BEGIN

    _fullName := REPLACE(_tableName, '.', '_'); -- 테이블명에 스키마가 포함된 경우, 점 치환
    
    -- 1. deleted_at 컬럼 추가
    EXECUTE FORMAT('ALTER TABLE %s ADD COLUMN %s TIMESTAMPTZ NULL DEFAULT NULL', _tableName, _deleted_at);
    EXECUTE FORMAT('CREATE INDEX IDX_%s_%s ON %s USING BRIN (%s)', _fullName, _deleted_at, _tableName, _deleted_at);

    -- 2. Soft delete 강제
    IF _makeSoftDelete = TRUE THEN
        EXECUTE FORMAT(
            'CREATE OR REPLACE FUNCTION TRIGGER_SOFT_DELETE_%s() '
            'RETURNS trigger AS '' BEGIN UPDATE %s SET %s = NOW() WHERE ctid=OLD.ctid; RETURN NULL; END; '' language plpgsql;'
        , _fullName, _tableName, _deleted_at);
        
        EXECUTE FORMAT('CREATE TRIGGER SOFT_DEL_%s BEFORE DELETE ON %s FOR EACH ROW EXECUTE PROCEDURE TRIGGER_SOFT_DELETE_%s();'
        ,_fullName, _tableName, _fullName);
    END IF;
    
END; $$
LANGUAGE plpgsql;

-- ---------------------------------------------------------------------------------------------
-- 최종: C, U, D 컬럼 추가
-- ---------------------------------------------------------------------------------------------
DROP PROCEDURE IF EXISTS ADD_THREE_TIME_COLS_ON_TABLE;
CREATE PROCEDURE ADD_THREE_TIME_COLS_ON_TABLE
(
    _tableName TEXT
)
AS $$
    DECLARE _fullName TEXT;
BEGIN

    CALL ADD_CRT_UPT_ON_TABLE(_tableName);
    CALL ADD_DLT_ON_TABLE(_tableName);
    
END; $$
LANGUAGE plpgsql;