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_stat_all_tables ordered by seq scan';
return next textstr;
textstr := null;
return next textstr;
textstr := rpad('SCHEMA',20,' ')||' '||rpad('RELATION',30,' ')||' '||rpad('SEQ SCAN',10,' ')||
' '||rpad('REL TUP READ',12,' ')||' '||rpad('IDX SCAN',10,' ')||' '||rpad('IDX TUP READ',12,' ')||
' '||rpad('INS',6,' ')||' '||rpad('UPD',6,' ')||' '||rpad('DEL',6,' ');
return next textstr;
textstr := '-----------------------------------------------------------------------------------------------------------------------';
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.seq_scan - b.seq_scan   as seq_scan,   	 
     e.seq_tup_read - b.seq_tup_read  as seq_tup_read,       
	 e.idx_scan - b.idx_scan as idx_scan,
	 e.idx_tup_fetch - b.idx_tup_fetch as idx_tup_fetch,
	 e.n_tup_ins - b.n_tup_ins as ins,
	 e.n_tup_upd - b.n_tup_upd as upd,
	 e.n_tup_del - b.n_tup_del as del 
from edb$stat_all_tables as e
    ,edb$stat_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 b.idx_scan is not null
and e.idx_scan is not null
and b.idx_tup_fetch is not null
and e.idx_tup_fetch is not null
    order by seq_scan desc, relname   
    limit p_topn;
elsif v_stat = 'USER' then
  open refcur for
select e.schemaname,
	e.relname,
    e.seq_scan - b.seq_scan   as seq_scan,   	 
    e.seq_tup_read - b.seq_tup_read   as seq_tup_read,       
	e.idx_scan - b.idx_scan  as idx_scan,
	e.idx_tup_fetch - b.idx_tup_fetch  as idx_tup_fetch,
	e.n_tup_ins - b.n_tup_ins  as ins,
	e.n_tup_upd - b.n_tup_upd  as upd,
	e.n_tup_del - b.n_tup_del  as del 
from edb$stat_all_tables as e
    ,edb$stat_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 b.idx_scan is not null
and e.idx_scan is not null
and b.idx_tup_fetch is not null
and e.idx_tup_fetch is not null
and e.schemaname in (select distinct schemaname from pg_stat_user_tables)
    order by seq_scan desc, relname   
    limit p_topn;
elsif v_stat = 'SYS' then
  open refcur for
select e.schemaname,
	e.relname,
    e.seq_scan - b.seq_scan  as seq_scan,   	 
    e.seq_tup_read - b.seq_tup_read  as seq_tup_read,       
	e.idx_scan - b.idx_scan as idx_scan,
	e.idx_tup_fetch - b.idx_tup_fetch as idx_tup_fetch,
	e.n_tup_ins - b.n_tup_ins as ins,
	e.n_tup_upd - b.n_tup_upd as upd,
	e.n_tup_del - b.n_tup_del as del 
from edb$stat_all_tables as e
    ,edb$stat_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 b.idx_scan is not null
and e.idx_scan is not null
and b.idx_tup_fetch is not null
and e.idx_tup_fetch is not null
and e.schemaname in (select distinct schemaname from pg_stat_sys_tables)
    order by seq_scan desc, relname   
    limit p_topn;
end if;
loop
fetch refcur into rec;
exit when not found;
textstr := rpad(rec.schemaname,20,' ')||' '||rpad(rec.relname,30,' ')||' '||rpad(rec.seq_scan::text,10,' ')||
' '||rpad(rec.seq_tup_read::text,12,' ')||' '||rpad(rec.idx_scan::text,10,' ')||' '||rpad(rec.idx_tup_fetch::text,12,' ')||
' '||rpad(rec.ins::text,6,' ')||' '||rpad(rec.upd::text,6,' ')||' '||rpad(rec.del::text,6,' ');
return next textstr;
end loop;
close refcur;
textstr := null;
return next textstr;
textstr := '  DATA from pg_stat_all_tables ordered by rel tup read';
return next textstr;
textstr := null;
return next textstr;
textstr := rpad('SCHEMA',20,' ')||' '||rpad('RELATION',30,' ')||' '||rpad('SEQ SCAN',10,' ')||
' '||rpad('REL TUP READ',12,' ')||' '||rpad('IDX SCAN',10,' ')||' '||rpad('IDX TUP READ',12,' ')||
' '||rpad('INS',6,' ')||' '||rpad('UPD',6,' ')||' '||rpad('DEL',6,' ');
return next textstr;
textstr := '-----------------------------------------------------------------------------------------------------------------------';
return next textstr;
if v_stat = 'ALL' then
 open refcur for
select e.schemaname,
	 e.relname,
     e.seq_scan - b.seq_scan   as seq_scan,   	 
     e.seq_tup_read - b.seq_tup_read  as seq_tup_read,       
	 e.idx_scan - b.idx_scan as idx_scan,
	 e.idx_tup_fetch - b.idx_tup_fetch as idx_tup_fetch,
	 e.n_tup_ins - b.n_tup_ins as ins,
	 e.n_tup_upd - b.n_tup_upd as upd,
	 e.n_tup_del - b.n_tup_del as del 
from edb$stat_all_tables as e
    ,edb$stat_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 b.idx_scan is not null
and e.idx_scan is not null
and b.idx_tup_fetch is not null
and e.idx_tup_fetch is not null
    order by seq_tup_read desc, relname   
    limit p_topn;
elsif v_stat = 'USER' then
  open refcur for
select e.schemaname,
	e.relname,
    e.seq_scan - b.seq_scan   as seq_scan,   	 
    e.seq_tup_read - b.seq_tup_read   as seq_tup_read,       
	e.idx_scan - b.idx_scan  as idx_scan,
	e.idx_tup_fetch - b.idx_tup_fetch  as idx_tup_fetch,
	e.n_tup_ins - b.n_tup_ins  as ins,
	e.n_tup_upd - b.n_tup_upd  as upd,
	e.n_tup_del - b.n_tup_del  as del 
from edb$stat_all_tables as e
    ,edb$stat_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 b.idx_scan is not null
and e.idx_scan is not null
and b.idx_tup_fetch is not null
and e.idx_tup_fetch is not null
and e.schemaname in (select distinct schemaname from pg_stat_user_tables)
    order by seq_tup_read desc, relname   
    limit p_topn;
elsif v_stat = 'SYS' then
  open refcur for
select e.schemaname,
	e.relname,
    e.seq_scan - b.seq_scan  as seq_scan,   	 
    e.seq_tup_read - b.seq_tup_read  as seq_tup_read,       
	e.idx_scan - b.idx_scan as idx_scan,
	e.idx_tup_fetch - b.idx_tup_fetch as idx_tup_fetch,
	e.n_tup_ins - b.n_tup_ins as ins,
	e.n_tup_upd - b.n_tup_upd as upd,
	e.n_tup_del - b.n_tup_del as del 
from edb$stat_all_tables as e
    ,edb$stat_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 b.idx_scan is not null
and e.idx_scan is not null
and b.idx_tup_fetch is not null
and e.idx_tup_fetch is not null
and e.schemaname in (select distinct schemaname from pg_stat_sys_tables)
    order by seq_tup_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,30,' ')||' '||rpad(rec.seq_scan::text,10,' ')||
' '||rpad(rec.seq_tup_read::text,12,' ')||' '||rpad(rec.idx_scan::text,10,' ')||' '||rpad(rec.idx_tup_fetch::text,12,' ')||
' '||rpad(rec.ins::text,6,' ')||' '||rpad(rec.upd::text,6,' ')||' '||rpad(rec.del::text,6,' ');
return next textstr;
end loop;
close refcur;
return;
end;
         