PGSQL表分区

本来想应用表分区把每天数据按小时分开，结果pgsql表在copy方式导入数据时不支持规则。不过函数写好了就不浪费了，保存在这里做个备份吧。

分区方式接口表
CREATE OR REPLACE FUNCTION func_create_table(text) RETURNS integer AS
$BODY$
declare
table_count integer;
BEGIN
 select into table_count count(*) from pg_tables where tablename='pv_data_'||$1;
 if table_count <> 0 then
  return 0;
 else
  execute('CREATE TABLE pv_data_'||$1||' () INHERITS (pv_data);');
  FOR i IN 0..24 LOOP   
   IF i < 10 THEN
    execute('CREATE TABLE pv_data_'||$1||'_'||i||' (CHECK(createtime >= '''||$1||'0'||i||'0000'' and createtime <= '''||$1||'0'||i||'5959'')) INHERITS (pv_data_'||$1||');');
    execute('CREATE RULE rule_pv_data_'||$1||'_'||i||' as on insert to pv_data_'||$1||' where createtime >= '''||$1||'0'||i||'0000'' and createtime <= '''||$1||'0'||i||'5959'' do instead insert into pv_data_'||$1||'_'||i||' values (NEW.codeid,NEW.logfile,NEW.uid,NEW.url_domain,NEW.url_dir,NEW.url_file,NEW.refer_domain,NEw.refer_dir,NEW.refer_file,NEw.s,NEW.k,NEW.iploc,NEw.ip,NEw.createtime);');
   ELSE
    execute('CREATE TABLE pv_data_'||$1||'_'||i||' (CHECK(createtime >= '''||$1||i||'0000'' and createtime <= '''||$1||i||'5959'')) INHERITS (pv_data_'||$1||');');
    execute('CREATE RULE rule_pv_data_'||$1||'_'||i||' as on insert to pv_data_'||$1||' where createtime >= '''||$1||i||'0000'' and createtime <= '''||$1||i||'5959'' do instead insert into pv_data_'||$1||'_'||i||' values (NEW.codeid,NEW.logfile,NEW.uid,NEW.url_domain,NEW.url_dir,NEW.url_file,NEW.refer_domain,NEw.refer_dir,NEW.refer_file,NEw.s,NEW.k,NEW.iploc,NEw.ip,NEw.createtime);');
   END IF;
  END LOOP;
  return 1;
 end if;
END;
$BODY$
LANGUAGE 'plpgsql';

CREATE OR REPLACE FUNCTION func_drop_table(text) RETURNS integer AS
$BODY$
declare
table_count integer;
BEGIN  
 execute('DROP TABLE pv_data_'||$1||' CASCADE;');
 return 1;
END;
$BODY$
LANGUAGE 'plpgsql';