/*** uninstall file to drop all objects created by the extension pgsql_tweaks ***/

DO $$
DECLARE
	t_schemaname TEXT;
	i_count INTEGER;
BEGIN

SELECT DISTINCT routine_schema
INTO t_schemaname
FROM information_schema.routines
WHERE specific_catalog = current_database()
	AND routine_name IN
		(
			'gap_fill',
			'replace_latin1'
		)
;

EXECUTE 'DROP FUNCTION IF EXISTS ' || t_schemaname || '.is_empty(TEXT)';
EXECUTE 'DROP FUNCTION IF EXISTS ' || t_schemaname || '.is_empty_b(TEXT)';
EXECUTE 'DROP FUNCTION IF EXISTS ' || t_schemaname || '.array_sum(BIGINT[])';
EXECUTE 'DROP FUNCTION IF EXISTS ' || t_schemaname || '.array_sum(INTEGER[])';
EXECUTE 'DROP FUNCTION IF EXISTS ' || t_schemaname || '.array_sum(SMALLINT[])';
EXECUTE 'DROP FUNCTION IF EXISTS ' || t_schemaname || '.array_sum(REAL[])';
EXECUTE 'DROP FUNCTION IF EXISTS ' || t_schemaname || '.array_sum(DOUBLE PRECISION[])';
EXECUTE 'DROP FUNCTION IF EXISTS ' || t_schemaname || '.array_sum(NUMERIC[])';
EXECUTE 'DROP FUNCTION IF EXISTS ' || t_schemaname || '.array_avg(BIGINT[])';
EXECUTE 'DROP FUNCTION IF EXISTS ' || t_schemaname || '.array_avg(INTEGER[])';
EXECUTE 'DROP FUNCTION IF EXISTS ' || t_schemaname || '.array_avg(SMALLINT[])';
EXECUTE 'DROP FUNCTION IF EXISTS ' || t_schemaname || '.array_avg(REAL[])';
EXECUTE 'DROP FUNCTION IF EXISTS ' || t_schemaname || '.array_avg(DOUBLE PRECISION[])';
EXECUTE 'DROP FUNCTION IF EXISTS ' || t_schemaname || '.array_avg(NUMERIC[])';
EXECUTE 'DROP FUNCTION IF EXISTS ' || t_schemaname || '.array_min(TEXT[])';
EXECUTE 'DROP FUNCTION IF EXISTS ' || t_schemaname || '.array_min(BIGINT[])';
EXECUTE 'DROP FUNCTION IF EXISTS ' || t_schemaname || '.array_min(INTEGER[])';
EXECUTE 'DROP FUNCTION IF EXISTS ' || t_schemaname || '.array_min(SMALLINT[])';
EXECUTE 'DROP FUNCTION IF EXISTS ' || t_schemaname || '.array_min(REAL[])';
EXECUTE 'DROP FUNCTION IF EXISTS ' || t_schemaname || '.array_min(DOUBLE PRECISION[])';
EXECUTE 'DROP FUNCTION IF EXISTS ' || t_schemaname || '.array_min(NUMERIC[])';
EXECUTE 'DROP FUNCTION IF EXISTS ' || t_schemaname || '.array_max(TEXT[])';
EXECUTE 'DROP FUNCTION IF EXISTS ' || t_schemaname || '.array_max(BIGINT[])';
EXECUTE 'DROP FUNCTION IF EXISTS ' || t_schemaname || '.array_max(INTEGER[])';
EXECUTE 'DROP FUNCTION IF EXISTS ' || t_schemaname || '.array_max(NUMERIC[])';
EXECUTE 'DROP FUNCTION IF EXISTS ' || t_schemaname || '.array_max(REAL[])';
EXECUTE 'DROP FUNCTION IF EXISTS ' || t_schemaname || '.array_max(DOUBLE PRECISION[])';
EXECUTE 'DROP FUNCTION IF EXISTS ' || t_schemaname || '.array_max(SMALLINT[])';
EXECUTE 'DROP FUNCTION IF EXISTS ' || t_schemaname || '.array_trim(TEXT[], BOOLEAN)';
EXECUTE 'DROP FUNCTION IF EXISTS ' || t_schemaname || '.array_trim(SMALLINT[], BOOLEAN)';
EXECUTE 'DROP FUNCTION IF EXISTS ' || t_schemaname || '.array_trim(INTEGER[], BOOLEAN)';
EXECUTE 'DROP FUNCTION IF EXISTS ' || t_schemaname || '.array_trim(BIGINT[], BOOLEAN)';
EXECUTE 'DROP FUNCTION IF EXISTS ' || t_schemaname || '.array_trim(NUMERIC[], BOOLEAN)';
EXECUTE 'DROP FUNCTION IF EXISTS ' || t_schemaname || '.array_trim(REAL[], BOOLEAN)';
EXECUTE 'DROP FUNCTION IF EXISTS ' || t_schemaname || '.array_trim(DOUBLE PRECISION[], BOOLEAN)';
EXECUTE 'DROP FUNCTION IF EXISTS ' || t_schemaname || '.array_trim(DATE[], BOOLEAN)';
EXECUTE 'DROP FUNCTION IF EXISTS ' || t_schemaname || '.array_trim(TIMESTAMP[], BOOLEAN)';
EXECUTE 'DROP FUNCTION IF EXISTS ' || t_schemaname || '.array_trim(TIMESTAMP WITH TIME ZONE[], BOOLEAN)';
EXECUTE 'DROP FUNCTION IF EXISTS ' || t_schemaname || '.to_unix_timestamp(TIMESTAMP WITH TIME ZONE)';
EXECUTE 'DROP FUNCTION IF EXISTS ' || t_schemaname || '.to_unix_timestamp(TIMESTAMP)';
EXECUTE 'DROP FUNCTION IF EXISTS ' || t_schemaname || '.datetime_de(TIMESTAMP WITH TIME ZONE,BOOLEAN)';
EXECUTE 'DROP FUNCTION IF EXISTS ' || t_schemaname || '.date_de(DATE)';
EXECUTE 'DROP AGGREGATE IF EXISTS ' || t_schemaname || '.gap_fill(ANYELEMENT)';
EXECUTE 'DROP FUNCTION IF EXISTS ' || t_schemaname || '.gap_fill_internal(ANYELEMENT, ANYELEMENT)';
EXECUTE 'DROP FUNCTION IF EXISTS ' || t_schemaname || '.replace_latin1(TEXT, TEXT[], TEXT[])';
EXECUTE 'DROP FUNCTION IF EXISTS ' || t_schemaname || '.replace_latin1(TEXT, TEXT)';
EXECUTE 'DROP FUNCTION IF EXISTS ' || t_schemaname || '.replace_latin1(TEXT)';
EXECUTE 'DROP FUNCTION IF EXISTS ' || t_schemaname || '.replace_encoding(TEXT, TEXT[], TEXT[])';
EXECUTE 'DROP FUNCTION IF EXISTS ' || t_schemaname || '.replace_encoding(TEXT, TEXT, TEXT)';
EXECUTE 'DROP FUNCTION IF EXISTS ' || t_schemaname || '.replace_encoding(TEXT, TEXT)';
EXECUTE 'DROP FUNCTION IF EXISTS ' || t_schemaname || '.return_not_part_of_encoding(TEXT, TEXT)';
EXECUTE 'DROP FUNCTION IF EXISTS ' || t_schemaname || '.return_not_part_of_latin1(TEXT)';
EXECUTE 'DROP FUNCTION IF EXISTS ' || t_schemaname || '.is_latin1(TEXT)';
EXECUTE 'DROP FUNCTION IF EXISTS ' || t_schemaname || '.is_encoding(TEXT, TEXT, TEXT)';
EXECUTE 'DROP FUNCTION IF EXISTS ' || t_schemaname || '.is_encoding(TEXT, TEXT)';
EXECUTE 'DROP FUNCTION IF EXISTS ' || t_schemaname || '.pg_schema_size(TEXT)';
EXECUTE 'DROP FUNCTION IF EXISTS ' || t_schemaname || '.is_bigint(TEXT)';
EXECUTE 'DROP FUNCTION IF EXISTS ' || t_schemaname || '.is_integer(TEXT)';
EXECUTE 'DROP FUNCTION IF EXISTS ' || t_schemaname || '.is_smallint(TEXT)';
EXECUTE 'DROP FUNCTION IF EXISTS ' || t_schemaname || '.is_numeric(TEXT)';
EXECUTE 'DROP FUNCTION IF EXISTS ' || t_schemaname || '.is_real(TEXT)';
EXECUTE 'DROP FUNCTION IF EXISTS ' || t_schemaname || '.is_double_precision(TEXT)';
EXECUTE 'DROP FUNCTION IF EXISTS ' || t_schemaname || '.is_boolean(TEXT)';
EXECUTE 'DROP FUNCTION IF EXISTS ' || t_schemaname || '.is_json(TEXT)';
EXECUTE 'DROP FUNCTION IF EXISTS ' || t_schemaname || '.is_timestamp(TEXT, TEXT)';
EXECUTE 'DROP FUNCTION IF EXISTS ' || t_schemaname || '.is_timestamp(TEXT)';
EXECUTE 'DROP FUNCTION IF EXISTS ' || t_schemaname || '.is_time(TEXT, TEXT)';
EXECUTE 'DROP FUNCTION IF EXISTS ' || t_schemaname || '.is_time(TEXT)';
EXECUTE 'DROP FUNCTION IF EXISTS ' || t_schemaname || '.is_date(TEXT)';
EXECUTE 'DROP FUNCTION IF EXISTS ' || t_schemaname || '.is_date(TEXT, TEXT)';
EXECUTE 'DROP FUNCTION IF EXISTS ' || t_schemaname || '.is_json(TEXT)';
EXECUTE 'DROP FUNCTION IF EXISTS ' || t_schemaname || '.is_jsonb(TEXT)';
EXECUTE 'DROP FUNCTION IF EXISTS ' || t_schemaname || '.is_hex(TEXT)';
EXECUTE 'DROP FUNCTION IF EXISTS ' || t_schemaname || '.is_uuid(TEXT)';
EXECUTE 'DROP FUNCTION IF EXISTS ' || t_schemaname || '.hex2bigint(TEXT)';
EXECUTE 'DROP FUNCTION IF EXISTS ' || t_schemaname || '.is_bigint_array(TEXT)';
EXECUTE 'DROP FUNCTION IF EXISTS ' || t_schemaname || '.is_integer_array(TEXT)';
EXECUTE 'DROP FUNCTION IF EXISTS ' || t_schemaname || '.is_smallint_array(TEXT)';
EXECUTE 'DROP FUNCTION IF EXISTS ' || t_schemaname || '.is_text_array(TEXT)';
EXECUTE 'DROP FUNCTION IF EXISTS ' || t_schemaname || '.get_markdown_doku_by_schema(TEXT, TEXT)';
EXECUTE 'DROP VIEW IF EXISTS ' || t_schemaname || '.pg_functions';
EXECUTE 'DROP VIEW IF EXISTS ' || t_schemaname || '.pg_foreign_keys';
EXECUTE 'DROP VIEW IF EXISTS ' || t_schemaname || '.pg_db_views';
EXECUTE 'DROP VIEW IF EXISTS ' || t_schemaname || '.pg_table_matview_infos';
EXECUTE 'DROP VIEW IF EXISTS ' || t_schemaname || '.pg_object_ownership';
EXECUTE 'DROP VIEW IF EXISTS ' || t_schemaname || '.pg_bloat_info';
EXECUTE 'DROP VIEW IF EXISTS ' || t_schemaname || '.pg_unused_indexes';
EXECUTE 'DROP VIEW IF EXISTS ' || t_schemaname || '.pg_partitioned_tables_infos';
EXECUTE 'DROP VIEW IF EXISTS ' || t_schemaname || '.pg_table_bloat';
EXECUTE 'DROP VIEW IF EXISTS ' || t_schemaname || '.pg_active_locks';
EXECUTE 'DROP VIEW IF EXISTS ' || t_schemaname || '.pg_missing_indexes';
EXECUTE 'DROP VIEW IF EXISTS ' || t_schemaname || '.pg_role_permissions';
EXECUTE 'DROP VIEW IF EXISTS ' || t_schemaname || '.pg_role_infos';
EXECUTE 'DROP VIEW IF EXISTS ' || t_schemaname || '.statistics_top_ten_query_times';
EXECUTE 'DROP VIEW IF EXISTS ' || t_schemaname || '.statistics_top_ten_query_average_time_in_seconds';
EXECUTE 'DROP VIEW IF EXISTS ' || t_schemaname || '.statistics_top_ten_time_consuming_queries';
EXECUTE 'DROP VIEW IF EXISTS ' || t_schemaname || '.statistics_top_ten_memory_usage_queries';
EXECUTE 'DROP VIEW IF EXISTS ' || t_schemaname || '.statistics_top_ten_called_queries';
EXECUTE 'DROP VIEW IF EXISTS ' || t_schemaname || '.statistics_top_ten_rows_returned_queries';
EXECUTE 'DROP VIEW IF EXISTS ' || t_schemaname || '.statistics_top_ten_shared_block_hits_queries';
EXECUTE 'DROP VIEW IF EXISTS ' || t_schemaname || '.statistics_top_ten_block_writes_queries';
EXECUTE 'DROP VIEW IF EXISTS ' || t_schemaname || '.statistics_top_ten_wal_records_generated_queries';
EXECUTE 'DROP VIEW IF EXISTS ' || t_schemaname || '.statistics_query_activity';
EXECUTE 'DROP VIEW IF EXISTS ' || t_schemaname || '.monitoring_wal';
EXECUTE 'DROP VIEW IF EXISTS ' || t_schemaname || '.monitoring_wal_archiving';
EXECUTE 'DROP VIEW IF EXISTS ' || t_schemaname || '.monitoring_active_locks';
EXECUTE 'DROP VIEW IF EXISTS ' || t_schemaname || '.monitoring_replication';
EXECUTE 'DROP VIEW IF EXISTS ' || t_schemaname || '.monitoring_database_conflicts';
EXECUTE 'DROP VIEW IF EXISTS ' || t_schemaname || '.monitoring_blocked_and_blocking_activity';
EXECUTE 'DROP VIEW IF EXISTS ' || t_schemaname || '.monitoring_follower_wal_status';
EXECUTE 'DROP VIEW IF EXISTS ' || t_schemaname || '.monitoring_vacuum';
EXECUTE 'DROP FUNCTION IF EXISTS ' || t_schemaname || '.role_inheritance(TEXT)';

WITH schema_objects AS
	(
		SELECT c.oid
		, nspname AS schema_name
		FROM pg_namespace AS n
		INNER JOIN pg_authid AS a
			ON n.nspowner = a.oid
		INNER JOIN pg_class AS c
			ON n.oid = c.relnamespace
		WHERE nspname = t_schemaname
		UNION ALL
		SELECT pt.oid
			, nspname AS schema_name
		FROM pg_namespace AS n
		INNER JOIN pg_authid AS a
			ON n.nspowner = a.oid
		INNER JOIN pg_type AS pt
			ON n.oid = pt.typnamespace
		WHERE nspname = t_schemaname
		UNION ALL
		SELECT pc.oid
			, nspname AS schema_name
		FROM pg_namespace AS n
		INNER JOIN pg_authid AS a
			ON n.nspowner = a.oid
		INNER JOIN pg_proc AS pc
			ON n.oid = pc.pronamespace
		WHERE nspname = t_schemaname
	)
SELECT count(*)
INTO i_count
FROM schema_objects
;

IF i_count = 0 THEN
	EXECUTE 'DROP SCHEMA ' || t_schemaname;
END IF;

END;
$$;