PG数组以及触发器应用

• 建表

CREATE TABLE tbl_test
(
	event_code imos_code NOT NULL,
	event_name imos_name NOT NULL,
	event_time imos_timeslice NOT NULL,
	shared_time imos_timeslice NOT NULL,
	expired_time imos_timeslice NOT NULL,
	shared_code imos_code NOT NULL, 
	shared_user_list imos_code[]
);
ALTER TABLE tbl_test ADD CONSTRAINT pk_tbl_test_event_code PRIMARY KEY(event_code);
-- ALTER TABLE tbl_test ADD CONSTRAINT fk_tbl_test_shared_code FOREIGN KEY(shared_code)
	REFERENCES tbl_user(user_code) MATCH SIMPLE ON UPDATE NO ACTION ON DELETE CASCADE;

• 创建函数

CREATE OR REPLACE FUNCTION func_update_tbl_test()
  RETURNS trigger AS
$BODY$
BEGIN
	execute 'update   tbl_test  set shared_user_list=array_remove(shared_user_list,'''||OLD.shared_code||''')	where shared_user_list @> ''{'||OLD.shared_code||'}'';';
	RETURN OLD;
END;
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;

• 创建触发器

CREATE TRIGGER trig_update_tbl_test AFTER DELETE ON tbl_test FOR EACH ROW EXECUTE PROCEDURE func_update_tbl_test();

• 插入数据进行测试

insert into tbl_test values
('event_code01','event_name01','event_time01','shared_time01','expired_time01','001','{002,003}'),
('event_code02','event_name02','event_time02','shared_time02','expired_time02','002','{001,004}'),
('event_code03','event_name03','event_time03','shared_time03','expired_time03','003','{001,004}'),
('event_code04','event_name04','event_time04','shared_time04','expired_time04','004','{001,002}'),
('event_code05','event_name05','event_time05','shared_time05','expired_time05','005','{001,002}');

• 查看数据

postgres=# select * from tbl_test;
  event_code  |  event_name  |  event_time  |  shared_time  |  expired_time  | shared_code | shared_user_list 
--------------+--------------+--------------+---------------+----------------+-------------+------------------
 event_code01 | event_name01 | event_time01 | shared_time01 | expired_time01 | 001         | {002,003}
 event_code02 | event_name02 | event_time02 | shared_time02 | expired_time02 | 002         | {001,004}
 event_code03 | event_name03 | event_time03 | shared_time03 | expired_time03 | 003         | {001,004}
 event_code04 | event_name04 | event_time04 | shared_time04 | expired_time04 | 004         | {001,002}
 event_code05 | event_name05 | event_time05 | shared_time05 | expired_time05 | 005         | {001,002}
(5 rows)

• 删除共享用户

postgres=# delete from tbl_test where shared_code='004';
DELETE 1

• 检测结果

postgres=# select * from tbl_test;
  event_code  |  event_name  |  event_time  |  shared_time  |  expired_time  | shared_code | shared_user_list 
--------------+--------------+--------------+---------------+----------------+-------------+------------------
 event_code01 | event_name01 | event_time01 | shared_time01 | expired_time01 | 001         | {002,003}
 event_code05 | event_name05 | event_time05 | shared_time05 | expired_time05 | 005         | {001,002}
 event_code02 | event_name02 | event_time02 | shared_time02 | expired_time02 | 002         | {001}
 event_code03 | event_name03 | event_time03 | shared_time03 | expired_time03 | 003         | {001}
(4 rows)