Performs RANGE partitioning for`relation` by partitioning key`attribute`,`start_value` argument specifies initial value,`p_interval` sets the default range for auto created partitions or partitions created with`append_range_partition()` or`prepend_range_partition()` (if`NULL` then auto partition creation feature won't work),`p_count` is the number of premade partitions (if not set then`pg_pathman` tries to determine it based on attribute values). Partition creation callback is invoked for each partition if set beforehand.

create_partitions_from_range(relation REGCLASS,

attributeTEXT,

start_value ANYELEMENT,

end_value ANYELEMENT,

p_interval ANYELEMENT,

partition_dataBOOLEAN DEFAULT TRUE)

create_partitions_from_range(relation REGCLASS,

attributeTEXT,

start_value ANYELEMENT,

end_value ANYELEMENT,

p_interval INTERVAL,

partition_dataBOOLEAN DEFAULT TRUE)

Performs RANGE-partitioning from specified range for`relation` by partitioning key`attribute`. Partition creation callback is invoked for each partition if set beforehand.

DROP TABLE test.insert_into_select_copy, test.insert_into_select CASCADE;

NOTICE: drop cascades to 6 other objects

/* Test INSERT hooking with DATE type */

CREATE TABLE test.insert_date_test(val DATE NOT NULL);

SELECT pathman.create_partitions_from_range('test.insert_date_test', 'val',

date '20161001', date '20170101', interval '1 month');

create_partitions_from_range

------------------------------

4

(1 row)

INSERT INTO test.insert_date_test VALUES ('20161201'); /* just insert the date */

SELECT count(*) FROM pathman.pathman_partition_list WHERE parent = 'test.insert_date_test'::REGCLASS;

count

-------

4

(1 row)

INSERT INTO test.insert_date_test VALUES ('20170311'); /* append new partitions */

SELECT count(*) FROM pathman.pathman_partition_list WHERE parent = 'test.insert_date_test'::REGCLASS;

count

-------

6

(1 row)

INSERT INTO test.insert_date_test VALUES ('20160812'); /* prepend new partitions */

SELECT count(*) FROM pathman.pathman_partition_list WHERE parent = 'test.insert_date_test'::REGCLASS;

count

-------

8

(1 row)

SELECT min(val) FROM test.insert_date_test; /* check first date */

min

------------

08-12-2016

(1 row)

SELECT max(val) FROM test.insert_date_test; /* check last date */

max

------------

03-11-2017

(1 row)

DROP TABLE test.insert_date_test CASCADE;

NOTICE: drop cascades to 9 other objects

SET pg_pathman.enable_runtimeappend = OFF;

SET pg_pathman.enable_runtimemergeappend = OFF;

VACUUM;

{12-31-2014,01-02-2015}

(1 row)

SELECT pathman.drop_partitions('test."RangeRel"');

NOTICE: 0 rows copied from test."RangeRel_1"

NOTICE: 1 rows copied from test."RangeRel_2"

NOTICE: 1 rows copied from test."RangeRel_3"

NOTICE: 0 rows copied from test."RangeRel_4"

NOTICE: 1 rows copied from test."RangeRel_6"

drop_partitions

-----------------

5

(1 row)

SELECT pathman.create_partitions_from_range('test."RangeRel"', 'dt', '2015-01-01'::DATE, '2015-01-05'::DATE, '1 day'::INTERVAL);

create_partitions_from_range

------------------------------

5

(1 row)

DROP TABLE test."RangeRel" CASCADE;

NOTICE: drop cascades to 6 other objects

SELECT * FROM pathman.pathman_config;

3

(1 row)

SELECT pathman.drop_partitions('test."RangeRel"');

NOTICE: 0 rows copied from test."RangeRel_1"

NOTICE: 0 rows copied from test."RangeRel_2"

NOTICE: 0 rows copied from test."RangeRel_3"

drop_partitions

-----------------

3

(1 row)

SELECT pathman.create_partitions_from_range('test."RangeRel"', 'id', 1, 300, 100);

create_partitions_from_range

------------------------------

3

(1 row)

DROP TABLE test."RangeRel" CASCADE;

NOTICE: drop cascades to 4 other objects

DROP EXTENSION pg_pathman;

14

(1 row)

SELECT create_partitions_from_range('test.range_rel', 'id', 1, 1000, 100);

create_partitions_from_range

------------------------------

10

(1 row)

SELECT drop_partitions('test.range_rel', TRUE);

drop_partitions

-----------------

10

(1 row)

SELECT create_partitions_from_range('test.range_rel', 'dt', '2015-01-01'::date, '2015-12-01'::date, '1 month'::interval);

create_partitions_from_range

------------------------------

12

(1 row)

EXPLAIN (COSTS OFF) SELECT * FROM test.range_rel WHERE dt = '2015-12-15';

QUERY PLAN

--------------------------------------------------------------------------------

Append

-> Seq Scan on range_rel_12

Filter: (dt = 'Tue Dec 15 00:00:00 2015'::timestamp without time zone)

(3 rows)

/* Test NOT operator */

CREATE TABLE bool_test(a INT NOT NULL, b BOOLEAN);

SELECT create_hash_partitions('bool_test', 'a', 3);

DROP TABLE test.provided_part_names CASCADE;

NOTICE: drop cascades to 2 other objects

DROP SCHEMA test CASCADE;

NOTICE: drop cascades to42 other objects

NOTICE: drop cascades to29 other objects

DROP EXTENSION pg_pathman CASCADE;

DROP SCHEMA pathman CASCADE;

$$

LANGUAGE plpgsql;

CREATEOR REPLACE FUNCTION @extschema@.create_partitions_from_range(

parent_relidREGCLASS,

expressionTEXT,

start_valueANYELEMENT,

end_valueANYELEMENT,

p_intervalANYELEMENT,

partition_dataBOOLEAN DEFAULT TRUE)

RETURNSINTEGERAS $$

DECLARE

part_countINTEGER :=0;

PERFORM @extschema@.prepare_for_partitioning(parent_relid,

expression,

partition_data);

PERFORM @extschema@.check_boundaries(parent_relid,

expression,

start_value,

end_value);

PERFORM @extschema@.create_naming_sequence(parent_relid);

PERFORM @extschema@.add_to_pathman_config(parent_relid, expression,

p_interval::TEXT);

WHILE start_value<= end_value

LOOP

PERFORM @extschema@.create_single_range_partition(

parent_relid,

start_value,

start_value+ p_interval,

tablespace := @extschema@.get_tablespace(parent_relid));

start_value := start_value+ p_interval;

part_count := part_count+1;

END LOOP;

IF partition_data= true THEN

PERFORM @extschema@.set_enable_parent(parent_relid, false);

PERFORM @extschema@.partition_data(parent_relid);

ELSE

PERFORM @extschema@.set_enable_parent(parent_relid, true);

END IF;

RETURN part_count;/* number of created partitions*/

END

$$ LANGUAGE plpgsql;

CREATEOR REPLACE FUNCTION @extschema@.create_partitions_from_range(

parent_relidREGCLASS,

expressionTEXT,

start_valueANYELEMENT,

end_valueANYELEMENT,

p_intervalINTERVAL,

partition_dataBOOLEAN DEFAULT TRUE)

RETURNSINTEGERAS $$

DECLARE

part_countINTEGER :=0;

PERFORM @extschema@.prepare_for_partitioning(parent_relid,

expression,

partition_data);

PERFORM @extschema@.check_boundaries(parent_relid,

expression,

start_value,

end_value);

PERFORM @extschema@.create_naming_sequence(parent_relid);

PERFORM @extschema@.add_to_pathman_config(parent_relid, expression,

p_interval::TEXT);

WHILE start_value<= end_value

LOOP

EXECUTE

format('SELECT @extschema@.create_single_range_partition($1, $2, $3::%s, tablespace:=$4);',

@extschema@.get_base_type(pg_typeof(start_value))::TEXT)

USING

parent_relid,

start_value,

start_value+ p_interval,

@extschema@.get_tablespace(parent_relid);

start_value := start_value+ p_interval;

part_count := part_count+1;

END LOOP;

IF partition_data= true THEN

PERFORM @extschema@.set_enable_parent(parent_relid, false);

PERFORM @extschema@.partition_data(parent_relid);

ELSE

PERFORM @extschema@.set_enable_parent(parent_relid, true);

END IF;

RETURN part_count;/* number of created partitions*/

END

$$ LANGUAGE plpgsql;

DROPTABLEtest.insert_into_select_copy,test.insert_into_select CASCADE;

CREATETABLEtest.insert_date_test(valDATENOT NULL);

SELECTpathman.create_partitions_from_range('test.insert_date_test','val',

date'20161001',date'20170101', interval'1 month');

INSERT INTOtest.insert_date_testVALUES ('20161201');/* just insert the date*/

SELECTcount(*)FROMpathman.pathman_partition_listWHERE parent='test.insert_date_test'::REGCLASS;

INSERT INTOtest.insert_date_testVALUES ('20170311');/* append new partitions*/

SELECTcount(*)FROMpathman.pathman_partition_listWHERE parent='test.insert_date_test'::REGCLASS;

INSERT INTOtest.insert_date_testVALUES ('20160812');/* prepend new partitions*/

SELECTcount(*)FROMpathman.pathman_partition_listWHERE parent='test.insert_date_test'::REGCLASS;

SELECTmin(val)FROMtest.insert_date_test;/* check first date*/

SELECTmax(val)FROMtest.insert_date_test;/* check last date*/

DROPTABLEtest.insert_date_test CASCADE;

SETpg_pathman.enable_runtimeappend= OFF;

SETpg_pathman.enable_runtimemergeappend= OFF;

SELECTpathman.prepend_range_partition('test."RangeRel"');

SELECTpathman.merge_range_partitions('test."RangeRel_1"','test."RangeRel_'|| currval('test."RangeRel_seq"')||'"');

SELECTpathman.split_range_partition('test."RangeRel_1"','2015-01-01'::DATE);

SELECTpathman.drop_partitions('test."RangeRel"');

SELECTpathman.create_partitions_from_range('test."RangeRel"','dt','2015-01-01'::DATE,'2015-01-05'::DATE,'1 day'::INTERVAL);

DROPTABLE test."RangeRel" CASCADE;

SELECT*FROMpathman.pathman_config;

CREATETABLEtest."RangeRel" (

idSERIALPRIMARY KEY,

dtTIMESTAMPNOT NULL,

txtTEXT);

SELECTpathman.create_range_partitions('test."RangeRel"','id',1,100,3);

SELECTpathman.drop_partitions('test."RangeRel"');

SELECTpathman.create_partitions_from_range('test."RangeRel"','id',1,300,100);

DROPTABLE test."RangeRel" CASCADE;

DROP EXTENSION pg_pathman;

SELECT drop_partitions('test.range_rel');

SELECT create_partitions_from_range('test.range_rel','id',1,1000,100);

SELECT drop_partitions('test.range_rel', TRUE);

SELECT create_partitions_from_range('test.range_rel','dt','2015-01-01'::date,'2015-12-01'::date,'1 month'::interval);

EXPLAIN (COSTS OFF)SELECT*FROMtest.range_relWHERE dt='2015-12-15';

CREATETABLEbool_test(aINTNOT NULL, bBOOLEAN);