DROP VIEW v1;

DROP TABLE t1;

-- check that an auto-updatable view on a partitioned table works correctly

create tablep (a int, b int) partition by range (a, b);

create tablep1 (b int not null, a int not null) partition by range (b);

create tablep11 (likep1);

alter tablep11 drop a;

alter tablep11 add a int;

alter tablep11 drop a;

alter tablep11 add a int not null;

alter tablep1 attach partitionp11 for values from (2) to (5);

alter tablep attach partitionp1 for values from (1, 2) to (1, 10);

create viewpv as select * fromp;

insert intopv values (1, 2);

select tableoid::regclass, * fromp;

create tablept (a int, b int) partition by range (a, b);

create tablept1 (b int not null, a int not null) partition by range (b);

create tablept11 (likept1);

alter tablept11 drop a;

alter tablept11 add a int;

alter tablept11 drop a;

alter tablept11 add a int not null;

alter tablept1 attach partitionpt11 for values from (2) to (5);

alter tablept attach partitionpt1 for values from (1, 2) to (1, 10);

create viewptv as select * frompt;

insert intoptv values (1, 2);

select tableoid::regclass, * frompt;

tableoid | a | b

----------+---+---

p11 | 1 | 2

pt11 | 1 | 2

(1 row)

create viewpv_wco as select * fromp where a = 0 with check option;

insert intopv_wco values (1, 2);

ERROR: new row violates check option for view "pv_wco"

create viewptv_wco as select * frompt where a = 0 with check option;

insert intoptv_wco values (1, 2);

ERROR: new row violates check option for view "ptv_wco"

DETAIL: Failing row contains (2, 1).

drop viewpv, pv_wco;

drop tablep, p1, p11;

drop viewptv, ptv_wco;

drop tablept, pt1, pt11;

DROPTABLE t1;

createtablep (aint, bint) partition by range (a, b);

createtablep1 (bintnot null, aintnot null) partition by range (b);

createtablep11 (likep1);

altertablep11 drop a;

altertablep11 add aint;

altertablep11 drop a;

altertablep11 add aintnot null;

altertablep1 attach partitionp11 forvaluesfrom (2) to (5);

altertablep attach partitionp1 forvaluesfrom (1,2) to (1,10);

createviewpvasselect*fromp;

insert intopvvalues (1,2);

select tableoid::regclass,*fromp;

createviewpv_wcoasselect*frompwhere a=0 withcheck option;

insert intopv_wcovalues (1,2);

dropviewpv, pv_wco;

droptablep, p1, p11;

createtablept (aint, bint) partition by range (a, b);

createtablept1 (bintnot null, aintnot null) partition by range (b);

createtablept11 (likept1);

altertablept11 drop a;

altertablept11 add aint;

altertablept11 drop a;

altertablept11 add aintnot null;

altertablept1 attach partitionpt11 forvaluesfrom (2) to (5);

altertablept attach partitionpt1 forvaluesfrom (1,2) to (1,10);

createviewptvasselect*frompt;

insert intoptvvalues (1,2);

select tableoid::regclass,*frompt;

createviewptv_wcoasselect*fromptwhere a=0 withcheck option;

insert intoptv_wcovalues (1,2);

dropviewptv, ptv_wco;

droptablept, pt1, pt11;