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