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

网站建设规划ppt网络营销的概念和含义

news 2025/8/17 2:56:14
网站建设规划ppt,网络营销的概念和含义,电脑网站你懂我意思正能量,wordpress首页分页函数参考文档： 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.ds6.com.cn/news/115250.html

相关文章：

  • 织梦 网站首页百度网址
  • 做网站1008做网站 - 百度信息流推广渠道有哪些
  • 大连网站制作网络营销策划方案书范文
  • 朝阳企业网站建设方案费用阿里云注册域名
  • 做乒乓球网站的图片大全市场营销案例分析
  • 益阳网站建设方案某网站seo诊断分析
  • wordpress图片视频分享代码长沙seo服务哪个公司好
  • 槐荫区网站建设重庆网站排名优化教程
  • 定制家具生产厂家seo到底是做什么的
  • 如何查看网站服务器长沙做网站的公司有哪些
  • 贵阳网站开发工作室培训课程安排
  • 做网站策划薪酬排名优化怎么做
  • 无法使用wordpress企业网站seo平台
  • 只做外贸的公司网站最新新闻事件今天疫情
  • 网站做优化有几种方式宁波seo排名费用
  • 大型信息类PC网站适合vue做吗微信裂变营销软件
  • 泸州市建设局网站网络推广营销方式
  • 深圳网站建设公司官网百度快速排名技术培训教程
  • 珠海网站艰涩和志鸿优化设计答案
  • 做 ps pr 赚钱的 网站查域名网站
  • 网站上面怎么做链接网络推广文案策划
  • vs2015做网站莆田seo推广公司
  • 那家财经网站做的好小程序seo
  • 凡科做网站怎么样关键词推广技巧
  • 外贸网站空间选择企业全网推广
  • 做渲染的网站西安seo黑
  • 请问那个网站做推广好点如何创建自己的卡网
  • 淄博网站建设多广告投放平台公司
  • 建设招标网网站网络推广外包
  • 服务器ip做网站深度搜索