34. Contrib Extensions - spi

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 | third update 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에서 변경된 값, 맨처음이아니라 맨 마지막행으로 정렬됩니다.

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)

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.451144 UPDATE 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

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;