cr_show_t4.sql
Save this script as cr_show_t4.sql.
-- Function to report on some useful overall measures of t4.
create or replace function show_t4()
returns table(t varchar)
language plpgsql
as $body$
declare
count_star constant int not null :=
(select count(*) from t4);
min_dp_score constant numeric not null :=
(select min(dp_score) from t4);
max_dp_score constant numeric not null :=
(select max(dp_score) from t4);
avg_dp_score constant numeric not null :=
(select avg(dp_score) from t4);
dev_dp_score constant numeric not null :=
(select stddev(dp_score) from t4);
min_int_score constant numeric not null :=
(select min(int_score) from t4);
max_int_score constant numeric not null :=
(select max(int_score) from t4);
avg_int_score constant numeric not null :=
(select avg(int_score) from t4);
dev_int_score constant numeric not null :=
(select stddev(int_score) from t4);
begin
assert
(min_int_score = 0) and
(min_int_score::numeric = min_dp_score) and
(max_int_score = 100) and
(max_int_score::numeric = max_dp_score) and
((avg_int_score*100.0)/avg_dp_score between 99.99 and 101.01) and
((dev_int_score*100.0)/dev_dp_score between 99.99 and 101.01) ,
'unexpected';
t := rpad('count(*)', 30)||
to_char(count_star, '999999999'); return next;
t := ''; return next;
t := rpad('avg(%score)', 30)||
to_char(avg_dp_score, '9999999.9'); return next;
t := rpad('stddev(%score)', 30)||
to_char(dev_dp_score, '9999999.9'); return next;
end;
$body$;