Postgres 16

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 extension intarray;

CREATE EXTENSION


create table stats
(
	id serial,
    x	int4,
    y 	int4,
    z 	int4    
);

insert into stats(x,y,z) values 
(6,3,3), 
(5,6,null), 
(4,5,6),
(11,7,8);

CREATE TABLE



INSERT 0 4


create or replace function median() returns setof decimal as $$	
declare	
	r record;	
begin
	for r in	
		select (sort(array_remove(array[x,y,z], null))) array_sorted, 
		array_length(array_remove(array[x,y,z], null), 1) as len 
		from stats order by id
	loop 
		if mod(r.len, 2) != 0 then
			return next r.array_sorted[(r.len+1)/2];
		else 
			return next (r.array_sorted[(r.len+1)/2] + r.array_sorted[(r.len+1)/2+1]) / 2::decimal;
		end if;
	end loop;	
end; 
$$ language plpgsql;

CREATE FUNCTION


select median();


median


3

5.5000000000000000

5

8



SELECT 4