通过 FUNCTION 创建大量子表

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

创建父表

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)  
);

创建建表函数

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…’ ，这段代码在创建表的同时也把权限给加上。

执行建表函数

skytf=> select create_table();

验证

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
..
..

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