PostgreSQL, there are no partition table creation commands that are created by creating rules such as inheritance tables and constraints, and the steps are tedious and cumbersome, encapsulating a method. Easy to create partitioned tables:
CREATE TABLE "ODL"."User_action_fatt0"("date_id" Numeric(8,0),"chnl_id" Numeric(2,0),"User_acct_type" Numeric(2,0),"user_id" Numeric( +,0),"cont_id" Numeric( +,0),"act_id" Numeric(5,0),"Act_value" Numeric)
Create a partition function
CREATE FUNCTION"Public"."Partition_generate_numeric"(in tablename varchar, in start_date varchar, in end_date varchar, in ptype varchar, in pcolumn varchar) RETURNS"Text"As $BODY $ImportReImportDatetime def udf_date_add(lstr,day):s = Datetime.datetime.strptime (Lstr,"%y%m%d") s = S+datetime.timedelta (Days=day)returnSTR (s). Replace ('-',"')[0:8] Startdate=start_date enddate=end_dateifPType not inch(' Mon ',' Day '):return "Error:\tptype only support ' mon ' or ' Day '" ifptype==' Day ':if notRe.match (' [0-9]{8} ', StartDate):return "Error:\tstartdate need 20130101 format" if notRe.match (' [0-9]{8} ', EndDate):return "Error:\tenddate need 20130101 format" Try: table_name = Tablename.lower (). Split ('. ')[1] Table_schema = Tablename.lower (). Split ('. ')[0]except(Indexerror):return ' error:\ttablename need ' tableschema.table_name ' format ' while True:#1) Create the partition tablesql ="" " Create Table " ""+table_schema+"""."""+table_name+"""_"""+startdate+"" (Check ( "" ")+ Pcolumn +"" " >= ( " "")+ StartDate +"" :: Numeric) and " " "+ Pcolumn +"" < ( "" "+ Udf_date_add (StartDate,1) +"" " :: Numeric))) INHERITS ( "" "+table_schema+"""."""+table_name+""")""" #plpy. info (SQL) Try: Plpy.execute (SQL)except:Pass #2) Create the index for the partition tablesql ="" " Create INDEX" ""+table_name+"""_"""+startdate+"""_"""+pcolumn+"" On " " "+table_schema+"""."""+table_name+"""_"""+startdate+""" ("""+pcolumn+""")""" #plpy. info (SQL) Try: Plpy.execute (SQL)except:PassStartdate=udf_date_add (StartDate,1)ifStartdate>enddate: Break #2.0) Create the error tablesql ="" " Create Table " ""+table_schema+"""."""+table_name+"" " _error_ " ""+pcolumn+"" As select * from " " "+table_schema+"""."""+table_name+"" " Limit 0 " "" Try: Plpy.execute (SQL)except:Pass #3) Create the trigger for the partition tabletrigger_tmp=""Startdate=start_date while True: trigger_tmp=trigger_tmp+"" " elsif (NEW. " "")+pcolumn+"" " >= ( " "")+startdate+"" :: Numeric) and NEW. "" "+pcolumn+"" < ( "" "+udf_date_add (StartDate,1)+"" " :: Numeric)) Then INSERT into "" "+table_schema+"""."""+table_name+"""_"""+startdate+"" " VALUES (new.*); """Startdate=udf_date_add (StartDate,1)ifStartdate>udf_date_add (EndDate,365): Breaktrigger_tmp=trigger_tmp+"" else INSERT into " " "+table_schema+"""."""+table_name+"" " _error_ " ""+pcolumn+"" " VALUES (new.*); End If; """trigger_tmp=trigger_tmp[3:] sql ="" CREATE OR REPLACE FUNCTION " " "+table_schema+"""."""+table_name+"" " _insert_trigger () RETURNS trigger as $PROC $ BEGIN " ""+trigger_tmp+"" " RETURN NULL; END; $PROC $ LANGUAGE plpgsql " " " #plpy. info (SQL)Plpy.execute (SQL)#4) Create the Insert triggersql ="" " CREATE TRIGGER insert_ " ""+table_name+"" " _trigger before INSERT on" "+table_schema+"""."""+table_name+"" For each ROW EXECUTE PROCEDURE ""+table_schema+"""."""+table_name+"" " _insert_trigger () " "" #plpy. info (SQL) Try: Plpy.execute (SQL)except:Pass return "Success"$BODY $ LANGUAGE Plpythonu Cost -Called on NULL INPUT SECURITY INVOKER VOLATILE; ALTER FUNCTION"Public"."Partition_generate_numeric"(in tablename varchar, in start_date varchar, in end_date varchar, in ptype varchar, in pcolumn varchar) OWNER to"Brecom";
PostgreSQL Partition Table creation