1CREATE FUNCTION @extschema@.dump_partitioned_table_definition( 2 p_parent_table TEXT, 3 p_ignore_template_table BOOLEAN DEFAULT false 4) RETURNS TEXT 5 LANGUAGE PLPGSQL STABLE 6AS $$ 7DECLARE 8 v_create_parent_definition TEXT; 9 v_update_part_config_definition TEXT; 10 -- Columns from part_config table. 11 v_parent_table TEXT; -- NOT NULL 12 v_control TEXT; -- NOT NULL 13 v_partition_type TEXT; -- NOT NULL 14 v_partition_interval TEXT; -- NOT NULL 15 v_constraint_cols TEXT[]; 16 v_premake integer; -- NOT NULL 17 v_optimize_trigger integer; -- NOT NULL 18 v_optimize_constraint integer; -- NOT NULL 19 v_epoch text; -- NOT NULL 20 v_inherit_fk BOOLEAN; -- NOT NULL 21 v_retention TEXT; 22 v_retention_schema TEXT; 23 v_retention_keep_table BOOLEAN; -- NOT NULL 24 v_retention_keep_index BOOLEAN; -- NOT NULL 25 v_infinite_time_partitions BOOLEAN; -- NOT NULL 26 v_datetime_string TEXT; 27 v_automatic_maintenance TEXT; -- NOT NULL 28 v_jobmon BOOLEAN; -- NOT NULL 29 v_sub_partition_set_full BOOLEAN; -- NOT NULL 30 v_trigger_exception_handling BOOLEAN; 31 v_upsert TEXT; -- NOT NULL 32 v_trigger_return_null BOOLEAN; -- NOT NULL 33 v_template_table TEXT; 34 v_publications TEXT[]; 35 v_inherit_privileges BOOLEAN; -- DEFAULT false 36 v_constraint_valid BOOLEAN; -- DEFAULT true NOT NULL 37 v_subscription_refresh text; 38BEGIN 39 SELECT 40 pc.parent_table, 41 pc.control, 42 pc.partition_type, 43 pc.partition_interval, 44 pc.constraint_cols, 45 pc.premake, 46 pc.optimize_trigger, 47 pc.optimize_constraint, 48 pc.epoch, 49 pc.inherit_fk, 50 pc.retention, 51 pc.retention_schema, 52 pc.retention_keep_table, 53 pc.retention_keep_index, 54 pc.infinite_time_partitions, 55 pc.datetime_string, 56 pc.automatic_maintenance, 57 pc.jobmon, 58 pc.sub_partition_set_full, 59 pc.trigger_exception_handling, 60 pc.upsert, 61 pc.trigger_return_null, 62 pc.template_table, 63 pc.publications, 64 pc.inherit_privileges, 65 pc.constraint_valid, 66 pc.subscription_refresh 67 INTO 68 v_parent_table, 69 v_control, 70 v_partition_type, 71 v_partition_interval, 72 v_constraint_cols, 73 v_premake, 74 v_optimize_trigger, 75 v_optimize_constraint, 76 v_epoch, 77 v_inherit_fk, 78 v_retention, 79 v_retention_schema, 80 v_retention_keep_table, 81 v_retention_keep_index, 82 v_infinite_time_partitions, 83 v_datetime_string, 84 v_automatic_maintenance, 85 v_jobmon, 86 v_sub_partition_set_full, 87 v_trigger_exception_handling, 88 v_upsert, 89 v_trigger_return_null, 90 v_template_table, 91 v_publications, 92 v_inherit_privileges, 93 v_constraint_valid, 94 v_subscription_refresh 95 FROM @extschema@.part_config pc 96 WHERE pc.parent_table = p_parent_table; 97 98 IF v_partition_type = 'partman' THEN 99 CASE 100 WHEN v_partition_interval::INTERVAL = '1 year'::INTERVAL THEN 101 v_partition_interval := 'yearly'; 102 WHEN v_partition_interval::INTERVAL = '3 months'::INTERVAL THEN 103 v_partition_interval := 'quarterly'; 104 WHEN v_partition_interval::INTERVAL = '1 month'::INTERVAL THEN 105 v_partition_interval := 'monthly'; 106 WHEN v_partition_interval::INTERVAL = '1 week'::INTERVAL THEN 107 v_partition_interval := 'weekly'; 108 WHEN v_partition_interval::INTERVAL = '1 day'::INTERVAL THEN 109 v_partition_interval := 'daily'; 110 WHEN v_partition_interval::INTERVAL = '1 hour'::INTERVAL THEN 111 v_partition_interval := 'hourly'; 112 WHEN v_partition_interval::INTERVAL = '30 mins'::INTERVAL THEN 113 v_partition_interval := 'half-hour'; 114 WHEN v_partition_interval::INTERVAL = '15 mins'::INTERVAL THEN 115 v_partition_interval := 'quarter-hour'; 116 ELSE 117 RAISE EXCEPTION 'Partitioning interval not recognized for "partman" partitioning type'; 118 END CASE; 119 END IF; 120 121 IF v_partition_type = 'native' AND p_ignore_template_table THEN 122 v_template_table := NULL; 123 END IF; 124 125 v_create_parent_definition := format( 126E'SELECT @extschema@.create_parent( 127\tp_parent_table := %L, 128\tp_control := %L, 129\tp_type := %L, 130\tp_interval := %L, 131\tp_constraint_cols := %L, 132\tp_premake := %s, 133\tp_automatic_maintenance := %L, 134\tp_inherit_fk := %L, 135\tp_epoch := %L, 136\tp_upsert := %L, 137\tp_publications := %L, 138\tp_trigger_return_null := %L, 139\tp_template_table := %L, 140\tp_jobmon := %L 141\t-- v_start_partition is intentionally ignored as there 142\t-- isn''t any obviously correct definition. 143);', 144 v_parent_table, 145 v_control, 146 v_partition_type, 147 v_partition_interval, 148 v_constraint_cols, 149 v_premake, 150 v_automatic_maintenance, 151 v_inherit_fk, 152 v_epoch, 153 v_upsert, 154 v_publications, 155 v_trigger_return_null, 156 v_template_table, 157 v_jobmon 158 ); 159 160 v_update_part_config_definition := format( 161E'UPDATE @extschema@.part_config SET 162\toptimize_trigger = %s, 163\toptimize_constraint = %s, 164\tretention = %L, 165\tretention_schema = %L, 166\tretention_keep_table = %L, 167\tretention_keep_index = %L, 168\tinfinite_time_partitions = %L, 169\tdatetime_string = %L, 170\tsub_partition_set_full = %L, 171\ttrigger_exception_handling = %L, 172\tinherit_privileges = %L, 173\tconstraint_valid = %L, 174\tsubscription_refresh = %L 175WHERE parent_table = %L;', 176 v_optimize_trigger, 177 v_optimize_constraint, 178 v_retention, 179 v_retention_schema, 180 v_retention_keep_table, 181 v_retention_keep_index, 182 v_infinite_time_partitions, 183 v_datetime_string, 184 v_sub_partition_set_full, 185 v_trigger_exception_handling, 186 v_inherit_privileges, 187 v_constraint_valid, 188 v_subscription_refresh, 189 v_parent_table 190 ); 191 192 RETURN concat_ws(E'\n', 193 v_create_parent_definition, 194 v_update_part_config_definition 195 ); 196END 197$$; 198 199 200