postgresql在分表的情况下，绑定触发器，使某个字段自动更新

最新推荐文章于 2024-05-19 17:03:37 发布
最新推荐文章于 2024-05-19 17:03:37 发布
阅读量388 收藏 6
点赞数 9
文章标签： postgresql 数据库
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/weixin_45448227/article/details/136537227
版权

要点：
1.postgresql分表需要使用触发器完成，分表后一个主表以及n个子表；
2.给某个字段，如（update_time）设置自动填充，仍需要创建触发器，但是不能光绑定主表，应该给每一个子表都绑定该触发器；
分表触发器代码

CREATE OR REPLACE FUNCTION "public"."auto_insert_sub_indicator_table"()
  RETURNS "pg_catalog"."trigger" AS $BODY$
DECLARE
    admission_date    text ;			-- 父表中用于分区的时间字段的名称（推送时间）	
    curMM               varchar(6);		-- 'YYYYMM'字串,用做分区子表的后缀
    isExist             boolean;		-- 分区子表,是否已存在
    startTime           text;
    endTime             text;
    strSQL              text;
    
-- 如果表名使用很多，也可以声明一个变量表示字表名称
BEGIN
    -- 调用前,必须首先初始化(时间字段名):admission_date [直接从调用参数中获取!!]
    -- 没有显示的声明参数，使用TG_ARGV[0]获取参数
    admission_date := TG_ARGV[0];


    -- 判断对应分区表 是否已经存在?
	EXECUTE 'SELECT $1.'||admission_date INTO strSQL USING NEW;
	curMM := to_char( strSQL::timestamp , 'YYYYMM' );
	select count(*) INTO isExist from pg_class where relname = (TG_TABLE_NAME||'_'||curMM);

	-- 若不存在, 则插入前需先创建子分区
	IF ( isExist = false ) THEN
        -- 创建子分区表，写明约束。TG_TABLE_NAME为主表的名字，分表将继承主表的所有字段，但不会继承主键和索引等，需要手动创建。		
        startTime := left(curMM, 4)||'-'||right(curMM, 2)||'-01 00:00:00';

        endTime := to_char( startTime::timestamp + interval '1 month', 'YYYY-MM-DD HH24:MI:SS');
        strSQL := 'CREATE TABLE IF NOT EXISTS '||'"'||TG_TABLE_NAME||'_'||curMM||'"'||
                  ' ( CHECK('||admission_date||'>='||''''|| startTime||'''' ||' AND '
                             ||admission_date||'< '||''''|| endTime ||''''||' )
                          ) INHERITS ('||'"'||TG_TABLE_NAME||'"'||') ;';
				EXECUTE strSQL;
				-- 创建主键，id主键只能保证单个表的唯一，多个子表可能会存在相同的主键。这里为子表创建复合主键
        strSQL := 'ALTER TABLE '||'"'||TG_RELNAME||'_'||curMM||'"'||' ADD PRIMARY KEY("patient_id","visit_id","section_id","field_id") ';
        EXECUTE strSQL;
        -- 创建索引（使用分表的字段），可选。 TODO： 现在先只为业务数据日期时间添加索引，后面有需要再加
        strSQL := 'CREATE INDEX '||'"'||TG_TABLE_NAME||'_'||curMM||'_INDEX_patient_id'||'"'||' ON '
                          ||'"'||TG_TABLE_NAME||'_'||curMM||'"'||' ("patient_id");';
        EXECUTE strSQL;
				strSQL := 'CREATE INDEX '||'"'||TG_TABLE_NAME||'_'||curMM||'_INDEX_visit_id'||'"'||' ON '
                          ||'"'||TG_TABLE_NAME||'_'||curMM||'"'||' ("visit_id");';
        EXECUTE strSQL;
				strSQL := 'CREATE INDEX '||'"'||TG_TABLE_NAME||'_'||curMM||'_INDEX_field_id'||'"'||' ON '
                          ||'"'||TG_TABLE_NAME||'_'||curMM||'"'||' ("field_id");';
        EXECUTE strSQL;
				strSQL := 'CREATE UNIQUE INDEX '||'"'||TG_TABLE_NAME||'_'||curMM||'_INDEX_unique'||'"'||' ON '
                          ||'"'||TG_TABLE_NAME||'_'||curMM||'"'||' ("patient_id","visit_id","section_id","field_id");';
        EXECUTE strSQL;
    	
    END IF;

    -- 插入数据到子分区(主键冲突时更新数据，否则新增数据)!
    strSQL := 'INSERT INTO '||'"'||TG_TABLE_NAME||'_'||curMM||'"'|| ' SELECT $1.*' ||
              ' on conflict (patient_id, visit_id, section_id, field_id) ' ||
              ' do update set ' ||
              ' patient_id = EXCLUDED.patient_id,' ||
              ' visit_id = EXCLUDED.visit_id,' ||
              ' section_id = EXCLUDED.section_id,' ||
              ' field_id = EXCLUDED.field_id,' ||
              ' field_code = EXCLUDED.field_code,' ||
							' field_value = EXCLUDED.field_value,' ||
							' field_name = EXCLUDED.field_name,' ||
							' update_time = EXCLUDED.update_time,' ||
              ' admission_date = EXCLUDED.admission_date' ;
	EXECUTE strSQL USING NEW;
	RETURN NULL;
	
END
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100

其中子表名是不断变化的，即：‘"’||TG_TABLE_NAME||‘_’||curMM||‘"’
只要把自动更新的触发器放到里面就可以了，每创建一个新子表，就为该子表绑定触发器。

CREATE OR REPLACE FUNCTION "public"."auto_insert_sub_indicator_table"()
  RETURNS "pg_catalog"."trigger" AS $BODY$
DECLARE
    admission_date    text ;			-- 父表中用于分区的时间字段的名称（推送时间）	
    curMM               varchar(6);		-- 'YYYYMM'字串,用做分区子表的后缀
    isExist             boolean;		-- 分区子表,是否已存在
    startTime           text;
    endTime             text;
    strSQL              text;
    
-- 如果表名使用很多，也可以声明一个变量表示字表名称
BEGIN
    -- 调用前,必须首先初始化(时间字段名):admission_date [直接从调用参数中获取!!]
    -- 没有显示的声明参数，使用TG_ARGV[0]获取参数
    admission_date := TG_ARGV[0];


    -- 判断对应分区表 是否已经存在?
	EXECUTE 'SELECT $1.'||admission_date INTO strSQL USING NEW;
	curMM := to_char( strSQL::timestamp , 'YYYYMM' );
	select count(*) INTO isExist from pg_class where relname = (TG_TABLE_NAME||'_'||curMM);

	-- 若不存在, 则插入前需先创建子分区
	IF ( isExist = false ) THEN
        -- 创建子分区表，写明约束。TG_TABLE_NAME为主表的名字，分表将继承主表的所有字段，但不会继承主键和索引等，需要手动创建。		
        startTime := left(curMM, 4)||'-'||right(curMM, 2)||'-01 00:00:00';

        endTime := to_char( startTime::timestamp + interval '1 month', 'YYYY-MM-DD HH24:MI:SS');
        strSQL := 'CREATE TABLE IF NOT EXISTS '||'"'||TG_TABLE_NAME||'_'||curMM||'"'||
                  ' ( CHECK('||admission_date||'>='||''''|| startTime||'''' ||' AND '
                             ||admission_date||'< '||''''|| endTime ||''''||' )
                          ) INHERITS ('||'"'||TG_TABLE_NAME||'"'||') ;';
				EXECUTE strSQL;
				-- *************************************
				-- *************************************
				-- *************************************
				-- 为每个分表绑定触发器，自动填充update_time
				strSQL := 'CREATE TRIGGER "auto_update_time" BEFORE UPDATE ON "public".'||'"'||TG_TABLE_NAME||'_'||curMM||'"'||
										'FOR EACH ROW
										EXECUTE PROCEDURE "public"."update_timestamp"();';
				EXECUTE strSQL;
				-- *************************************
				-- *************************************
				-- *************************************
				-- 创建主键，id主键只能保证单个表的唯一，多个子表可能会存在相同的主键。这里为子表创建复合主键
        strSQL := 'ALTER TABLE '||'"'||TG_RELNAME||'_'||curMM||'"'||' ADD PRIMARY KEY("patient_id","visit_id","section_id","field_id") ';
        EXECUTE strSQL;
        -- 创建索引（使用分表的字段），可选。 TODO： 现在先只为业务数据日期时间添加索引，后面有需要再加
        strSQL := 'CREATE INDEX '||'"'||TG_TABLE_NAME||'_'||curMM||'_INDEX_patient_id'||'"'||' ON '
                          ||'"'||TG_TABLE_NAME||'_'||curMM||'"'||' ("patient_id");';
        EXECUTE strSQL;
				strSQL := 'CREATE INDEX '||'"'||TG_TABLE_NAME||'_'||curMM||'_INDEX_visit_id'||'"'||' ON '
                          ||'"'||TG_TABLE_NAME||'_'||curMM||'"'||' ("visit_id");';
        EXECUTE strSQL;
				strSQL := 'CREATE INDEX '||'"'||TG_TABLE_NAME||'_'||curMM||'_INDEX_field_id'||'"'||' ON '
                          ||'"'||TG_TABLE_NAME||'_'||curMM||'"'||' ("field_id");';
        EXECUTE strSQL;
				strSQL := 'CREATE UNIQUE INDEX '||'"'||TG_TABLE_NAME||'_'||curMM||'_INDEX_unique'||'"'||' ON '
                          ||'"'||TG_TABLE_NAME||'_'||curMM||'"'||' ("patient_id","visit_id","section_id","field_id");';
        EXECUTE strSQL;

    END IF;

    -- 插入数据到子分区(主键冲突时更新数据，否则新增数据)!
    strSQL := 'INSERT INTO '||'"'||TG_TABLE_NAME||'_'||curMM||'"'|| ' SELECT $1.*' ||
              ' on conflict (patient_id, visit_id, section_id, field_id) ' ||
              ' do update set ' ||
              ' patient_id = EXCLUDED.patient_id,' ||
              ' visit_id = EXCLUDED.visit_id,' ||
              ' section_id = EXCLUDED.section_id,' ||
              ' field_id = EXCLUDED.field_id,' ||
              ' field_code = EXCLUDED.field_code,' ||
							' field_value = EXCLUDED.field_value,' ||
							' field_name = EXCLUDED.field_name,' ||
							' update_time = EXCLUDED.update_time,' ||
              ' admission_date = EXCLUDED.admission_date' ;
	EXECUTE strSQL USING NEW;
	RETURN NULL;
	
END
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100
Z狂想メ曲
关注
  • 9
    点赞
  • 踩
  • 6
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
[ruby on rails]PostgresSQL分区表、继承表、触发器trigger在rails中的应用
深度的博客
02-21 395
PostgresSQL 分区是把逻辑上的一个大表分割成物理上的几块。就是一个主表，会有很多的子表继承主表，字段和主表一样。一般都是建立一个主表，里面是空，然后每个分区都去继承它。分区不仅能带来性能的提升，还能带来管理和维护上的方便。在 PG 里面无论何时，都应保证主表里面是空的。
实战 PostgreSQL 分区表
PageThinker
03-04 1107
我们在之前的《实战PostgreSQL》 详细描述的 PostgreSQL 的版本差别、JSON/JSONB、全文检索等相关信息，PostgreSQL 远比上述三点内容，这篇文章将实战 PostgreSQL 分区表。 01，什么是分区表？ 如下图，分区表就是根据分区策略，将数据数据分散到不同的子表中，并通过父表建立关联关系，从而实现数据物理上的分区。 02，PostgreSQL 提供的分区表功...
java实现pgsql自动更新创建时间与更新时间的两种方式
FDJKDF的博客
10-13 1852
在 PostgreSQL 中，可以通过创建触发器来实现自动填充创建时间和更新时间。和，分别表示创建时间和更新时间。2.然后，我们可以创建两个触发器，一个用于在插入行时自动填充created_at字段，另一个用于在更新行时自动填充updated_at字段。如果你想重新定义该触发器，请先删除现有的触发器，然后再创建新的触发器。
Postgresql：触发器某个字段更新时执行，行插入或更新执行
qq_40985985的博客
11-26 5676
【Postgresql】触发器某个字段更新时执行，行插入或更新执行1. postgresql触发器2. 触发器的创建及示例1) 字段更新时，触发2) 行插入或更新时，触发3. 触发器的删除参考 1. postgresql触发器 触发器可以理解为一种绑定，在某个场景出现时自动做的操作，可以执行复杂的Procedure，也可以是简单的sql； 场景可以是：字段更新、行插入、行更新 步骤： 1）创建触发器被触发时，需要执行的函数方法； 2）创建触发器，并绑定函数； 2. 触发器的创建及示例 1) 字段
PostgreSQL表层级时间自动更新
硬水果糖
11-10 828
触发器（Trigger）是由事件自动触发执行的一种特殊的存储过程，触发事件可以是对一个表进行INSERT、UPDATE、DELETE等操作。，对我们需要做时间戳更新操作的表设置相应的触发器即可，只需触发器名称和表明替换。需要给每一个做类似操作的表都增加触发器，因此这种方法适用于小规模，区别于全局更细的特殊逻辑场景。要在插入数据的时候自动填充 create_at列的值，我们可以使用DEFAULT值，如下面所示。对于MySQL来说，其内部提供的函数对于创建时间和更新时间的字段的自动更新是相当容易的，但对于。
postgresql使用触发器进行分表操作
myth_g的博客
11-02 1112
1.首先准备测试的父表: 2.创建子表: create table info_2017( check ( year = '2017' ) ) INHERITS (info); create table info_2018( check ( year = '2018' ) ) INHERITS (info); 3.创建各表索引 create index ...
PostgreSQL分区表
liuzhilong的博客
08-27 4163
PostgreSQL分区表的特性和维护实践
数据库中分区表的详解，以postgresql数据库为例
weixin_73350116的博客
07-01 1520
postgresql的四种分区表详解
数据库——MySQL分库分表的演进和实践以及中间件的比较
小诚信驿站
09-20 9015
1、了解几个问题？ 0、什么是分库分表 把原本存储于一个库的数据分块存储到多个库上，把原本存储于一个表的数据分块存储到多个表上。 1、MySQL为什么分库分表 当一张表的数据达到几千万时，你查询一次所花的时间会变多，如果有联合查询的话，可能会死在那儿。分表的目的就在于此，减小数据库的负担，缩短查询时间。目前我们系统将近20亿数据每张表最大的接近519w条/表，每条数据大约3k，1131M将近...
postgresql 按小时分表（含触发器）的实现方式
09-08
主要介绍了postgresql 按小时分表（含触发器）的实现方式，本文给大家介绍的非常详细，具有一定的参考借鉴价值,需要的朋友可以参考下
PostgreSQL更新表时时间戳不会自动更新的解决方法
09-09
主要为大家详细介绍了PostgreSQL更新表时时间戳不会自动更新的解决方法，具有一定的参考价值，感兴趣的小伙伴们可以参考一下
在Windows下自动备份PostgreSQL的教程
09-10
主要介绍了在Windows下自动备份PostgreSQL的教程,主要通过编写一个简单的批处理脚本,需要的朋友可以参考下
Centos7安装PostgreSql全自动部署脚本完整版
08-07
①添加【postgres】用户，数据库安装在postgres用户目录【/home/postgres/disk_d/applications/service/postgresql】路径下， ②给新用户授权 ③解压数据库文件 ④安装数据库依赖包 ⑤编译数据库 ⑥安装数据库 ⑦...
Ubuntu 配置Samba
weixin_45842249的博客
05-15 252
测试中发现使用另一个ubuntu机器上的smbclient连接需要开放目标机器的139端口，而windows连 接需要开放目标机器的445端口，不清楚是不是固定的，可以自行尝试。user - smb.conf中添加的配置信息中[user]填的共享名。再重新尝试windows连接，如果能成功，原因就是ufw配置有问题。查看ufw的app list是否有samba。ip_addr - ubuntu的ip地址。此时可能时防火墙的问题，可以先将防火墙关闭。这里我设置的密码为123456。查看ufw的端口开放情况。
mysql事务(原理)
最新发布
m0_74347016的博客
05-19 590
mysql事务原理
python学习-使用pandas库分析excel表，并导出所需的表
SixSix的自留地
05-16 318
使用pandas库分析excel表中多个子表的数据
实战项目技术点（1）
qq_60396437的博客
05-15 1157
分页插件PageHelper 是 Mybatis 的一款功能强大、方便易用的分页插件，支持任何形式的单标、多表的分页查询。官网：https://pagehelper.github.io/。文件上传，是指将本地图片、视频、音频等文件上传到服务器，供其他用户浏览或下载的过程。
postgresql批量更新表中某个字段
03-17
要批量更新 PostgreSQL 中表的某个字段，可以使用UPDATE语句，格式如下： ``` UPDATE 表名 SET 字段名 = 新值 WHERE 条件; ``` 例如，要将表 "users" 中 "age" 字段全部加 1，可以使用以下语句： ``` UPDATE users SET age = age + 1; ``` 注意：在执行批量更新操作时，请务必谨慎操作并先备份数据，以防数据丢失。

“相关推荐”对你有帮助么？

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值