[PostgreSQL] 테이블 수정 컬럼 자동 갱신 설정 (With trigger)

Mariadb에서 ON UPDATE CURRENT_TIMESTAMP  옵션으로 수정시 자동 수정시간 갱신되는 컬럼을 설정할 수 있다. 이러한 기능을 PostgreSQL에서도 사용하기 위해 찾아보니 Trigger를 사용해야 했다.

Trigger를 사용하기 위해선 시간 갱신 함수를 같이 사용해야한다.

아래는 모든 테이블의 updated_at 이라는 컬럼에 대한 트리거 생성, 삭제 하는 PLSQL 이다.

updatedAt 갱신 함수

CREATE OR REPLACE FUNCTION "fn_update_updated_at"()
RETURNS TRIGGER AS $$
BEGIN
    NEW."updated_at" = current_timestamp;
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

updated_at 갱신 트리거 생성

DO $$
DECLARE
    r RECORD;
BEGIN
	FOR r IN (
	    SELECT table_name, table_schema
		FROM information_schema.columns
		WHERE column_name  = 'updated_at'
	) LOOP
	    EXECUTE format('
	        DROP TRIGGER IF EXISTS "tg_on_update_%s" on %I.%I;
	
			CREATE TRIGGER "tg_on_update_%s"
	        BEFORE UPDATE ON %I.%I
	        FOR EACH ROW
	        EXECUTE PROCEDURE %I."fn_update_updated_at"();        
	    ', r.table_name, r.table_schema, r.table_name, r.table_name, r.table_schema, r.table_name, r.table_schema);
	END LOOP;
END $$;

updated_at 갱신 트리거 삭제

DO $$
DECLARE
    r RECORD;
BEGIN
	FOR r IN (
	    SELECT table_name, table_schema
		FROM information_schema.columns
		WHERE column_name  = 'updated_at'
	) LOOP
	    EXECUTE format('
	        DROP TRIGGER IF EXISTS "tg_on_update_%s" on %I.%I;   
	    ', r.table_name, r.table_schema, r.table_name);
	END LOOP;
END $$;

 

위 구문을 통해서 수정시간 컬럼에 대한 갱신 트리거를 쉽게 생성하고 삭제 할 수 있다.