Postgresql add partition table (monthly and per day)

Students who built partitioned tables believe that adding partitioned tables is a headache. If you have monthly partitions and daily partitions, and they are partitioned by the "_yyyymmdd" or "_yyyymm" suffix, you can use this function to add partitions.

CREATE OR REPLACE FUNCTION f_add_partition ()
 RETURNS void
 LANGUAGE plpgsql
AS $ function $
declare
v_max_childname text; --Maximum child table name
v_parentname text;-the name of the parent table corresponding to the child table
v_suffix text; --Subtable date suffix
sql text;-SQL statement to be executed
GRA_TO text; --Grant permission statement
begin
--Remove the largest partition table of the partition table and the name of the parent table
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 ‘Max child 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)) '; --Retrieve whether the date is monthly or daily
    execute sql into v_suffix;-store the retrieved date into v_suffix
    while (length (v_suffix) = 6 and v_suffix <‘201512’)-Judge if it is monthly, then execute the table creation statement cyclically and grant permissions
        loop
        v_suffix = to_char (to_timestamp (v_suffix, ’yyyymm’) + interval ‘1 month’, ’yyyymm’);-+ 1 on the date of the removed partition table
        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;
        end loop;
    while (length (v_suffix) = 8 and v_suffix <‘20151231’)-Judge if it is partitioned by day, execute the following table-building statement in a loop and grant permissions
        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;
        end loop;
    end loop;
    RAISE NOTICE ‘Partition table has be created successfully! ‘;
end;
$ function $

\ dt
 hank | tbl | table | hank
 hank | tbl_20140322 | table | hank
 hank | test | table | hank
 hank | test_201405 | table | hank
select f_add_partition ();
\ dt

 hank | tbl | table | hank
 hank | tbl_20140322 | table | hank
 There are too many displays by day, and 10,000 lines are omitted here. . . . .
 hank | tbl_20151230 | table | hank
 hank | tbl_20151231 | table | hank

Less monthly, so all of them are posted
 hank | test | table | hank
 hank | test_201405 | table | hank
 hank | test_201406 | table | hank
 hank | test_201407 | table | hank
 hank | test_201408 | table | hank
 hank | test_201409 | table | hank
 hank | test_201410 | table | hank
 hank | test_201411 | table | hank
 hank | test_201412 | table | hank
 hank | test_201501 | table | hank
 hank | test_201502 | table | hank
 hank | test_201503 | table | hank
 hank | test_201504 | table | hank
 hank | test_201505 | table | hank
 hank | test_201506 | table | hank
 hank | test_201507 | table | hank
 hank | test_201508 | table | hank
 hank | test_201509 | table | hank
 hank | test_201510 | table | hank
 hank | test_201511 | table | hank
 hank | test_201512 | table | hank