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), Priority int);

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

Create Or Replace Function pvt(qr_selector int, Scenario_selector varchar default 'all')
    RETURNS void
    LANGUAGE plpgsql AS
$body$
declare
    sqlColumn varchar;
    qr varchar; qr2 varchar;
    columnlist varchar;
Begin
  sqlColumn= 'select distinct D.sys_cat from (select Row_Number() Over (Partition By Scenario,Date Order By Priority) as sys_cat From run) D order by D.sys_cat;';
  qr='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 Priority) as sys_cat From run) D ';
   
  if Scenario_selector='all' then
    qr=qr || ' Group By D.Scenario,D.Date,overall Order By D.Scenario,D.Date';
  else
    qr=qr || 'Where D.Scenario =''' || Scenario_selector || ''' Group By D.Scenario,D.Date,overall Order By D.Scenario,D.Date';
  end if;
   
  qr2='with cte as (' || qr || ') Select date, count(*) filter (where overall=''PASS'') as Num_Of_Passes,' ||
  'count(*) filter (where overall=''FAIL'') as Num_Of_Fails ' ||
  'from cte group by date;';
   
  Deallocate All;

  if qr_selector = 1 then
   EXECUTE 'prepare pvtstmt AS ' || qr;
  else
   EXECUTE 'prepare pvtstmt AS ' || qr2;
  end if;
End;
$body$
Select pvt(1);
Execute pvtstmt;
Select pvt(2);
Execute pvtstmt;

Select pvt(1,'Proj1');
Execute pvtstmt;
Select pvt(2,'Proj1');
Execute pvtstmt;


pvt







scenario
date
system1
system2
system3
system4
overall


Proj1
07-01
C_PASS
A_PASS
B_PASS
D_PASS
PASS

Proj1
07-02
C_FAIL
A_FAIL
B_FAIL
D_FAIL
FAIL

Proj2
07-01
E_PASS
F_FAIL


FAIL

Proj2
07-02
E_PASS
F_PASS


PASS




pvt







date
num_of_passes
num_of_fails


07-01
1
1

07-02
1
1




pvt







scenario
date
system1
system2
system3
system4
overall


Proj1
07-01
C_PASS
A_PASS
B_PASS
D_PASS
PASS

Proj1
07-02
C_FAIL
A_FAIL
B_FAIL
D_FAIL
FAIL




pvt







date
num_of_passes
num_of_fails


07-01
1
0

07-02
0
1

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