I believe that it is a headache to add a partition table. If there are monthly partitions and daily partitions, And the partition is done through the suffix of _ yyyymmdd or _ yyyymm, you can use this function to add the partition CREATEORREPLACEFUNCTIONf_add_partition () RETURNSvoidLANGUAGEplpgsqlAS $ function $ declarev _
I believe that it is a headache to add a partition table. If there are monthly partitions and daily partitions, And the partition is done through the suffix of _ yyyymmdd or _ yyyymm, you can use this FUNCTION to add the partition create or replace function f_add_partition () RETURNS voidLANGUAGE plpgsqlAS $ FUNCTION $ declarev _
I believe that it is a headache to add a partition table. If there are monthly partitions and daily partitions, And the partition is performed using the suffix "_ yyyymmdd" or "_ yyyymm, you can use this function to add partitions.
Create or replace function f_add_partition () RETURNS void LANGUAGE plpgsqlAS $ function $ declarev_max_childname text; -- maximum sub-table name v_parentname text; -- parent table name v_suffix text corresponding to the sub-table; -- subtable date suffix SQL text; -- the SQL statement GRA_TO text to be executed; -- grant permission to the statement begin -- retrieve the maximum Partition Table and parent table name for v_max_childname, v_parentname in select max (inhrelid: regclass: text), inhparent: regclass from pg_inherits where inhparent: regclass: text not like '%. %' Group by inhparent loop raise notice 'maximum sub-table: %, parent table: % ', v_max_childname, v_parentname; SQL = 'select split_part (''' | v_max_childname | ''', ''' _ '', (length (''' | v_max_childname | ''') -length (replace (''' | v_max_childname | ''', ''' _ '', ''') + 1 ))'; -- whether to extract the date by month or by day execute SQL into v_suffix; -- save the retrieved date to v_suffix while (length (v_suffix) = 6 and v_suffix <'20140901') -- determine if it is by month, then execute the table creation statement cyclically and grant the permission loop v_suffix = to_char (To_timestamp (v_suffix, 'yyyymmm ') + interval '1 month', 'yyyymmm '); -- When the partition table is retrieved by month + 1 SQL = 'create table' | v_parentname | '_' | v_suffix | '(like' | v_parentname | 'including all) inherits ('| v_parentname |') '; execute SQL; for GRA_TO in execute 'select' grant ''| privilege_type | 'on' | v_parentname | '_' | v_suffix | 'to'' | grantee from information_schema.table_privileges where table_name = ''' | V_max_childname | ''' loop execute GRA_TO; end loop; while (length (v_suffix) = 8 and v_suffix <'20140901 ') -- If the partition is by day, execute the following table creation statement cyclically and grant the permission loop v_suffix = to_char (to_timestamp (v_suffix, 'yyyymmdd') + interval '1 Day ', 'yyyymmdd'); SQL = 'create table' | v_parentname | '_' | v_suffix | '(like' | v_parentname | 'including all) inherits ('| v_parentname |') '; execute SQL; for GRA_TO in execute' Select ''grant ''| privilege_type | ''on' | v_parentname | '_' | v_suffix | 'to'' | grantee from information_schema.table_privileges where table_name = ''' | v_max_childname | ''' loop execute GRA_TO; end loop; raise notice 'partition table has be created successfully! '; End; $ function $
\ Dt hank | tbl | table | Thank you | tbl_20140322 | table | Thank you | test | table | Thank you | test_201405 | table | hankselect f_add_partition (); \ dt
Hank | tbl | table | hank | tbl_20140322 | table | the number of days when the function is used to partition is too large. Here, 10 thousand rows are omitted ..... Thank you! | tbl_20151230 | table | Thank you! | tbl_20151231 | table | thank you!
Less on a monthly basis, all of them have been pasted out. hank | test | table | thank you, thank you. | test_201405 | table | Thank you. | test_201407 | table | Thank you. | test_201408 | table | Thank you. | | test_201409 | table | thank you for choosing test_201410 | table | thank you for choosing test_201411 | table | thank you for choosing test_201412 | table | thank you for choosing test_201501 | table | thank you for choosing test_201502 | table | thank you for choosing test_201503 | table | thank you for choosing test_201504 | table | thank you for choosing test_201505 | table | thank you for choosing test_201506 | table | thank you for choosing test_201507 | table | thank you for choosing test_201508 table | thank-you-go | test_201509 | table | thank-you-go | test_201510 | table | thank-you-go | test_201511 | table | thank-you-go | test_201512 | table | thank-you-go
