萬盛學電腦網

 萬盛學電腦網 >> 網絡編程 >> 編程語言綜合 >> postgresql建立刪除分區表

postgresql建立刪除分區表

  常經常添加分區表,和清理分區表,很簡單的2個函數 --添加分區表 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); #必須寫上輸入參數 drop FUNCTION drop_table(table_name character varying,table_num integer);

copyright © 萬盛學電腦網 all rights reserved