4Pm55kH4

1-- Upgrading old partitioning with ALTER TABLEs and PLPGSQL.
2-- IN: _orig_table - master table which should be upgraded
3-- IN: _partkey - column which used as partition key
4-- IN: _seq_col - sequence column
5CREATE OR REPLACE FUNCTION fn_upgrade_partitioning(_orig_table text, _partkey text, _seq_col text) RETURNS void AS 
6$function$
7       DECLARE 
8               _new_table text = _orig_table ||'_new';         -- parent relation's name 
9               _child_table text;                              -- child relation's name 
10               _v_from timestamp without time zone; 
11               _v_to timestamp without time zone; 
12               _seq_name text = split_part(pg_get_serial_sequence(_orig_table, _seq_col), '.', 2); 
13       BEGIN 
14               -- create new table and attach existing sequence 
15               EXECUTE format($$CREATE TABLE IF NOT EXISTS %I (LIKE %I) PARTITION BY RANGE (%I)$$, _new_table, _orig_table, _partkey); 
16               EXECUTE format($$ALTER SEQUENCE %I OWNED BY %I.%I$$, _seq_name, _new_table, _seq_col); 
17               EXECUTE format($$ALTER TABLE %I ALTER COLUMN %I SET DEFAULT nextval('%I')$$, _new_table, _seq_col, _seq_name); 
18               -- loop over partitions 
19               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) 
20               LOOP 
21                       -- detach partition 
22                       EXECUTE format('ALTER TABLE %I NO INHERIT %I', _child_table, _orig_table); 
23                       -- calculate FROM and TO values and attach partition 
24                       SELECT to_date(split_part(_child_table, '_', 2), 'YYYYMMDD')::timestamp without time zone INTO _v_from; 
25                       SELECT to_date(split_part(_child_table, '_', 2), 'YYYYMMDD')::timestamp without time zone + interval '1 month' INTO _v_to; 
26                       EXECUTE format($$ALTER TABLE %I ATTACH PARTITION %I FOR VALUES FROM ('%s') TO ('%s')$$, _new_table, _child_table, _v_from, _v_to); 
27                       RAISE NOTICE '% reattached from % to %', _child_table, _orig_table, _new_table; 
28               END LOOP; 
29               -- drop old parent table and rename new one 
30               EXECUTE format('DROP TABLE %I', _orig_table); 
31               EXECUTE format('ALTER TABLE %I RENAME TO %I', _new_table, _orig_table); 
32               RAISE NOTICE 'partitioning for % has been upgraded.', _orig_table; 
33       END; 
34$function$ LANGUAGE plpgsql;