Upgrading old partitioning with ALTER TABLEs and PLPGSQL.

fn_upgrade_partitioning.sql

-- Upgrading old partitioning with ALTER TABLEs and PLPGSQL.
-- IN: _orig_table - master table which should be upgraded
-- IN: _partkey - column which used as partition key
-- IN: _seq_col - sequence column
CREATE OR REPLACE FUNCTION fn_upgrade_partitioning(_orig_table text, _partkey text, _seq_col text) RETURNS void AS 
$function$
       DECLARE 
               _new_table text = _orig_table ||'_new';         -- parent relation's name 
               _child_table text;                              -- child relation's name 
               _v_from timestamp without time zone; 
               _v_to timestamp without time zone; 
               _seq_name text = split_part(pg_get_serial_sequence(_orig_table, _seq_col), '.', 2); 
       BEGIN 
               -- create new table and attach existing sequence 
               EXECUTE format($$CREATE TABLE IF NOT EXISTS %I (LIKE %I) PARTITION BY RANGE (%I)$$, _new_table, _orig_table, _partkey); 
               EXECUTE format($$ALTER SEQUENCE %I OWNED BY %I.%I$$, _seq_name, _new_table, _seq_col); 
               EXECUTE format($$ALTER TABLE %I ALTER COLUMN %I SET DEFAULT nextval('%I')$$, _new_table, _seq_col, _seq_name); 
               -- loop over partitions 
               FOR _child_table IN (SELECT c.relname FROM pg_inherits JOIN pg_class AS c ON (inhrelid=c.oid) JOIN pg_class AS p ON (inhparent=p.oid) WHERE p.relname = _orig_table) 
               LOOP 
                       -- detach partition 
                       EXECUTE format('ALTER TABLE %I NO INHERIT %I', _child_table, _orig_table); 
                       -- calculate FROM and TO values and attach partition 
                       SELECT to_date(split_part(_child_table, '_', 2), 'YYYYMMDD')::timestamp without time zone INTO _v_from; 
                       SELECT to_date(split_part(_child_table, '_', 2), 'YYYYMMDD')::timestamp without time zone + interval '1 month' INTO _v_to; 
                       EXECUTE format($$ALTER TABLE %I ATTACH PARTITION %I FOR VALUES FROM ('%s') TO ('%s')$$, _new_table, _child_table, _v_from, _v_to); 
                       RAISE NOTICE '% reattached from % to %', _child_table, _orig_table, _new_table; 
               END LOOP; 
               -- drop old parent table and rename new one 
               EXECUTE format('DROP TABLE %I', _orig_table); 
               EXECUTE format('ALTER TABLE %I RENAME TO %I', _new_table, _orig_table); 
               RAISE NOTICE 'partitioning for % has been upgraded.', _orig_table; 
       END; 
$function$ LANGUAGE plpgsql;