当前位置： 首页 > news >正文

杭州商城网站建设温州seo结算

news 2025/7/21 11:42:14
杭州商城网站建设,温州seo结算,上海内贸网站建设,政务微信小程序参考文档： http://postgres.cn/docs/12/ddl-partitioning.html 创建基于继承的分区表的步骤 1 创建父表 2 创建子表，从父表继承过来 3 创建函数及触发器，使插入的数据根据规则，插入到对应的子表中 -- 创建父表 CREATE TABLE a…

参考文档：
http://postgres.cn/docs/12/ddl-partitioning.html

创建基于继承的分区表的步骤
1 创建父表
2 创建子表，从父表继承过来
3 创建函数及触发器，使插入的数据根据规则，插入到对应的子表中 

-- 创建父表

CREATE TABLE apps.measurement (city_id         int not null,logdate         date not null,peaktemp        int,unitsales       int
);test=# CREATE TABLE apps.measurement (
test(#     city_id         int not null,
test(#     logdate         date not null,
test(#     peaktemp        int,
test(#     unitsales       int
test(# );
CREATE TABLE
test=#

-- 创建分区表，5个分区 

CREATE TABLE apps.measurement_y2023m01 (CHECK ( logdate >= DATE '2023-01-01' AND logdate < DATE '2023-02-01' )) INHERITS (measurement);
CREATE TABLE apps.measurement_y2023m02 (CHECK ( logdate >= DATE '2023-02-01' AND logdate < DATE '2023-03-01' )) INHERITS (measurement);
CREATE TABLE apps.measurement_y2023m03 (CHECK ( logdate >= DATE '2023-03-01' AND logdate < DATE '2023-04-01' )) INHERITS (measurement);
CREATE TABLE apps.measurement_y2023m04 (CHECK ( logdate >= DATE '2023-04-01' AND logdate < DATE '2023-05-01' )) INHERITS (measurement);
CREATE TABLE apps.measurement_y2023m05 (CHECK ( logdate >= DATE '2023-05-01' AND logdate < DATE '2023-06-01' )) INHERITS (measurement);test=# CREATE TABLE apps.measurement_y2023m01 (CHECK ( logdate >= DATE '2023-01-01' AND logdate < DATE '2023-02-01' )) INHERITS (measurement);
CREATE TABLE
test=# CREATE TABLE apps.measurement_y2023m02 (CHECK ( logdate >= DATE '2023-02-01' AND logdate < DATE '2023-03-01' )) INHERITS (measurement);
CREATE TABLE
test=# CREATE TABLE apps.measurement_y2023m03 (CHECK ( logdate >= DATE '2023-03-01' AND logdate < DATE '2023-04-01' )) INHERITS (measurement);
CREATE TABLE
test=# CREATE TABLE apps.measurement_y2023m04 (CHECK ( logdate >= DATE '2023-04-01' AND logdate < DATE '2023-05-01' )) INHERITS (measurement);
CREATE TABLE
test=# CREATE TABLE apps.measurement_y2023m05 (CHECK ( logdate >= DATE '2023-05-01' AND logdate < DATE '2023-06-01' )) INHERITS (measurement);
CREATE TABLE
test=#

-- 创建触发器函数，针对不同月份的数据，落入不同的子表，从而达到数据分区的效果

CREATE OR REPLACE FUNCTION apps.measurement_insert_trigger()
RETURNS TRIGGER AS $$
BEGINIF (NEW.logdate >= DATE '2023-01-01' AND  NEW.logdate < DATE '2023-02-01') THEN INSERT INTO apps.measurement_y2023m01 values (NEW.*);ELSIF (NEW.logdate >= DATE '2023-02-01' AND  NEW.logdate < DATE '2023-03-01') THEN INSERT INTO apps.measurement_y2023m02 values (NEW.*);ELSIF (NEW.logdate >= DATE '2023-03-01' AND  NEW.logdate < DATE '2023-04-01') THEN INSERT INTO apps.measurement_y2023m03 values (NEW.*);ELSIF (NEW.logdate >= DATE '2023-04-01' AND  NEW.logdate < DATE '2023-05-01') THEN INSERT INTO apps.measurement_y2023m04 values (NEW.*);ELSIF (NEW.logdate >= DATE '2023-05-01' AND  NEW.logdate < DATE '2023-06-01') THEN INSERT INTO apps.measurement_y2023m05 values (NEW.*);	  ELSE RAISE EXCEPTION 'Date out of range .Fix the apps.measurement_insert_trigger() function!';END IF;RETURN NULL;
END;
$$
LANGUAGE plpgsql;test=# CREATE OR REPLACE FUNCTION apps.measurement_insert_trigger()
test-# RETURNS TRIGGER AS $$
test$# BEGIN
test$#       IF (NEW.logdate >= DATE '2023-01-01' AND  NEW.logdate < DATE '2023-02-01') THEN 
test$#   INSERT INTO apps.measurement_y2023m01 values (NEW.*);
test$#   
test$#   ELSE IF (NEW.logdate >= DATE '2023-02-01' AND  NEW.logdate < DATE '2023-03-01') THEN 
test$#   INSERT INTO apps.measurement_y2023m02 values (NEW.*);
test$#   
test$#   ELSE IF (NEW.logdate >= DATE '2023-03-01' AND  NEW.logdate < DATE '2023-04-01') THEN 
test$#   INSERT INTO apps.measurement_y2023m03 values (NEW.*);
test$#   
test$#   ELSE IF (NEW.logdate >= DATE '2023-04-01' AND  NEW.logdate < DATE '2023-05-01') THEN 
test$#   INSERT INTO apps.measurement_y2023m04 values (NEW.*);
test$#   
test$#   ELSE IF (NEW.logdate >= DATE '2023-05-01' AND  NEW.logdate < DATE '2023-06-01') THEN 
test$#   INSERT INTO apps.measurement_y2023m05 values (NEW.*);
test$#   
test$#   ELSE 
test$#       RAISE EXCEPTION 'Date out of range .Fix the apps.measurement_insert_trigger() function! ';
test$#   END IF;
test$#   RETURN NULL;
test$# END;
test$# $$
test-# LANGUAGE plpgsql;
CREATE FUNCTION
test=#

-- 创建触发器  ，不带schema，创建的触发器，默认就在apps的schema中

CREATE TRIGGER APPS.INSERT_MEASUREMENT_TRIGGER       <<<<<< 奇怪，触发器带schema，创建的时候报错 ，BEFORE INSERT ON APPS.MEASUREMENTFOR EACH ROW EXECUTE FUNCTION APPS.MEASUREMENT_INSERT_TRIGGER();CREATE or replace  TRIGGER INSERT_MEASUREMENT_TRIGGERBEFORE INSERT ON APPS.MEASUREMENTFOR EACH ROW EXECUTE FUNCTION APPS.MEASUREMENT_INSERT_TRIGGER();	test=# CREATE TRIGGER APPS.INSERT_MEASUREMENT_TRIGGER
test-#     BEFORE INSERT ON APPS.MEASUREMENT
test-#     FOR EACH ROW EXECUTE FUNCTION APPS.MEASUREMENT_INSERT_TRIGGER();
ERROR:  syntax error at or near "."
LINE 1: CREATE TRIGGER APPS.INSERT_MEASUREMENT_TRIGGER^
test=# CREATE or replace  TRIGGER INSERT_MEASUREMENT_TRIGGER
test-#     BEFORE INSERT ON APPS.MEASUREMENT
test-#     FOR EACH ROW EXECUTE FUNCTION APPS.MEASUREMENT_INSERT_TRIGGER();
CREATE TRIGGER
test=#

-- 插入数据

CREATE TABLE apps.measurement (city_id         int not null,logdate         date not null,peaktemp        int,unitsales       int
);insert into apps.measurement values (1,date '2023-01-02',2,2);
insert into apps.measurement values (2,date '2023-02-02',2,2);	
insert into apps.measurement values (3,date '2023-03-02',2,2);	
insert into apps.measurement values (4,date '2023-04-02',2,2);	
insert into apps.measurement values (5,date '2023-05-02',2,2);	test=# insert into apps.measurement values (1,date '2023-01-02',2,2);
INSERT 0 0
test=# insert into apps.measurement values (2,date '2023-02-02',2,2);
INSERT 0 0
test=# insert into apps.measurement values (3,date '2023-03-02',2,2);
INSERT 0 0
test=# insert into apps.measurement values (4,date '2023-04-02',2,2);
INSERT 0 0
test=# insert into apps.measurement values (5,date '2023-05-02',2,2);
INSERT 0 0
test=#

-- 查看父表和各个分区表的数据 ，可以看到1个父表，5个分区表

test=# \dList of relationsSchema |         Name         | Type  |  Owner   
--------+----------------------+-------+----------apps   | measurement          | table | postgresapps   | measurement_y2023m01 | table | postgresapps   | measurement_y2023m02 | table | postgresapps   | measurement_y2023m03 | table | postgresapps   | measurement_y2023m04 | table | postgresapps   | measurement_y2023m05 | table | postgresapps   | persons              | table | postgresapps   | students             | table | postgresapps   | t1                   | table | postgres
(9 rows)test=# test=# select * from measurement;city_id |  logdate   | peaktemp | unitsales 
---------+------------+----------+-----------1 | 2023-01-02 |        2 |         22 | 2023-02-02 |        2 |         23 | 2023-03-02 |        2 |         24 | 2023-04-02 |        2 |         25 | 2023-05-02 |        2 |         2
(5 rows)test=# test=# select * from measurement_y2023m01;city_id |  logdate   | peaktemp | unitsales 
---------+------------+----------+-----------1 | 2023-01-02 |        2 |         2
(1 row)test=# select * from measurement_y2023m02;city_id |  logdate   | peaktemp | unitsales 
---------+------------+----------+-----------2 | 2023-02-02 |        2 |         2
(1 row)test=# select * from measurement_y2023m03;city_id |  logdate   | peaktemp | unitsales 
---------+------------+----------+-----------3 | 2023-03-02 |        2 |         2
(1 row)test=# select * from measurement_y2023m04;city_id |  logdate   | peaktemp | unitsales 
---------+------------+----------+-----------4 | 2023-04-02 |        2 |         2
(1 row)test=# select * from measurement_y2023m05;city_id |  logdate   | peaktemp | unitsales 
---------+------------+----------+-----------5 | 2023-05-02 |        2 |         2
(1 row)test=#

-- 插入2023年6月份的数据 ，直接报错 ，因为此时没有创建6月份的子表，触发器函数中也没有处理6月份数据的规则

insert into apps.measurement values (1,date '2023-06-02',2,2);
test=# insert into apps.measurement values (1,date '2023-06-02',2,2);
ERROR:  Date out of range .Fix the apps.measurement_insert_trigger() function!
CONTEXT:  PL/pgSQL function measurement_insert_trigger() line 23 at RAISE
test=#

-- 创建6月份分区表，

修改触发器函数。使6月份的数据可以插入进去

CREATE TABLE apps.measurement_y2023m06 (CHECK ( logdate >= DATE '2023-06-01' AND logdate < DATE '2023-07-01' )) INHERITS (measurement);CREATE OR REPLACE FUNCTION apps.measurement_insert_trigger()
RETURNS TRIGGER AS $$
BEGINIF (NEW.logdate >= DATE '2023-01-01' AND  NEW.logdate < DATE '2023-02-01') THEN INSERT INTO apps.measurement_y2023m01 values (NEW.*);ELSIF (NEW.logdate >= DATE '2023-02-01' AND  NEW.logdate < DATE '2023-03-01') THEN INSERT INTO apps.measurement_y2023m02 values (NEW.*);ELSIF (NEW.logdate >= DATE '2023-03-01' AND  NEW.logdate < DATE '2023-04-01') THEN INSERT INTO apps.measurement_y2023m03 values (NEW.*);ELSIF (NEW.logdate >= DATE '2023-04-01' AND  NEW.logdate < DATE '2023-05-01') THEN INSERT INTO apps.measurement_y2023m04 values (NEW.*);ELSIF (NEW.logdate >= DATE '2023-05-01' AND  NEW.logdate < DATE '2023-06-01') THEN INSERT INTO apps.measurement_y2023m05 values (NEW.*);	  ELSIF (NEW.logdate >= DATE '2023-06-01' AND                             <<<<<<<  新加入的规则NEW.logdate < DATE '2023-07-01') THEN INSERT INTO apps.measurement_y2023m06 values (NEW.*);ELSE RAISE EXCEPTION 'Date out of range .Fix the apps.measurement_insert_trigger() function!';END IF;RETURN NULL;
END;
$$
LANGUAGE plpgsql;
test=# insert into apps.measurement values (1,date '2023-06-02',2,2);       <<<<<< 插入6月份数据 
INSERT 0 0
test=# select * from measurement_y2023m06;                                  <<<<<<  查询分区数据 city_id |  logdate   | peaktemp | unitsales 
---------+------------+----------+-----------1 | 2023-06-02 |        2 |         2
(1 row)test=#

-- 通过视图查看分区表，查询不到基于继承建立的分区表

test=# select * from pg_partitioned_table ;partrelid | partstrat | partnatts | partdefid | partattrs | partclass | partcollation | partexprs 
-----------+-----------+-----------+-----------+-----------+-----------+---------------+-----------
(0 rows)test=#

--通过dt+ 命令查看建立的父表和子表

test=# \dt+ apps.measurement*List of relationsSchema |         Name         | Type  |  Owner   | Persistence | Access method |    Size    | Description 
--------+----------------------+-------+----------+-------------+---------------+------------+-------------apps   | measurement          | table | postgres | permanent   | heap          | 0 bytes    | apps   | measurement_y2023m01 | table | postgres | permanent   | heap          | 8192 bytes | apps   | measurement_y2023m02 | table | postgres | permanent   | heap          | 8192 bytes | apps   | measurement_y2023m03 | table | postgres | permanent   | heap          | 8192 bytes | apps   | measurement_y2023m04 | table | postgres | permanent   | heap          | 8192 bytes | apps   | measurement_y2023m05 | table | postgres | permanent   | heap          | 8192 bytes | apps   | measurement_y2023m06 | table | postgres | permanent   | heap          | 8192 bytes | 
(7 rows)test=#

END

http://www.zhongyajixie.com/news/44312.html

相关文章：

  • 网站总浏览量上海百度关键词优化公司
  • 深圳高端网站制作公司排名下店拓客团队
  • 四川省住房和城乡建设厅官网站网营销推广的特点
  • 邳州做网站的公司合肥seo按天收费
  • 网站建设流程和费用seo会被取代吗
  • java做网站需要哪些技术网站流量数据分析
  • 临汾尚世互联网站建设怎么收录网站
  • 杭州房产网签流程搜索引擎优化内容包括哪些方面
  • 校园网站建设的系统分析网站服务器失去响应
  • 做动态网站必学南宁seo排名优化
  • 做网站需要什么认证怎样制作免费网页
  • 临武县网站建设营销策划的概念
  • 在哪家公司建设网站好站长之家音效
  • 网站里的个人中心下拉列表怎么做优化工具箱
  • 鄂州做网站如何搭建一个网站
  • 做造价在那个网站比较好郴州seo外包
  • 什么网站做首页郑州网络推广服务
  • 政务服务网站建设方案html友情链接代码
  • 个人业务网站建设网络营销的发展趋势
  • 东莞网站建设方案维护查排名官网
  • 郑州市网站建设公司市场营销公司
  • 网站的通知栏一般用什么控件做seo是什么意思蜘蛛屯
  • wordpress建站教程mac搜索数据
  • 做自己的网站的一般步骤福州seo推广服务
  • 建网站要花费多少钱黄冈网站推广软件
  • 乐歌股份摄像头甘肃新站优化
  • 中国建设银行官网网址多少站长seo推广
  • 微信微网站怎么进入微信群推广网站
  • 网站开发的目的百度网登录入口
  • 做幼儿英语的教案网站营销推广策划