| PostgreSQL 8.3.3문서 | ||||
|---|---|---|---|---|
| Prev | Fast Backward | Chapter 37. PL/pgSQL - SQL수속 언어 | Fast Forward | Next |
PL/pgSQL는 트리거 프로시저의 정의에 사용할 수 있습니다. 트리거 프로시저는, CREATE FUNCTION명령을 사용해, trigger라는 반환값의 형태를 가진 인자가 없는 함수로서 작성됩니다. 그 함수는, 비록, CREATE TRIGGER에서 인자를 취하고 있었어도, 인자를 가지지 않는 것이 선언되어야 합니다. 트리거의 인자는, 후술 하는 대로, TG_ARGV를 통해 건네받습니다.
PL/pgSQL함수가 트리거로서 불려 갔을 경우, 몇개의 특수한 변수가 자동적으로 최상 레벨의 블록으로 작성됩니다. 그것들을 이하에 나타냅니다.
RECORD데이터형. 이 변수는 행 레벨의 트리거에서의 INSERT/UPDATE조작에 의해 갱신된, 새로운 데이터베이스의 행을 보관 유지합니다. 이 변수는 명령문 레벨의 트리거에서는 NULL입니다.
RECORD데이터형. 이 변수는, 행 레벨의 트리거에서의 UPDATE/DELETE조작에 의해 갱신되기 전의 데이터베이스의 행을 보관 유지합니다. 이 변수는 명령문 레벨의 트리거에서는 NULL입니다.
name데이터형. 실제로 발행된 트리거의 이름을 가지는 변수.
text데이터형. 트리거의 정의에 의존한 BEFORE 또는 AFTER라는 문자열.
text데이터형. 트리거의 정의에 의존한 ROW 또는 STATEMENT라는 문자열.
text데이터형. 트리거를 발행한 조작을 나타내는, INSERT, UPDATE 또는 DELETE라는 문자열.
oid데이터형. 이 트리거 호출의 기원이 되는 테이블의 객체 ID.
name데이터형. 이 트리거 호출의 기원이 되는 테이블의 이름. 향후, 이것은 폐지될 것 같습니다. 대신에 TG_TABLE_NAME를 사용해 주세요.
name데이터형. 이 트리거 호출의 기원이 되는 테이블의 이름.
name데이터형. 이 트리거 호출의 기원이 되는 테이블의 스키마명.
integer형. CREATE TRIGGER문에서의 트리거 프로시저에게 줄 수 있는 인자의 수.
text형의 배열형. CREATE TRIGGER문에서의 인자. 이 인덱스는 0으로부터 시작됩니다. 무효인 인덱스(0 미만이나 tg_nargs이상)는, NULL값이라는 결과가 됩니다.
트리거 함수는 NULL 또는, 트리거의 근원이 된 테이블의 구조를 정확하게 가진 레코드/행을 돌려주어야 합니다.
BEFORE로서 발행된 행 level trigger가 NULL를 돌려주는 경우에는, 트리거 관리자에 실제의 행에의 조작을 취소하도록 통지합니다 (즉, 그 후에 트리거가 발행되지 않고, 그 INSERT/UPDATE/DELETE는 그 행에 대해서 실행되지 않습니다). 비NULL값을 돌려주는 경우에는, 그 조작은 그 행값으로 처리됩니다. 원래의 NEW의 값과 다른 행값을 돌려주는 것은, 삽입, 갱신되는 값을 변경하는 것에 주의해 주세요 (그러나, DELETE의 경우는 직접 영향을 주지 않습니다). 저장해야 할 행을 변경하기 위해서, NEW의 개개의 값을 직접 옮겨놓고 NEW를 돌려주는 일도, 새로운 레코드/행을 완전하게 작성해 돌려주는 일도 가능합니다.
명령문 레벨의 BEFORE 또는 AFTER트리거, 행 레벨의 AFTER트리거의 반환값은 항상 무시됩니다. NULL라고 해도 상관하지 않습니다. 그러나, 이러한 종류의 트리거로, 에러를 발생시켜 조작 전체를 중단시키는 것이 가능합니다.
Example 37-2에 PL/pgSQL의 트리거 함수의 예를 나타냅니다.
Example 37-2. PL/pgSQL트리거 프로시저
이 트리거의 예에서는, 테이블의 행이 삽입 또는 갱신되었을 때에는 반드시, 현재의 사용자명과 시각이 그 행에 들어있는 것을 확실히 합니다. 그리고, 종업원명이 주어진 것으로 그 급료가 정의 값인 것을 확인합니다.
CREATE TABLE emp (
empname text,
salary integer,
last_date timestamp,
last_user text
);
CREATE FUNCTION emp_stamp() RETURNS trigger AS $emp_stamp$
BEGIN
-- empname와 salary가 주어지고 있는 것을 체크
IF NEW.empname IS NULL THEN
RAISE EXCEPTION 'empname cannot be null';
END IF;
IF NEW.salary IS NULL THEN
RAISE EXCEPTION '% cannot have null salary', NEW.empname;
END IF;
-- 지불시에 문제가 일어나지 않게
IF NEW.salary < 0 THEN
RAISE EXCEPTION '% cannot have a negative salary', NEW.empname;
END IF;
-- 누가 언제 변경했는지를 기록
NEW.last_date := current_timestamp;
NEW.last_user := current_user;
RETURN NEW;
END;
$emp_stamp$ LANGUAGE plpgsql;
CREATE TRIGGER emp_stamp BEFORE INSERT OR UPDATE ON emp
FOR EACH ROW EXECUTE PROCEDURE emp_stamp();
테이블에 있어서의 변경의 로그를 취하는 다른 방법은, 삽입, 갱신 또는 삭제의 각각에 대한 행을 보유하는 새 테이블을 작성하는 것입니다. 이 방법은 테이블에서의 변경의 감사라고 생각할 수 있습니다. Example 37-3는 PL/pgSQL에 의한 감사 트리거 프로시저의 일례를 나타냅니다.
Example 37-3. A PL/pgSQL 감사 트리거 프로시저
이 트리거의 예에서는, emp테이블에의 행의 삽입, 갱신 또는 삭제의 모두가 emp_audit 테이블안에 확실히 기록(즉 감사)됩니다. 현재 시각과 사용자명은, 수행된 연산 종류와 함께 emp_audit의 행안에 기록됩니다.
CREATE TABLE emp (
empname text NOT NULL,
salary integer
);
CREATE TABLE emp_audit(
operation char(1) NOT NULL,
stamp timestamp NOT NULL,
userid text NOT NULL,
empname text NOT NULL,
salary integer
);
CREATE OR REPLACE FUNCTION process_emp_audit() RETURNS TRIGGER AS $emp_audit$
BEGIN
--
-- emp로 수행된 연산을 반영하는 행을 emp_audit에 작성
-- 연산의 종류를 결정하기 위해서, 특수한 변수 TG_OP를 활용
--
IF (TG_OP = 'DELETE') THEN
INSERT INTO emp_audit SELECT 'D', now(), user, OLD.*;
RETURN OLD;
ELSIF (TG_OP = 'UPDATE') THEN
INSERT INTO emp_audit SELECT 'U', now(), user, NEW.*;
RETURN NEW;
ELSIF (TG_OP = 'INSERT') THEN
INSERT INTO emp_audit SELECT 'I', now(), user, NEW.*;
RETURN NEW;
END IF;
RETURN NULL; -- AFTER 트리거이므로, 결과는 무시됩니다
END;
$emp_audit$ LANGUAGE plpgsql;
CREATE TRIGGER emp_audit
AFTER INSERT OR UPDATE OR DELETE ON emp
FOR EACH ROW EXECUTE PROCEDURE process_emp_audit();
트리거의 사용 목적의 하나는, 어느 테이블의 요약 테이블(데이터를 발췌한 테이블)을 유지하는 것입니다. 요약 테이블은, 원래의 테이블에 대신해, 특정 종류의 쿼리에 대해서 사용할 수 있어 종종 실행 시간을 큰폭으로 축소합니다. 통상 이 방법은 데이터 도매상에 사용되어 측정 또는 관측 데이터(팩트 테이블이라고 말합니다)를 매우 크게 할 수 있습니다. 데이터 웨어하우스 내의 팩트 테이블에 대해서 요약 테이블을 유지하는 PL/pgSQL의 트리거 프로시저의 예를 Example 37-4에 나타냅니다.
Example 37-4. A 요약 테이블을 유지하기 위한 PL/pgSQL트리거 프로시저
여기에 말하는 스키마의 일부는 Ralph Kimball에 의한 The Data Warehouse Toolkit 의 Grocery Store 의 예에 근거하고 있습니다.
--
-- time dimension와 sales fact의 주테이블
--
CREATE TABLE time_dimension (
time_key integer NOT NULL,
day_of_week integer NOT NULL,
day_of_month integer NOT NULL,
month integer NOT NULL,
quarter integer NOT NULL,
year integer NOT NULL
);
CREATE UNIQUE INDEX time_dimension_key ON time_dimension(time_key);
CREATE TABLE sales_fact (
time_key integer NOT NULL,
product_key integer NOT NULL,
store_key integer NOT NULL,
amount_sold numeric(12,2) NOT NULL,
units_sold integer NOT NULL,
amount_cost numeric(12,2) NOT NULL
);
CREATE INDEX sales_fact_time ON sales_fact(time_key);
--
-- sales by time의 요약 테이블
--
CREATE TABLE sales_summary_bytime (
time_key integer NOT NULL,
amount_sold numeric(15,2) NOT NULL,
units_sold numeric(12) NOT NULL,
amount_cost numeric(15,2) NOT NULL
);
CREATE UNIQUE INDEX sales_summary_bytime_key ON sales_summary_bytime(time_key);
--
-- 갱신, 삽입 및 삭제에 의해 요약 테이블의 열을 수정하는 함수와 트리거
--
CREATE OR REPLACE FUNCTION maint_sales_summary_bytime() RETURNS TRIGGER AS $maint_sales_summary_bytime$
DECLARE
delta_time_key integer;
delta_amount_sold numeric(15,2);
delta_units_sold numeric(12);
delta_amount_cost numeric(15,2);
BEGIN
-- 증가 또는 감소량을 산출
IF (TG_OP = 'DELETE') THEN
delta_time_key = OLD.time_key;
delta_amount_sold = -1 * OLD.amount_sold;
delta_units_sold = -1 * OLD.units_sold;
delta_amount_cost = -1 * OLD.amount_cost;
ELSIF (TG_OP = 'UPDATE') THEN
-- time_key를 변경하는 갱신을 금지합니다
-- (삭제 + 삽입의 방법에 의해 대부분의 변경을 실시하기 때문에
-- 그렇게 귀찮지는 않습니다).
IF ( OLD.time_key != NEW.time_key) THEN
RAISE EXCEPTION 'Update of time_key : % -> % not allowed', OLD.time_key, NEW.time_key;
END IF;
delta_time_key = OLD.time_key;
delta_amount_sold = NEW.amount_sold - OLD.amount_sold;
delta_units_sold = NEW.units_sold - OLD.units_sold;
delta_amount_cost = NEW.amount_cost - OLD.amount_cost;
ELSIF (TG_OP = 'INSERT') THEN
delta_time_key = NEW.time_key;
delta_amount_sold = NEW.amount_sold;
delta_units_sold = NEW.units_sold;
delta_amount_cost = NEW.amount_cost;
END IF;
-- 요약 테이블의 행을 삽입 또는 새로운 값으로 갱신합니다.
<<insert_update>>
LOOP
UPDATE sales_summary_bytime
SET amount_sold = amount_sold + delta_amount_sold,
units_sold = units_sold + delta_units_sold,
amount_cost = amount_cost + delta_amount_cost
WHERE time_key = delta_time_key;
EXIT insert_update WHEN found;
BEGIN
INSERT INTO sales_summary_bytime (
time_key,
amount_sold,
units_sold,
amount_cost)
VALUES (
delta_time_key,
delta_amount_sold,
delta_units_sold,
delta_amount_cost
);
EXIT insert_update;
EXCEPTION
WHEN UNIQUE_VIOLATION THEN
-- 아무것도 하지 않습니다
END;
END LOOP insert_update;
RETURN NULL;
END;
$maint_sales_summary_bytime$ LANGUAGE plpgsql;
CREATE TRIGGER maint_sales_summary_bytime
AFTER INSERT OR UPDATE OR DELETE ON sales_fact
FOR EACH ROW EXECUTE PROCEDURE maint_sales_summary_bytime();
INSERT INTO sales_fact VALUES(1,1,1,10,3,15);
INSERT INTO sales_fact VALUES(1,2,1,20,5,35);
INSERT INTO sales_fact VALUES(2,2,1,40,15,135);
INSERT INTO sales_fact VALUES(2,3,1,10,1,13);
SELECT * FROM sales_summary_bytime;
DELETE FROM sales_fact WHERE product_key = 1;
SELECT * FROM sales_summary_bytime;
UPDATE sales_fact SET units_sold = units_sold * 2;
SELECT * FROM sales_summary_bytime;