Postgres 11

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

Help with an interesting Postgres question: Why isn't an Index Only Scan used on a partition accessed via the parent table?.

create table test1 (id int, val int);
create table test2 (id int, val int);
create view total 
as
select t1.id, t1.val val1, t2.val val2
from test1 t1
join test2 t2 on t1.id = t2.id;
create function fninsert()
returns trigger as $fninsert$
begin
insert into test1 values (new.id, new.val1);
insert into test2 values (new.id, new.val2);
return new;
end;
$fninsert$ LANGUAGE plpgsql;
create trigger trinsert
instead of insert on total
for each row
execute procedure fninsert();
create rule r_insert as 
on insert to total
do also
select new.val1, new.val2;
-- insert data into view via trigger
-- returning some fields via rule
insert into total values (1,11,111);


val1
val2


11
111


select * from total;


id
val1
val2


1
11
111


select 1 num, * from test1
union all
select 2 num, * from test2;


num
id
val


1
1
11

2
1
111