 declare
-- v_topn controls how many rows to report by section
-- v_stattype determines where user,sys or all data is used
v_topn		int := 10;
v_stattype	text := 'all';
v_start 	bigint;
v_end   	bigint;
v_db		text;
v_dbsize	text;
v_tspsize	text;
v_tabsize	numeric;
v_version	text;
v_textstr   	text;
v_currdt	text;
beginsnap_tm	edb$snap.snap_tm%TYPE;
endsnap_tm	edb$snap.snap_tm%TYPE;
spcrec		record;
nsprec		record;
tabrec		record;
parmrec		record;
toptabrec	record;
topidxrec	record;
rec		record;
dmlrec		record;
bufrec		record;
v_nspcnt	numeric := 0;
have_pg_buffercache_ext bool;
begin
v_start := beginsnap;
v_end   := endsnap;
-- get date/time
-- get period from snapids
select current_database() into v_db;
select version() into v_version;
select current_date into v_currdt;
select snap_tm into beginsnap_tm from edb$snap where edb_id = v_start;
select snap_tm into endsnap_tm from edb$snap where edb_id = v_end;
-- RM32092, raise exception if session identifiers are not found
if beginsnap_tm IS NULL then
	raise exception 'Begin snapshot identifier % does not exist', v_start::text;
end if;
if endsnap_tm IS NULL then
	raise exception 'End snapshot identifier % does not exist', v_end::text;
end if;
v_textstr := '   EnterpriseDB Report for database '||v_db||'        '||v_currdt;
return next v_textstr;
v_textstr := 'Version: '||v_version;
return next v_textstr;
v_textstr := null;
return next v_textstr;
v_textstr := '     Begin snapshot: '||v_start::text||' at '||beginsnap_tm;
return next v_textstr;
v_textstr := '     End snapshot:   '||v_end::text||' at '||endsnap_tm;
return next v_textstr;
-- Get database size
v_textstr := null;
return next v_textstr;
select pg_size_pretty(pg_database_size(v_db)) into v_dbsize;
v_textstr := 'Size of database '||v_db||' is '||v_dbsize;
return next v_textstr;
-- Get tablespace info
for spcrec in select spcname,usename from pg_catalog.pg_tablespace as tsp, pg_user as usr
	where tsp.spcowner = usr.usesysid LOOP
select pg_size_pretty(pg_tablespace_size(spcrec.spcname)) into v_tspsize;
v_textstr := '     Tablespace: '||spcrec.spcname||' Size: '||v_tspsize||' Owner: '||spcrec.usename;
return next v_textstr;
end loop;
v_textstr := null;
return next v_textstr;
-- Get schema info
if v_version like '%EnterpriseDB Advanced Server%' then
	for nsprec in select nspname,usename from pg_catalog.pg_namespace as nsp, pg_user as usr
	where usr.usesysid = nsp.nspowner
	and nsp.nspname not in ('sys','information_schema','pg_toast','pg_catalog')
	and nsp.nspname not like E'pg\\_temp\\_%'
	and nspheadsrc is null LOOP
	v_nspcnt := 0;
	for tabrec in select schemaname,tablename from pg_tables where schemaname = nsprec.nspname LOOP
	select pg_total_relation_size(quote_ident(tabrec.schemaname)||'.'||quote_ident(tabrec.tablename)) into v_tabsize;
	v_nspcnt := v_nspcnt + v_tabsize;
	end loop;
	v_textstr := 'Schema: '||rpad(nsprec.nspname,30,' ')||' Size: '||rpad(pg_size_pretty(v_nspcnt::bigint),15,' ')||
	' Owner: '||rpad(nsprec.usename,20,' ');
	return next v_textstr;
	end loop;
else
	for nsprec in select nspname,usename from pg_catalog.pg_namespace as nsp, pg_user as usr
	where usr.usesysid = nsp.nspowner
	and nsp.nspname not in ('sys','information_schema','pg_toast','pg_catalog')
	and nsp.nspname not like E'pg\\_temp\\_%'
	 LOOP
	v_nspcnt := 0;
	for tabrec in select schemaname,tablename from pg_tables where schemaname = nsprec.nspname LOOP
	select pg_total_relation_size(quote_ident(tabrec.schemaname)||'.'||quote_ident(tabrec.tablename)) into v_tabsize;
	v_nspcnt := v_nspcnt + v_tabsize;
	end loop;
	v_textstr := 'Schema: '||rpad(nsprec.nspname,30,' ')||' Size: '||rpad(pg_size_pretty(v_nspcnt::bigint),15,' ')||
	' Owner: '||rpad(nsprec.usename,20,' ');
	return next v_textstr;
	end loop;
end if;
v_textstr := null;
return next v_textstr;
-- Get Top 10 largest tables
v_textstr := '               Top 10 Relations by pages';
return next v_textstr;
v_textstr := null;
return next v_textstr;
v_textstr := rpad('TABLE',45,' ')||rpad('RELPAGES',10,' ');
return next v_textstr;
v_textstr := '----------------------------------------------------------------------------------';
return next v_textstr;
for toptabrec in SELECT relname, relpages FROM pg_class 
where relkind = 'r' 
ORDER BY relpages DESC
limit 10 loop
v_textstr := rpad(toptabrec.relname,45,' ')||' '||rpad(toptabrec.relpages::text,10,' ');
return next v_textstr;
end loop;
v_textstr := null;
return next v_textstr;
-- Get Top 10 largest indexes
v_textstr := '               Top 10 Indexes by pages';
return next v_textstr;
v_textstr := null;
return next v_textstr;
v_textstr := rpad('INDEX',45,' ')||rpad('RELPAGES',10,' ');
return next v_textstr;
v_textstr := '----------------------------------------------------------------------------------';
return next v_textstr;
for topidxrec in SELECT relname, relpages FROM pg_class 
where relkind = 'i' 
ORDER BY relpages DESC
limit 10 loop
v_textstr := rpad(topidxrec.relname,45,' ')||' '||rpad(topidxrec.relpages::text,10,' ');
return next v_textstr;
end loop;
v_textstr := null;
return next v_textstr;
-- Top 10 relations by update,delete,insert activity
v_textstr := '               Top 10 Relations by DML';
return next v_textstr;
v_textstr := null;
return next v_textstr;
v_textstr := rpad('SCHEMA',15,' ')||' '||rpad('RELATION',45,' ')||rpad('UPDATES',10,' ')||
rpad('DELETES',10,' ')||rpad('INSERTS',10,' ');
return next v_textstr;
v_textstr := '--------------------------------------------------------------------------------------------------';
return next v_textstr;
for dmlrec in
select schemaname as SCHEMA,
		relname as RELATION,
		n_tup_upd as UPDATES,
		n_tup_del as DELETES,
		n_tup_ins as INSERTS
from pg_stat_user_tables
order by n_tup_upd desc,n_tup_del desc, n_tup_ins desc,schemaname,relname
limit 10 LOOP
v_textstr := rpad(dmlrec.schema,15,' ')||' '||rpad(dmlrec.relation,45,' ')||rpad(dmlrec.updates::text,10,' ')||
rpad(dmlrec.deletes::text,10,' ')||rpad(dmlrec.inserts::text,10,' ');
return next v_textstr;
end loop;
v_textstr := null;
return next v_textstr;
-- Call stat functions
for rec in (select * from  stat_db_rpt(v_start::int4,v_end::int4) z )
	loop
		v_textstr := rec.z;
  		return next v_textstr;
	end loop;
v_textstr := null;
return next v_textstr;
-- If pg_buffercache extension is installed, fetch buffer usage information
select count(*) > 0 from pg_extension where extname = 'pg_buffercache' into have_pg_buffercache_ext;
if have_pg_buffercache_ext then
	-- Top 10 Relations in buffer cache
	v_textstr := '  DATA from pg_buffercache';
	return next v_textstr;
	v_textstr := null;
	return next v_textstr;
	v_textstr := rpad('RELATION',35,' ')||' '||rpad('BUFFERS',10,' ');
	return next v_textstr;
	v_textstr := '-----------------------------------------------------------------------------';
	return next v_textstr;
	for bufrec in
		SELECT c.relname, count(*) AS buffers
		FROM pg_class c
		INNER JOIN pg_buffercache b ON b.relfilenode = c.relfilenode
		INNER JOIN pg_database d ON (b.reldatabase = d.oid AND d.datname = current_database())
		GROUP BY c.relname
		ORDER BY 2 DESC LIMIT 10
	loop
		v_textstr := rpad(bufrec.relname,35,' ')||' '||rpad(bufrec.buffers::text,10,' ');
		return next v_textstr;
	end loop;
	v_textstr := null;
	return next v_textstr;
else
	v_textstr := '  DATA from pg_buffercache not included because pg_buffercache is not installed';
	return next v_textstr;
	v_textstr := null;
	return next v_textstr;
end if;
for rec in (select * from  stat_tables_rpt(v_start::int4,v_end::int4,v_topn,v_stattype) z )
	loop
  		v_textstr := rec.z;
  		return next v_textstr;
	end loop;
v_textstr := null;
return next v_textstr;
for rec in (select * from  statio_tables_rpt(v_start::int4,v_end::int4,v_topn,v_stattype) z )
	loop
  		v_textstr := rec.z;
  		return next v_textstr;
	end loop;
v_textstr := null;
return next v_textstr;
for rec in (select * from  stat_indexes_rpt(v_start::int4,v_end::int4,v_topn,v_stattype) z )
	loop
  		v_textstr := rec.z;
  		return next v_textstr;
	end loop;
v_textstr := null;
return next v_textstr;
for rec in (select * from  statio_indexes_rpt(v_start::int4,v_end::int4,v_topn,v_stattype) z )
	loop
  		v_textstr := rec.z;
  		return next v_textstr;
	end loop;
v_textstr := null;
return next v_textstr;
-- Incluce DRITA system data, if Enterprisedb
if v_version like '%EnterpriseDB Advanced Server%' then
    v_textstr := '   System Wait Information';
    return next v_textstr;
	v_textstr := null;
	return next v_textstr;
	for rec in (select * from  sys_rpt(v_start::int4,v_end::int4,v_topn) z )
	loop
  		v_textstr := rec.z;
  		return next v_textstr;
	end loop;
	v_textstr := null;
	return next v_textstr;
end if;
-- Get database parameters
v_textstr := '   Database Parameters from postgresql.conf ';
return next v_textstr;
v_textstr := null;
return next v_textstr;
v_textstr := 'PARAMETER                         SETTING                                  CONTEXT     MINVAL       MAXVAL       ';
return next v_textstr;
v_textstr := '-------------------------------------------------------------------------------------------------------------------------';
return next v_textstr;
for parmrec in select name as parm,
	-- coalesce(unit,'') as unit,
    setting as setting,
-- category,
	coalesce(context,'') as context,
--	coalesce(vartype,'') as vartype,	
	coalesce(min_val,'') as minval,
	coalesce(max_val,'') as maxval
 from pg_settings 
 order by name LOOP
v_textstr := rpad(parmrec.parm,35,' ') -- ||rpad(parmrec.unit,5,' ')
||rpad(parmrec.setting,40,' ')||' '||rpad(parmrec.context,12,' ')||
rpad(parmrec.minval::text,13,' ')||rpad(parmrec.maxval::text,12,' ');
return next v_textstr;
end loop;
end;
               