declare
v_bid       int4;
v_eid       int4;
v_db        text;
v_hitrate	numeric;
textstr text;
rec RECORD;
begin
-- Confirm snap IDs
PERFORM 1 from edb$snap where edb_id = p_bid;
if not found then
     raise exception 'Begin snapshot identifier % does not exist', p_bid::text;
END IF;
PERFORM 1 from edb$snap where edb_id = p_eid;
if not found then
     raise exception 'End snapshot identifier % does not exist', p_eid::text;
END IF;
select current_database() into v_db;
textstr := '  DATA from pg_stat_database';
return next textstr;
textstr := null;
return next textstr;
textstr := rpad('DATABASE',10,' ')||' '||rpad('NUMBACKENDS',12,' ')||' '||rpad('XACT COMMIT',12,' ')||
' '||rpad('XACT ROLLBACK',15,' ')||' '||rpad('BLKS READ',10,' ')||' '||rpad('BLKS HIT',10,' ')||
' '||rpad('HIT RATIO',10,' ');
return next textstr;
textstr := rpad('-', 85, '-'); -- (85 = 10*4 + 12*2 + 15 + 6 spaces)
return next textstr;
v_bid := p_bid;
v_eid := p_eid;
-----------------------------------------------------------------------------
-- NOTE: blks_fetch = blks_read + blks_hit
-- blks_read are actual reads from the disk if the blk is not found in the 
-- shared buffers
-- hence, hitrate is calculated as blks_hit / blks_fetch
-----------------------------------------------------------------------------
for rec in (
select e.datname,
	(e.numbackends - b.numbackends)  as numbackends,   	 
    (e.xact_commit - b.xact_commit)  as xact_commit,       
	(e.xact_rollback - b.xact_rollback) as xact_rollback,
	(e.blks_read - b.blks_read) as blks_read,
	(e.blks_hit - b.blks_hit) as blks_hit,
    round(100 * ( (e.blks_hit - b.blks_hit)/( (e.blks_read - b.blks_read) + (e.blks_hit - b.blks_hit) )::numeric),2) as hitrate
from edb$stat_database as e
    ,edb$stat_database as b
where b.edb_id            = p_bid
and e.edb_id              = p_eid
and b.dbname              = e.dbname
and b.datname = v_db 
and e.datname = v_db
and ((e.blks_read - b.blks_read) + (e.blks_hit - b.blks_hit)) <> 0)
	 LOOP
textstr := rpad(rec.datname,10,' ')||' '||rpad(rec.numbackends::text,12,' ')||' '||rpad(rec.xact_commit::text,12,' ')||
' '||rpad(rec.xact_rollback::text,15,' ')||' '||rpad(rec.blks_read::text,10,' ')||' '||rpad(rec.blks_hit::text,10,' ')||
' '||rpad(rec.hitrate::text,10,' ');
return next textstr;
end loop;
return;
end;
         