Postgres 13

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 the_table
(
  customer_id int, 
  test_name text, 
  test_version text,
  var_name text,
  raw_value int
);
insert into the_table (customer_id, test_name, test_version, var_name, raw_value)
values
(1256, 'XYP', 'Z12', 'A', 45),
(1256, 'XYP', 'Z12', 'B', 66),
(1256, 'XYP', 'Z12', 'G', 32),
(1266, 'DBZ', 'C123', 'F', 20),
(1266, 'DBZ', 'C123', 'D', 123),
(1245, 'GAG', 'U2G', 'RW', 456),
(1245, 'GAG', 'U2G', 'F', 563),
(1245, 'GAG', 'U2G', 'A', 789),
(1245, 'GAG', 'U2G', 'B', 885);
9 rows affected

create procedure generate_test_report_view()
as
$$
declare
  l_row record;
  l_sql text;
  l_expression text;
begin
  l_sql := 'create view test_overview_per_customer as select customer_id';
  for l_row in select distinct concat_ws('_', test_name, test_version, var_name) test
               from the_table
               order by 1
  loop
    l_expression := format($s$max(raw_value) filter (where concat_ws('_', test_name, test_version, var_name) = %L) as %I$s$, l_row.test, l_row.test);
    l_sql := l_sql || ', ' || l_expression;
  end loop;
  execute 'drop view if exists test_overview_per_customer cascade';  
  l_sql := l_sql ||' from the_table group by customer_id';
  execute l_sql;
end;
$$
language plpgsql;
call generate_test_report_view()
select *
from test_overview_per_customer
order by customer_id;


customer_id
DBZ_C123_D
DBZ_C123_F
GAG_U2G_A
GAG_U2G_B
GAG_U2G_F
GAG_U2G_RW
XYP_Z12_A
XYP_Z12_B
XYP_Z12_G


1245
null
null
789
885
563
456
null
null
null

1256
null
null
null
null
null
null
45
66
32

1266
123
20
null
null
null
null
null
null
null

customer_id	DBZ_C123_D	DBZ_C123_F	GAG_U2G_A	GAG_U2G_B	GAG_U2G_F	GAG_U2G_RW	XYP_Z12_A	XYP_Z12_B	XYP_Z12_G
1245	null	null	789	885	563	456	null	null	null
1256	null	null	null	null	null	null	45	66	32
1266	123	20	null	null	null	null	null	null	null