-- Setup
drop table range_parted cascade;
CREATE TABLE range_parted (
	a text,
	b bigint,
	c numeric,
	d int,
	e varchar
) partition by range (a, b);
create table part_b_20_b_30 (e varchar, c numeric, a text, b bigint, d int);
alter table range_parted attach partition part_b_20_b_30 for values from ('b', 20) to ('b', 30);
create table part_b_10_b_20 (e varchar, c numeric, a text, b bigint, d int) partition by range (c);
create table part_b_1_b_10 partition of range_parted for values from ('b', 1) to ('b', 10);
alter table range_parted attach partition part_b_10_b_20 for values from ('b', 10) to ('b', 20);
create table part_a_10_a_20 partition of range_parted for values from ('a', 10) to ('a', 20);
create table part_a_1_a_10 partition of range_parted for values from ('a', 1) to ('a', 10);
create table part_c_100_200 (e varchar, c numeric, a text, b bigint, d int) partition by range (abs(d));
alter table part_c_100_200 drop column e, drop column c, drop column a;
alter table part_c_100_200 add column c numeric, add column e varchar, add column a text;
alter table part_c_100_200 drop column b;
alter table part_c_100_200 add column b bigint;
create table part_d_1_15 partition of part_c_100_200 for values from (1) to (15);
create table part_d_15_20 partition of part_c_100_200 for values from (15) to (20);
alter table part_b_10_b_20 attach partition part_c_100_200 for values from (100) to (200);
create table part_c_1_100 (e varchar, d int, c numeric, b bigint, a text);
alter table part_b_10_b_20 attach partition part_c_1_100 for values from (1) to (100);

\set init_range_parted 'truncate range_parted; insert into range_parted values (''a'', 1, 1, 1), (''a'', 10, 200, 1), (''b'', 12, 96, 1), (''b'', 13, 97, 2), (''b'', 15, 105, 16), (''b'', 17, 105, 19)'
\set show_data 'select tableoid::regclass::text COLLATE "C" partname, * from range_parted order by 1, 2, 3, 4, 5, 6'
:init_range_parted;
:show_data;

-- Create RLS policies

ALTER TABLE range_parted ENABLE ROW LEVEL SECURITY;
create user user1;
GRANT ALL ON range_parted TO user1 ;

create policy seeall ON range_parted as PERMISSIVE for SELECT using ( true);
create policy seeall ON part_c_100_200 as PERMISSIVE for SELECT using ( true);

-- Reject updated rows if 'c' is an odd number.
create policy pu on range_parted for UPDATE USING (true) WITH CHECK (c % 2 = 0);

-- Create a trigger on part_d_1_15
create or replace function func() returns trigger as $$
begin
   NEW.c = NEW.c + 1; -- Make even number odd, or vice versa
   return NEW;
end $$ language plpgsql;
create trigger trig_d_1_15 before insert on part_d_1_15
   for each row execute procedure func();


set role user1;

select * from range_parted where a = 'a' and c = 200;
-- This should fail with RLS violation error while moving row from
-- part_a_10_a_20 to part_d_1_15, because trigger makes 'c' value an odd number.
update range_parted set a = 'b', c = 150 where a = 'a' and c = 200;
