SQL 기본 및 고급 쿼리문 4

정리

ㅇㅇ

 

Prosedure

DO $$
DECLARE
	file_path TEXT;
	file_name TEXT;
	file_date TEXT;
	file_extension TEXT;
	total_path TEXT;
BEGIN
	file_path := 'your_path\';
	file_name := 'farm_summary_';
	file_date := to_char(now(), 'YYYYMMDD_HH24MISS');
	file_extension := '.csv';
	total_path := file_path || file_name || file_date || file_extension;

	EXECUTE format(
		'COPY(SELECT * FROM get_farm_dest_info(%L)) TO %L CSV HEADER',
        	'A',
		total_path
	);
END
$$

ㅇㅇ

 

Trigger

Keyword	Data type	Triggers on Data Changes
NEW	RECORD	variable holding the new database row for INSERT/UPDATE operations in row-level triggers. This variable is unassigned in statement-level triggers and for DELETE operations.
OLD	RECORD	variable holding the old database row for UPDATE/DELETE operations in row-level triggers. This variable is unassigned in statement-level triggers and for INSERT operations.
TG_NAME	name	variable that contains the name of the trigger actually fired.
TG_WHEN	text	a string of BEFORE, AFTER, or INSTEAD OF, depending on the trigger's definition.
TG_LEVEL	text	a string of either ROW or STATEMENT depending on the trigger's definition.
TG_OP	text	a string of INSERT, UPDATE, DELETE, or TRUNCATE telling for which operation the trigger was fired.
TG_RELID	old	the object ID of the table that caused the trigger invocation.
TG_RELNAME	name	the name of the table that caused the trigger invocation. This is now deprecated, and could disappear in a future release. Use TG_TABLE_NAME instead.
TG_TABLE _NAME	name	the name of the table that caused the trigger invocation.
TG_TABLE _SCHEMA	name	the name of the schema of the table that caused the trigger invocation.
TG_NARGS	integer	the number of arguments given to the trigger procedure in the CREATE TRIGGER statement.
TG_ARGV[]	text	the arguments from the CREATE TRIGGER statement. The index counts from 0. Invalid indexes (less than 0 or greater than or equal to tg_nargs) result in a null value.

PL/pgSQL은 트리거 프로시저(trigger procedure)를 정의하는 데 사용할 수 있습니다. 트리거 프로시저는 CREATE FUNCTION 명령으로 생성되며, 인수가 없는 함수로 선언되고 반환 타입은 trigger로 지정되어야 합니다. 비록 CREATE TRIGGER에서 인수를 지정할 수 있다고 해도, 함수 자체는 인수를 받지 않는 형태로 선언되어야 합니다 — 트리거 인수는 TG_ARGV를 통해 전달되기 때문입니다(아래에서 설명함). PL/pgSQL 함수가 트리거로 호출될 경우, 최상위 블록 내에 여러 개의 특수 변수들이 자동으로 생성됩니다.

이에 대해 자세한 설명은 Trigger Procedures에서 볼 수 있다.

 

ㅇㅇ

 

-- 테이블 생성
-- 다른 테이블이 꼬이지 않도록 트리거 전용으로 사용할 임시 테이블
CREATE TABLE fms.ENV_COND_AUDIT (
    audit_id SERIAL PRIMARY KEY,    -- 고유키(자동 증가)
    farm bpchar(1) NOT NULL,        -- 온습도 측정 농장 구분
    check_date date NOT NULL,       -- 온도 습도 측정 날짜
    new_temp NUMERIC(4, 1),         -- 신규 측정 온도
    new_humid NUMERIC(4, 1),        -- 신규 측정 온도
    modified_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,    -- 수정 날짜
    logging varchar(100)            -- update 등의 동작 구분
);

ㅇㅇ

 

-- 트리거에서 사용할 함수 정의
CREATE OR REPLACE FUNCTION fms.log_env_change()
RETURNS TRIGGER AS $$
DECLARE
    temp_q1 NUMERIC;
    temp_q3 NUMERIC;
    temp_iqr NUMERIC;
    temp_lower_bound NUMERIC;
    temp_upper_bound NUMERIC;

    humid_q1 NUMERIC;
    humid_q3 NUMERIC;
    humid_iqr NUMERIC;
    humid_lower_bound NUMERIC;
    humid_upper_bound NUMERIC;
    
    op TEXT;
    high NUMERIC(4, 1);
    low  NUMERIC(4, 1);
    avg_ NUMERIC;

BEGIN
    -- IQR 확인을 위한 Q1, Q3 구간 확인
    SELECT
        percentile_cont(0.25) WITHIN GROUP (ORDER BY "temp"),
        percentile_cont(0.75) WITHIN GROUP (ORDER BY "temp"),
        percentile_cont(0.25) WITHIN GROUP (ORDER BY humid),
        percentile_cont(0.75) WITHIN GROUP (ORDER BY humid)
    INTO temp_q1, temp_q3, humid_q1, humid_q3
    FROM fms.env_cond;
    
    -- 온도 IQR 확인
    temp_iqr := temp_q3 - temp_q1;
    temp_lower_bound := temp_q1 - (0.5 * temp_iqr);
    temp_upper_bound := temp_q3 + (0.5 * temp_iqr);

    -- 습도 IQR 확인
    humid_iqr := humid_q3 - humid_q1;
    humid_lower_bound := humid_q1 - (0.75 * humid_iqr);
    humid_upper_bound := humid_q3 + (0.75 * humid_iqr);

    -- INSERT가 발생하면 실행
    IF TG_OP = 'INSERT' THEN
        
        -- 저온일 경우 테이블에 log 남기기
        IF NEW."temp" < temp_lower_bound THEN
            -- 평균 대비 차이 구하기
            SELECT AVG("temp") INTO avg_ FROM fms.env_cond;
            low := (avg_ - NEW."temp") * 100 / avg_;
            op  := NEW.farm || '농장 저온 (평균 대비 ' || low || '% 낮음 ▼)';
            
            -- 이상치인 경우 데이터 로그 남기기
            INSERT INTO fms.ENV_COND_AUDIT
            (farm, check_date, new_temp, new_humid, logging)
            VALUES
            (
                NEW.farm, NEW."date", NEW."temp", NEW.humid, op
            );
        
        -- 고온일 경우 테이블에 log 남기기
        ELSIF NEW."temp" > temp_upper_bound THEN
            -- 평균 대비 차이 구하기
            SELECT AVG("temp") INTO avg_ FROM fms.env_cond;
            high := (NEW."temp" - avg_) * 100 / avg_;
            op   := NEW.farm || '농장 고온 (평균 대비 ' || high || '% 높음 ▲)';
            
            -- 이상치인 경우 데이터 로그 남기기
            INSERT INTO fms.ENV_COND_AUDIT
            (farm, check_date, new_temp, new_humid, logging)
            VALUES
            (
                NEW.farm, NEW."date", NEW."temp", NEW.humid, op
            );

        -- 저습일 경우 테이블에 log 남기기
        ELSIF NEW.humid < humid_lower_bound THEN
            -- 평균 대비 차이 구하기
            SELECT AVG(humid) INTO avg_ FROM fms.env_cond;
            low := (avg_ - NEW.humid) * 100 / avg_;
            op  := NEW.farm || '농장 저습 (평균 대비 ' || low || '% 낮음 ▼)';
            
            -- 이상치인 경우 데이터 로그 남기기
            INSERT INTO fms.ENV_COND_AUDIT
            (farm, check_date, new_temp, new_humid, logging)
            VALUES
            (
                NEW.farm, NEW."date", NEW."temp", NEW.humid, op
            );
        
        -- 고습일 경우 테이블에 log 남기기
        ELSIF NEW.humid > humid_upper_bound THEN
            -- 평균 대비 차이 구하기
            SELECT AVG(humid) INTO avg_ FROM fms.env_cond;
            high := (NEW.humid - avg_) * 100 / avg_;
            op   := NEW.farm || '농장 고습 (평균 대비 ' || high || '% 높음 ▲)';
            
            -- 이상치인 경우 데이터 로그 남기기
            INSERT INTO fms.ENV_COND_AUDIT
            (farm, check_date, new_temp, new_humid, logging)
            VALUES
            (
                NEW.farm, NEW."date", NEW."temp", NEW.humid, op
            );
        
        -- 정상 범주
        ELSE
            op := '온도 및 습도 정상';
            INSERT INTO fms.ENV_COND_AUDIT
            (farm, check_date, new_temp, new_humid, logging)
            VALUES
            (
                NEW.farm, NEW."date", NEW."temp", NEW.humid, op
            );

        END IF;
    END IF;
    
    -- trigger keyword 반환
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

ㅇㅇ

 

-- 트리거 생성
-- INSERT 또는 UPDATE가 발생할 때마다 fms.log_env_change 함수를 호출
CREATE TRIGGER env_log_trigger
AFTER INSERT ON
    fms.env_cond
FOR EACH ROW
EXECUTE PROCEDURE fms.log_env_change();

ㅇㅇ

 

-- 트리거 동작 확인을 위한 구문
INSERT INTO fms.env_cond (farm, "date", "temp", humid)
VALUES
('A', '2025-05-08', 45, 40),
('B', '2025-05-08', 12, 70),
('B', '2025-05-08', 35, 60);

ㅇㅇ

 

ㅇㅇ

ㅇㅇ