/**
 * Top ten queries with high memory usage
 * 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_called_queries AS
SELECT s.userid::regrole AS rolename
	, d.datname AS database_name
	, s.calls
	, s.query
FROM pg_stat_statements AS s
INNER JOIN pg_catalog.pg_database AS d
	ON s.dbid = d.oid
ORDER BY s.calls DESC
LIMIT 10
;
$string$
;
		EXECUTE view_source;

		COMMENT ON VIEW statistics_top_ten_called_queries IS 'Top ten queries by calls.';
		COMMENT ON COLUMN statistics_top_ten_called_queries.rolename IS 'The role/user who executed the query.';
		COMMENT ON COLUMN statistics_top_ten_called_queries.database_name IS 'The database name in which the query has been executed.';
		COMMENT ON COLUMN statistics_top_ten_called_queries.calls IS 'This is many times a statement has been executed.';
		COMMENT ON COLUMN statistics_top_ten_called_queries.query IS 'This is the SQL source of the query.';

	END IF;

END $$;