/**
 * Query statistics about how often the query has been called and the roles and
  * applications
 * 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;
 	version_greater_13 BOOLEAN;
BEGIN

	SELECT count(*) > 0 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

	 	SELECT to_number((string_to_array(version(), ' '))[2], '999.99') >= 14
		INTO version_greater_13;
	
	 	IF version_greater_13 THEN
	 		-- Create the view for PostgreSQL 14 or newer

			CREATE OR REPLACE VIEW statistics_query_activity AS
			SELECT psa.datname AS database_name
				, psa.usename AS  rolename
				, psa.backend_type
				, psa.application_name
				, psa.query
				, pss.calls AS calls_by_all_rolls
			FROM pg_stat_activity AS psa
			LEFT OUTER JOIN pg_stat_statements AS pss
				ON psa.query_id = pss.queryid
			;

			COMMENT ON VIEW statistics_query_activity IS 'Query statistics about how often the query has been called and the roles and applications.';
			COMMENT ON COLUMN statistics_query_activity.database_name IS 'The name of the database.';
			COMMENT ON COLUMN statistics_query_activity.rolename IS 'The role/user who executed the query.';
			COMMENT ON COLUMN statistics_query_activity.backend_type IS 'The type of current backend. Possible types are autovacuum launcher, autovacuum worker, logical replication launcher, logical replication worker, parallel worker, background writer, client backend, checkpointer, archiver, standalone backend, startup, walreceiver, walsender, walwriter and walsummarizer. In addition, background workers registered by extensions may have additional types.';
			COMMENT ON COLUMN statistics_query_activity.application_name IS 'The name of the application that owns the backend, maybe empty when not set by the application.';
			COMMENT ON COLUMN statistics_query_activity.query IS 'This is the SQL source of the query.';
			COMMENT ON COLUMN statistics_query_activity.calls_by_all_rolls IS 'The overall count of executions of this query, the same query can be executed by different roles.';
		END IF;
	END IF;

END $$;