declare
v_bid       int4;
v_eid       int4;
v_topn      int4;
v_stat      text;
textstr text;
rec RECORD;
refcur  refcursor;
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;
textstr := '  DATA from pg_statio_all_tables';
return next textstr;
textstr := null;
return next textstr;
textstr := rpad('SCHEMA',20,' ')||' '||rpad('RELATION',20,' ')||
' '||rpad('HEAP',8,' ') ||' '||rpad('HEAP',8,' ') ||
' '||rpad('IDX',8,' ')  ||' '||rpad('IDX',8,' ')  ||
' '||rpad('TOAST',8,' ')||' '||rpad('TOAST',8,' ')||
' '||rpad('TIDX',8,' ') ||' '||rpad('TIDX',8,' ');
return next textstr;
textstr := rpad('      ',20,' ')||' '||rpad('        ',20,' ')||
' '||rpad('READ',8,' ')||' '||rpad('HIT',8,' ')||
' '||rpad('READ',8,' ')||' '||rpad('HIT',8,' ')||
' '||rpad('READ',8,' ')||' '||rpad('HIT',8,' ')||
' '||rpad('READ',8,' ')||' '||rpad('HIT',8,' ');
return next textstr;
textstr := rpad('-', 113, '-'); -- (113 = 20*2 + 8*8 + 9 spaces)
return next textstr;
v_bid := p_bid;
v_eid := p_eid;
v_topn := p_topn;
v_stat := upper(p_stat);
if v_stat not in ('ALL','USER','SYS') then
	raise exception 'Invalid stat type.';
end if;
if v_stat = 'ALL' then
 open refcur for
 select e.schemaname,
	e.relname,
    e.heap_blks_read - b.heap_blks_read  as heap_blks_read,   	 
    e.heap_blks_hit - b.heap_blks_hit  as heap_blks_hit,       
	coalesce(e.idx_blks_read,0) - coalesce(b.idx_blks_read,0) as idx_blks_read,
	coalesce(e.idx_blks_hit,0) - coalesce(b.idx_blks_hit,0) as idx_blks_hit,
	coalesce(e.toast_blks_read,0) - coalesce(b.toast_blks_read,0) as toast_blks_read,
	coalesce(e.toast_blks_hit,0) - coalesce(b.toast_blks_hit,0) as toast_blks_hit,
	coalesce(e.tidx_blks_read,0) - coalesce(b.tidx_blks_read,0) as tidx_blks_read,
	coalesce(e.tidx_blks_hit,0) - coalesce(b.tidx_blks_hit,0) as tidx_blks_hit
from edb$statio_all_tables as e
    ,edb$statio_all_tables as b
where b.edb_id            = p_bid
and e.edb_id              = p_eid
and b.dbname              = e.dbname
and b.schemaname          = e.schemaname
and b.relname		  = e.relname
    order by heap_blks_hit desc, relname   
    limit p_topn;
elsif v_stat = 'USER' then
  open refcur for
select e.schemaname,
	e.relname,
    e.heap_blks_read - b.heap_blks_read  as heap_blks_read,   	 
    e.heap_blks_hit - b.heap_blks_hit  as heap_blks_hit,       
	coalesce(e.idx_blks_read,0) - coalesce(b.idx_blks_read,0) as idx_blks_read,
	coalesce(e.idx_blks_hit,0) - coalesce(b.idx_blks_hit,0) as idx_blks_hit,
	coalesce(e.toast_blks_read,0) - coalesce(b.toast_blks_read,0) as toast_blks_read,
	coalesce(e.toast_blks_hit,0) - coalesce(b.toast_blks_hit,0) as toast_blks_hit,
	coalesce(e.tidx_blks_read,0) - coalesce(b.tidx_blks_read,0) as tidx_blks_read,
	coalesce(e.tidx_blks_hit,0) - coalesce(b.tidx_blks_hit,0) as tidx_blks_hit
from edb$statio_all_tables as e
    ,edb$statio_all_tables as b
where b.edb_id            = p_bid
and e.edb_id              = p_eid
and b.dbname              = e.dbname
and b.schemaname          = e.schemaname
and b.relname		  = e.relname
and e.schemaname in (select distinct schemaname from pg_statio_user_tables)
    order by heap_blks_read desc, relname   
    limit p_topn;
elsif v_stat = 'SYS' then
  open refcur for
select e.schemaname,
	e.relname,
    e.heap_blks_read - b.heap_blks_read  as heap_blks_read,   	 
    e.heap_blks_hit - b.heap_blks_hit  as heap_blks_hit,       
	coalesce(e.idx_blks_read,0) - coalesce(b.idx_blks_read,0) as idx_blks_read,
	coalesce(e.idx_blks_hit,0) - coalesce(b.idx_blks_hit,0) as idx_blks_hit,
	coalesce(e.toast_blks_read,0) - coalesce(b.toast_blks_read,0) as toast_blks_read,
	coalesce(e.toast_blks_hit,0) - coalesce(b.toast_blks_hit,0) as toast_blks_hit,
	coalesce(e.tidx_blks_read,0) - coalesce(b.tidx_blks_read,0) as tidx_blks_read,
	coalesce(e.tidx_blks_hit,0) - coalesce(b.tidx_blks_hit,0) as tidx_blks_hit
from edb$statio_all_tables as e
    ,edb$statio_all_tables as b
where b.edb_id            = p_bid
and e.edb_id              = p_eid
and b.dbname              = e.dbname
and b.schemaname          = e.schemaname
and b.relname		  = e.relname
and e.schemaname in (select distinct schemaname from pg_statio_sys_tables)
    order by heap_blks_read desc, relname   
    limit p_topn;
end if;
loop
fetch refcur into rec;
exit when not found;
textstr := rpad(rec.schemaname,20,' ')||' '||rpad(rec.relname,20,' ')||
' '||rpad(rec.heap_blks_read::text,8,' ') ||' '||rpad(rec.heap_blks_hit::text,8,' ') ||
' '||rpad(rec.idx_blks_read::text,8,' ')  ||' '||rpad(rec.idx_blks_hit::text,8,' ')  ||
' '||rpad(rec.toast_blks_read::text,8,' ')||' '||rpad(rec.toast_blks_hit::text,8,' ')||
' '||rpad(rec.tidx_blks_read::text,8,' ') ||' '||rpad(rec.tidx_blks_hit::text,8,' ');
return next textstr;
end loop;
close refcur;
return;
end;
          