/**
 * Top ten time consuming queries
 * NOTE: The schema where the extension pg_stat_statements is installed
 *       has to be in the search_path of the user!
 *
 * @author: "Stefanie Janine Stölting"<stefanie@proopensource.eu>
 * @license: PostgreSQL https://opensource.org/licenses/postgresql
 */

DO $$
DECLARE
	pg_extension_installed BOOLEAN;
	view_source TEXT;
BEGIN

	SELECT count(*) AS pg_stat_statements_exists
	FROM pg_extension
	WHERE extname = 'pg_stat_statements'
	INTO pg_extension_installed
	;

	IF pg_extension_installed THEN
		-- The view is only created when pgstattuple is installed
		view_source :=
$string$
CREATE OR REPLACE VIEW statistics_top_ten_time_consuming_queries AS
SELECT s.userid::regrole AS rolename
	, d.datname AS database_name
	, query
	, calls
	, total_exec_time/1000 as total_time_seconds
	, min_exec_time/1000 as min_time_seconds
	, max_exec_time/1000 as max_time_seconds
	, mean_exec_time/1000 as mean_time_seconds
FROM pg_stat_statements AS s
INNER JOIN pg_catalog.pg_database AS d
	ON s.dbid = d.oid
ORDER BY mean_exec_time DESC
LIMIT 10
;
$string$
;
		EXECUTE view_source;

		COMMENT ON VIEW statistics_top_ten_time_consuming_queries IS 'Top ten time consuming queries.';
		COMMENT ON COLUMN statistics_top_ten_time_consuming_queries.rolename IS 'The role/user who executed the query.';
		COMMENT ON COLUMN statistics_top_ten_time_consuming_queries.database_name IS 'The database name in which the query has been executed.';
		COMMENT ON COLUMN statistics_top_ten_time_consuming_queries.query IS 'This is the SQL source of the query.';
		COMMENT ON COLUMN statistics_top_ten_time_consuming_queries.calls IS 'The number of times the statement was executed.';
		COMMENT ON COLUMN statistics_top_ten_time_consuming_queries.total_time_seconds IS 'The total execution time in seconds.';
		COMMENT ON COLUMN statistics_top_ten_time_consuming_queries.min_time_seconds IS 'The minimum execution time in seconds.';
		COMMENT ON COLUMN statistics_top_ten_time_consuming_queries.max_time_seconds IS 'The maximum execution time in seconds.';
		COMMENT ON COLUMN statistics_top_ten_time_consuming_queries.mean_time_seconds IS 'The average execution time of the query in seconds';

	END IF;

END $$;