PG数组以及触发器应用

最新推荐文章于 2025-06-07 09:22:15 发布

原创最新推荐文章于 2025-06-07 09:22:15 发布 · 244 阅读

0 ·

CC 4.0 BY-SA版权

文章标签：

#PG #PG数组 #PG触发器

PostgreSQL 专栏收录该内容

158 篇文章

订阅专栏

本文详细介绍了在PostgreSQL中创建表、函数、触发器的过程，并通过具体示例展示了如何实现数据更新时的共享用户列表自动维护。通过触发器与自定义函数的结合使用，确保了数据的一致性和完整性。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

建表

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)