Contrib Extension에서 트리거 기반의 함수를 제공하는 spi(server programming inserface) 모듈을 정리해봤습니다.
- autoinc
- insert_username
- moddatetime
- refint
autoinc
sequence의 다음 값을 정수 필드에 저장하는 함수입니다.
예시
간단한 sequence와 table을 생성하여, autoinc를 사용하는 trigger를 걸어준 뒤 insert 및 update로 sequence 필드가 자동적으로 갱신되는 상황의 예시입니다. 자세한 설명은 주석 참고바랍니다.
CREATE SEQUENCE next_id START -2 MINVALUE -2;
CREATE TABLE ids (
id int4,
idesc text
);CREATE TRIGGER ids_nextid
BEFORE INSERT OR UPDATE ON ids
FOR EACH ROW
EXECUTE PROCEDURE autoinc (id, next_id);
— autoinc를 이용해 insert/update시 id 필드에 next_id 시퀀스 갱신값을 반영하겠다는 trigger입니다.INSERT INTO ids VALUES (0, ‘first’); — id에는 0, idesc에는 first 문자를 넣겠다는 의도입니다.
INSERT INTO ids VALUES (null, ‘second’); — id에는 null값, idesc에는 second 문자를 넣겠다는 의도입니다.
INSERT INTO ids(idesc) VALUES (‘third’); — idesc에만 third문자를 넣겠다는 의도입니다.select * from ids;
— 원래 결과값은 insert에 의도한대로 나와야하지만, autoinc 포함된 trigger가 발동하여,
–next_id sequence의 처음 값인 -2 부터 순차적으로 배당됩니다.
id | idesc
—-+——–
-2 | first
-1 | second
1 | thirdupdate ids set id = null, idesc = ‘fourth’ where idesc=’first’; — idesc가 first인 행을 null | fourth 로 update 하겠다는 의도입니다.
SELECT * FROM ids;
— 원래 결과값은 update에 의도한대로 나와야하지만, autoinc 포함된 trigger가 발동하여, 1 다음 sequence 값인 2가 배당됩니다.
— 그러면서 저절로 id값을 기준으로 오름차순 정렬이 됩니다.
id | idesc
—-+——–
-1 | second
1 | third
2 | fourth — -2 | first에서 변경된 값, 맨처음이아니라 맨 마지막행으로 정렬됩니다.
참고 사이트
- 공식 홈페이지 : https://www.postgresql.org/docs/current/contrib-spi.html#id-1.11.7.50.6
- 공식 git hub contrib 참고 : sample sql https://github.com/postgres/postgres/blob/master/contrib/spi/autoinc.example
insert_username
현재 사용자의 이름을 텍스트 필드에 저장하는 함수입니다. 이는 테이블 내 특정 행을 마지막으로 수정한 사람을 추적하는 데 유용합니다.
예시
insert_username을 트리거를 생성해서 활용하는 예시입니다.
CREATE TABLE username_test (
name text,
username text not null
);CREATE TRIGGER insert_usernames
BEFORE INSERT OR UPDATE ON username_test
FOR EACH ROW
EXECUTE PROCEDURE insert_username (username);INSERT INTO username_test VALUES (‘nothing’);
INSERT INTO username_test VALUES (‘null’, null);
INSERT INTO username_test VALUES (’empty string’, ”);
INSERT INTO username_test VALUES (‘space’, ‘ ‘);
INSERT INTO username_test VALUES (‘tab’, ‘ ‘);
INSERT INTO username_test VALUES (‘name’, ‘name’);SELECT * FROM username_test;
–postgres 유저로 접속한 상황에서, insert 시 자동으로 username 필드에 어떤 값을 넣더라도 postgres 유저명이 들어감을 확인합니다.
postgres=# select * from username_test;
name | username
————–+———-
nothing | postgres
null | postgres
empty string | postgres
space | postgres
tab | postgres
name | postgres
(6 rows)
참고 사이트
- 공식 홈페이지 : https://www.postgresql.org/docs/current/contrib-spi.html#id-1.11.7.50.7
- 공식 git hub contrib 참고 : sample sql https://github.com/postgres/postgres/blob/master/contrib/spi/insert_username.example
moddatetime
timestamp with time zone 데이터 유형을 활용해 현재 시간을 필드에 저장하는 함수 입니다. 이는 테이블 내 특정 행의 마지막 수정 시간을 추적하는데 유용할 수 있습니다. DDL로 인한 수정 내역은 추적하지 못하지만, 데이터의 insert 시간과 update 시간을 추적할 수 있습니다.
예시
CREATE TABLE mdt (
id int4,
idesc text,
moddate timestamp DEFAULT CURRENT_TIMESTAMP NOT NULL
);CREATE TRIGGER mdt_moddatetime
BEFORE UPDATE ON mdt
FOR EACH ROW
EXECUTE PROCEDURE moddatetime (moddate);INSERT INTO mdt VALUES (1, ‘first’);
INSERT INTO mdt VALUES (2, ‘second’);
INSERT INTO mdt VALUES (3, ‘third’);SELECT * FROM mdt; — 각 행이 insert된 시간이 moddate에 저절로 저장됩니다.
id | idesc | moddate
—-+——–+—————————-
1 | first | 2022-08-24 20:28:28.344458
2 | second | 2022-08-24 20:28:28.371551
3 | third | 2022-08-24 20:28:28.451144UPDATE mdt SET id = 4
WHERE id = 1;
UPDATE mdt SET id = 5
WHERE id = 2;
UPDATE mdt SET id = 6
WHERE id = 3;SELECT * FROM mdt; –각 행이 update된 시간이 moddate에 저절로 저장됩니다.
id | idesc | moddate
—-+——–+—————————-
4 | first | 2022-08-24 20:28:40.752491
5 | second | 2022-08-24 20:28:40.78703
6 | third | 2022-08-24 20:28:40.842789
참고 사이트
- 공식 홈페이지 : https://www.postgresql.org/docs/current/contrib-spi.html#id-1.11.7.50.8
- 공식 git hub contrib 참고 : sample sql https://github.com/postgres/postgres/blob/master/contrib/spi/moddatetime.example
refint
참조 무결성 구현을 위한 함수입니다. check_primary_key(), check_foreign_key() 함수를 제공하며, 외래 키 제약 조건을 확인하는데 사용됩니다. 두 함수 모두 트리거로 생성해서 사용하기를 권장하나 이 extension 기능은 pg 내장 된 외래 키 메커니즘 기능으로 대체되었습니다.
예시
테이블 3개를 생성하여 각각의 무결성을 체크하는 예시입니다.
CREATE TABLE A (
ID int4 not null
);
CREATE UNIQUE INDEX AI ON A (ID);–Columns REFB of table B and REFC of C are foreign keys referencing ID of A:
CREATE TABLE B (
REFB int4
);
CREATE INDEX BI ON B (REFB);CREATE TABLE C (
REFC int4
);
CREATE INDEX CI ON C (REFC);CREATE TRIGGER AT BEFORE DELETE OR UPDATE ON A FOR EACH ROW
EXECUTE PROCEDURE
check_foreign_key (2, ‘cascade’, ‘id’, ‘b’, ‘refb’, ‘c’, ‘refc’);
/* 각 인수에 대한 설명입니다.
2 – 두 테이블의 외래 키에 대해 검사를 수행해야함을 의미.
cascade – 해당 키를 삭제해야 함을 정의.
id – 트리거된 테이블 A에 대한 primary key 컬럼명. 필요한 만큼 컬럼을 사용 가능.
b – foreign key가 있는 첫번째 테이블명.
refb – 이 테이블의 외래 키 컬럼명. 필요로하는 많은 컬럼을 사용할 수 있지만, 테이블 A에서 참조된 키 컬럼의 수는 같아야함
c – foreign key가 있는 두번째 테이블명.
refc – 이 테이블의 외래 키 컬럼명.
*/CREATE TRIGGER BT BEFORE INSERT OR UPDATE ON B FOR EACH ROW
EXECUTE PROCEDURE
check_primary_key (‘refb’, ‘a’, ‘id’);
/* 각 인수에 대한 설명입니다.
refb – 트리거된 테이블 B에 대한 foreign key 컬럼명. 필요로하는 많은 컬럼을 사용할 수 있지만, 테이블에서 참조된 키 컬럼의 수는 같아야함
a – 참조하고 있는 테이블명.
id – 참조하고 있는 테이블의 primary key 컬럼명.
*/CREATE TRIGGER CT BEFORE INSERT OR UPDATE ON C FOR EACH ROW
EXECUTE PROCEDURE
check_primary_key (‘refc’, ‘a’, ‘id’);— 테이블 세팅 완료, 이하는 데이터로 테스트 수행
INSERT INTO A VALUES (10);
INSERT INTO A VALUES (20);
INSERT INTO A VALUES (30);
INSERT INTO A VALUES (40);
INSERT INTO A VALUES (50);INSERT INTO B VALUES (1); — invalid reference
INSERT INTO B VALUES (10);
INSERT INTO B VALUES (30);
INSERT INTO B VALUES (30);INSERT INTO C VALUES (11); — invalid reference
INSERT INTO C VALUES (20);
INSERT INTO C VALUES (20);
INSERT INTO C VALUES (30);DELETE FROM A WHERE ID = 10;
DELETE FROM A WHERE ID = 20;
DELETE FROM A WHERE ID = 30;SELECT * FROM A;
SELECT * FROM B;
SELECT * FROM C;
참고 사이트
- 공식 홈페이지 : https://www.postgresql.org/docs/current/contrib-spi.html#id-1.11.7.50.5
- 공식 git hub contrib 참고 : sample sql https://github.com/postgres/postgres/blob/master/contrib/spi/refint.example
지금까지 ‘PostgreSQL의 Contrib Extensions – spi’에 관해 알아보았습니다
‘PostgreSQL의 Contrib Extensions – 인덱스 및 검색’을 바로 이어서 확인해보세요!
