通过 FUNCTION 创建大量子表

在PG里,用到分区表的时候需要创建大量的子表,今天有个项目,有个分区表已经很大了,一个月的数据有300G左右,目前这张分区表是以月分区,现在需要将它改为以日分区了, 下面是通过创建一个 FUNCTION 来创建日表。

创建父表

1
2
3
4
5
6
7
8
9
10
11
12
13
14
CREATE TABLE tbl_tmp (  
id bigint DEFAULT nextval('public.tbl_tmp_id_seq'::regclass),
host character varying(50),
remoteip character varying(50),
ntime timestamp without time zone,
reqdesc character varying(2000),
httpcode character varying(10),
clength integer,
refer character varying(2000),
agent character varying(50),
hsman character varying,
hstype character varying(50),
imsi character varying(50)
);

创建建表函数

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
CREATE OR REPLACE FUNCTION skytf.create_table()  
RETURNS void
LANGUAGE plpgsql
AS $function$
declare
v_date char(8);
v_tablename varchar(64);
begin

for i in 0..1500 loop
v_date :=to_char(current_date + i,'YYYYMMDD');
v_tablename := 'tbl_tmp_' || v_date;
execute 'create table ' || v_tablename || ' (like tbl_tmp including constraints including indexes including defaults) inherits (tbl_tmp)';
execute 'grant select on skytf.' || v_tablename || ' to dwetl';
end loop;
end
$function$;

说明:上面function有一段是 ‘grant…’ ,这段代码在创建表的同时也把权限给加上。

执行建表函数

1
skytf=> select create_table();

验证

1
2
3
4
5
6
7
8
9
10
11
12
13
14
skytf=> \d  
List of relations
Schema | Name | Type | Owner
---------+-----------------------------------+----------+---------
skytf | tbl_tmp_201012 | table | skytf
skytf | tbl_tmp_20101224 | table | skytf
skytf | tbl_tmp_20101225 | table | skytf
skytf | tbl_tmp_20101226 | table | skytf
skytf | tbl_tmp_20101227 | table | skytf
skytf | tbl_tmp_20101228 | table | skytf
skytf | tbl_tmp_20101229 | table | skytf
skytf | tbl_tmp_20101230 | table | skytf
..
..

以上省略了大部分输出结果,说明子表已创建。

最后推荐和张文升共同编写的《PostgreSQL实战》,本书基于PostgreSQL 10 编写,共18章,重点介绍SQL高级特性、并行查询、分区表、物理复制、逻辑复制、备份恢复、高可用、性能优化、PostGIS等,涵盖大量实战用例!

购买链接:https://item.jd.com/12405774.html

PostgreSQL实战
感谢支持!
0%