@@ -108,7 +108,7 @@ CREATE OR REPLACE FUNCTION @extschema@.create_partitions_from_range(
108
108
, p_attributeTEXT
109
109
, p_start_value ANYELEMENT
110
110
, p_end_value ANYELEMENT
111
- ,p_count INTEGER )
111
+ ,p_interval ANYELEMENT )
112
112
RETURNSINTEGER AS
113
113
$$
114
114
DECLARE
@@ -117,12 +117,12 @@ DECLARE
117
117
v_dt_interval INTERVAL;
118
118
v_type REGTYPE;
119
119
v_is_date BOOL;
120
- iINTEGER ;
120
+ iINTEGER : = 0 ;
121
121
BEGIN
122
122
p_relation := @extschema@.validate_relname(p_relation);
123
123
124
- IFp_count <= 0 THEN
125
- RAISE EXCEPTION' Partitions count must begreater than zero ' ;
124
+ IFp_interval <= 0 THEN
125
+ RAISE EXCEPTION' Interval must bepositive ' ;
126
126
END IF;
127
127
128
128
IF EXISTS (SELECT * FROM @extschema@.pathman_configWHERE relname= p_relation) THEN
@@ -135,49 +135,71 @@ BEGIN
135
135
INSERT INTO @extschema@.pathman_config (relname, attname, parttype)
136
136
VALUES (p_relation, p_attribute,2 );
137
137
138
- v_type := pg_typeof(p_start_value);
139
- v_interval := (p_end_value- p_start_value)/ cast(p_countas DOUBLE PRECISION );
138
+ WHILE p_start_value<= p_end_value
139
+ LOOP
140
+ PERFORM @extschema@.create_single_range_partition(p_relation
141
+ , p_start_value
142
+ , p_start_value+ p_interval);
143
+ p_start_value := p_start_value+ p_interval;
144
+ i := i+ 1 ;
145
+ END LOOP;
140
146
141
- IF v_typeIN (' date' ::regtype,' timestamp' ::regtype,' timestamptz' ::regtype) THEN
142
- v_is_date= TRUE;
143
- IF v_interval BETWEEN30 AND 31 THEN
144
- v_dt_interval := ' 1 month' ;
145
- ELSIF v_interval BETWEEN365 AND 366 THEN
146
- v_dt_interval := ' 1 year' ;
147
- ELSE
148
- v_dt_interval := format(' %s day' , ceil(v_interval));
149
- END IF;
150
- ELSIF v_typeIN (' integer' ::regtype,' smallint' ::regtype,' bigint' ::regtype) THEN
151
- v_interval := ceil(v_interval);
152
- END IF;
147
+ /* Create triggers*/
148
+ PERFORM @extschema@.create_range_insert_trigger(p_relation, p_attribute);
153
149
154
- /* create first partition*/
155
- IF v_is_date THEN
156
- FOR iIN 1 ..p_count
157
- LOOP
158
- p_end_value := p_start_value+ v_dt_interval;
159
- PERFORM @extschema@.create_single_range_partition(p_relation
160
- , p_start_value
161
- , p_end_value);
162
- p_start_value := p_end_value;
163
- END LOOP;
164
- ELSE
165
- FOR iIN 1 ..p_count
166
- LOOP
167
- PERFORM @extschema@.create_single_range_partition(p_relation
168
- , p_start_value
169
- , p_start_value+ v_interval);
170
- p_start_value := p_start_value+ v_interval;
171
- END LOOP;
150
+ /* Notify backend about changes*/
151
+ PERFORM @extschema@.on_create_partitions(p_relation::regclass::oid );
152
+
153
+ RETURN i;
154
+ END
155
+ $$ LANGUAGE plpgsql;
156
+
157
+ /*
158
+ * Creates RANGE partitions for specified range based on datetime attribute
159
+ */
160
+ CREATEOR REPLACE FUNCTION @extschema@.create_partitions_from_range(
161
+ p_relationTEXT
162
+ , p_attributeTEXT
163
+ , p_start_value ANYELEMENT
164
+ , p_end_value ANYELEMENT
165
+ , p_interval INTERVAL)
166
+ RETURNSINTEGER AS
167
+ $$
168
+ DECLARE
169
+ v_valueTEXT ;
170
+ v_intervalDOUBLE PRECISION ;
171
+ v_dt_interval INTERVAL;
172
+ v_type REGTYPE;
173
+ v_is_date BOOL;
174
+ iINTEGER := 0 ;
175
+ BEGIN
176
+ p_relation := @extschema@.validate_relname(p_relation);
177
+
178
+ IF EXISTS (SELECT * FROM @extschema@.pathman_configWHERE relname= p_relation) THEN
179
+ RAISE EXCEPTION' Relation "%" has already been partitioned' , p_relation;
172
180
END IF;
173
181
182
+ EXECUTE format(' DROP SEQUENCE IF EXISTS %s_seq' , p_relation);
183
+ EXECUTE format(' CREATE SEQUENCE %s_seq START 1' , p_relation);
184
+
185
+ INSERT INTO @extschema@.pathman_config (relname, attname, parttype)
186
+ VALUES (p_relation, p_attribute,2 );
187
+
188
+ WHILE p_start_value<= p_end_value
189
+ LOOP
190
+ EXECUTE format(' SELECT @extschema@.create_single_range_partition($1, $2, $3::%s);' , pg_typeof(p_start_value))
191
+ USING p_relation, p_start_value, p_start_value+ p_interval;
192
+ p_start_value := p_start_value+ p_interval;
193
+ i := i+ 1 ;
194
+ END LOOP;
195
+
174
196
/* Create triggers*/
175
197
PERFORM @extschema@.create_range_insert_trigger(p_relation, p_attribute);
176
198
177
199
/* Notify backend about changes*/
178
200
PERFORM @extschema@.on_create_partitions(p_relation::regclass::oid );
179
201
180
- RETURNp_count ;
202
+ RETURNi ;
181
203
END
182
204
$$ LANGUAGE plpgsql;
183
205