Postgres 14

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 run(Scenario VARCHAR(50), Date VARCHAR(50), System VARCHAR(50), Result VARCHAR(50));

Insert Into run Values('Proj1','07-01','A','PASS'),
('Proj1','07-01','B','PASS'),
('Proj1','07-01','C','PASS'),
('Proj1','07-01','D','PASS'),
('Proj1','07-02','A','FAIL'),
('Proj1','07-02','B','FAIL'),
('Proj1','07-02','C','FAIL'),
('Proj1','07-02','D','FAIL'),
('Proj2','07-01','E','PASS'),
('Proj2','07-01','F','FAIL'),
('Proj2','07-02','E','PASS'),
('Proj2','07-02','F','PASS');
12 rows affected

Create Or Replace Function pvt()
    RETURNS void
    LANGUAGE 'plpgsql'
As $body$
declare
    sqlColumn varchar;
    qr varchar;
    columnlist varchar;
Begin
sqlColumn= 'select distinct D.sys_cat from (select Row_Number() Over (Partition By Scenario,Date Order By System) as sys_cat From run) D order by D.sys_cat;';
qr='prepare pvtstmt AS Select D.Scenario,D.Date,';

For columnlist In EXECUTE sqlColumn  
   Loop
      qr=qr || 'Max(Case When sys_cat=' || columnlist || ' Then (System || ''_'' || Result) Else '''' End) As System' ||  columnlist ||  ' , ';
   END LOOP;
   
   --qr=substr(qr, 0, length(qr) - 1);
   qr=qr|| 'case when D.tc= D.pc then ''PASS'' else ''FAIL'' end as overall ';
   qr=qr || 'From (select *, Count(*) over (Partition By Scenario,Date) tc,'||
   ' count(*) filter (where Result=''PASS'') over (Partition By Scenario,Date) pc,'||
   '  Row_Number() Over (Partition By Scenario,Date Order By System) as sys_cat From run) D Where D.Scenario =' 
   || '''Proj1'' Group By D.Scenario,D.Date,overall Order By D.Scenario,D.Date';
   
Deallocate All;
EXECUTE qr;
End;
$body$
Select pvt();
Execute pvtstmt;


pvt







scenario
date
system1
system2
system3
system4
overall


Proj1
07-01
A_PASS
B_PASS
C_PASS
D_PASS
PASS

Proj1
07-02
A_FAIL
B_FAIL
C_FAIL
D_FAIL
FAIL

scenario	date	system1	system2	system3	system4	overall
Proj1	07-01	A_PASS	B_PASS	C_PASS	D_PASS	PASS
Proj1	07-02	A_FAIL	B_FAIL	C_FAIL	D_FAIL	FAIL