begin;
\set ECHO queries
create extension range_partitioning;

create schema part_test;


select  where_clause('x','(,)','int4range') as w1,
        where_clause('d','(,2015-01-01)','daterange') as w2,
        where_clause('y','[4,5]','int4range') as w3,
        where_clause('z','[4,5)','int4range') as w4,
        where_clause('z','empty','int4range') as w5;


create view p_info
as
select  c.relname, p.partition_number, p.range
from    partition p
join    pg_class c
on      c.oid = p.partition_class;

create table part_test.foo( x integer );

select create_parent('part_test.foo','x');
select * from p_info order by 1,2;

select  mt.relname as table_name,
        pt.relname as partition_name,
        cons.consrc
from    pg_namespace vs
join    pg_class mt
on      vs.oid = mt.relnamespace
        and
        mt.relkind = 'r'
join    pg_inherits i
on      i.inhparent = mt.oid
join    pg_class pt
on      pt.oid = i.inhrelid
        and
        pt.relkind = 'r'
join    pg_constraint cons
on      cons.conrelid = pt.oid
        and
        cons.contype = 'c'
where   vs.nspname = 'part_test'
and     mt.relname = 'foo'
order by 1,2,3;

select create_partition('part_test.foo','[5000,)');
select * from p_info order by 1,2;

select exists( select null from pg_class where relname = 'foo_p0');

select exists( select null from pg_class where relname = 'foo_p1');

insert into part_test.foo(x)
values (10),(4999),(5000),(15000);

select * from part_test.foo_p0 order by 1;

select * from part_test.foo_p1 order by 1;

select create_partition('part_test.foo','[10000,)');
select * from p_info order by 1,2;

select * from part_test.foo_p0 order by 1;

select * from part_test.foo_p1 order by 1;

select * from part_test.foo_p2 order by 1;

select master_class::regclass::text as m, partition_class::regclass::text as p, range, where_clause(partition_class) as sql
from partition
order by 1,2;

select drop_partition('part_test.foo_p1','part_test.foo_p0');
select * from p_info order by 1,2;

select * from part_test.foo_p0 order by 1;

select * from part_test.foo_p2 order by 1;

select drop_partition('part_test.foo_p0','part_test.foo_p2');
select * from p_info order by 1,2;

select * from part_test.foo_p2 order by 1;

create type range_partitioning_textrange_c as range (subtype = text, collation = "C");

create table part_test.bar(str text collate "C");

select exists( select null from pg_attribute where attrelid = 'part_test.bar'::regclass and attname = 'str' );

select create_parent('part_test.bar','str');
select * from p_info order by 1,2;

select create_partition('part_test.bar','(,A)');
select * from p_info order by 1,2;

select create_partition('part_test.bar','[A,C)');
select * from p_info order by 1,2;


insert into part_test.bar
values ('ABEL'),('BAKER'),('CHARLIE');

select * from part_test.bar_p0 order by 1;

select * from part_test.bar_p1 order by 1;

select * from part_test.bar_p2 order by 1;

select master_class::regclass::text as m, partition_class::regclass::text as p, range, where_clause(partition_class) as sql
from partition
order by 1,2;

select drop_partition('part_test.bar_p1','part_test.bar_p2');
select * from p_info order by 1,2;

select * from part_test.bar_p0 order by 1;

select * from part_test.bar_p2 order by 1;

set search_path = public;

select partition_class::regclass::text, refresh_exclusion_constraint(partition_class) from partition order by 1;

rollback;