--添加分区表
CREATE OR REPLACE FUNCTION create_table(table_name character varying,table_num integer)
RETURNS void
LANGUAGE plpgsql
AS $function$
declare
v_date char(8);
v_tablename varchar(64);
begin
for i in 0..table_num loop
v_date :=to_char(current_date + i,‘YYYYMMDD‘);
v_tablename := table_name ||‘_‘|| v_date;
execute ‘create table ‘ || v_tablename ||‘(like ‘||table_name||‘ including all) inherits(‘||table_name||‘)‘;
execute ‘grant select on ‘ || v_tablename || ‘ to dwetl‘;
end loop;
end
$function$;
--删除分区表
CREATE OR REPLACE FUNCTION drop_table(table_name character varying,table_num integer)
RETURNS void
LANGUAGE plpgsql
AS $function$
declare
v_date char(8);
v_tablename varchar(64);
begin
for i in 0..table_num loop
v_date :=to_char(current_date + i,‘YYYYMMDD‘);
v_tablename := table_name ||‘_‘|| v_date;
execute ‘drop table ‘ || v_tablename;
end loop;
end
$function$;
--执行函数
select create_table(‘table_name‘,table_num);
select drop_table(‘table_name‘,table_num);
--删除函数
drop FUNCTION create_table(table_name character varying,table_num integer); #必须写上输入参数
