/**
 * PostgreSQL pgsql_tweaks extension
 * Licence:    PostgreSQL Licence, see https://raw.githubusercontent.com/sjstoelting/pgsql-tweaks/master/LICENSE.md
 * Author:     Stefanie Janine Stölting <mail@stefanie-stoelting.de>
 * Repository: http://github.com/sjstoelting/pgsql_tweaks/
 * Version:    1.0.0
 */

/*** Initial statements ***/
SET client_min_messages TO warning;
SET log_min_messages    TO warning;

/*** Create a schema for the extension ***/
CREATE SCHEMA IF NOT EXISTS pgsql_tweaks;

/*** Grant rights to role public for all objects ***/
GRANT USAGE ON SCHEMA pgsql_tweaks TO public;

ALTER DEFAULT PRIVILEGES IN SCHEMA pgsql_tweaks GRANT SELECT ON TABLES TO public;

ALTER DEFAULT PRIVILEGES IN SCHEMA pgsql_tweaks GRANT EXECUTE ON FUNCTIONS TO public;

/*** Set search path to have all objects in the schema pgsql_tweaks ***/
SET search_path TO pgsql_tweaks, public;


/*** files with creation statements ***/

/**
 * Creates two functions to check strings for being a date.
 * The first function checks it with the default format, the second with the
 * format given as parameter.
 *
 * @author: "Stefanie Janine Stölting"<stefanie@proopensource.eu>
 * @license: PostgreSQL https://opensource.org/licenses/postgresql
 */

CREATE OR REPLACE FUNCTION is_date(s text) RETURNS BOOLEAN AS $$
BEGIN
	PERFORM s::date;
	RETURN TRUE;
EXCEPTION WHEN OTHERS THEN
	RETURN FALSE;
END;
$$
STRICT
LANGUAGE plpgsql IMMUTABLE
;
COMMENT ON FUNCTION is_date(s text) IS 'Takes a text and checks if it is a date, uses standard date format YYYY-MM-DD';


CREATE OR REPLACE FUNCTION is_date(s text, f text) RETURNS BOOLEAN AS $$
BEGIN
	PERFORM to_date(s, f);
	RETURN TRUE;
EXCEPTION WHEN OTHERS THEN
	RETURN FALSE;
END;
$$
STRICT
LANGUAGE plpgsql IMMUTABLE
;
COMMENT ON FUNCTION is_date(s text, f text) IS 'Takes a text and checks if it is a date by taking the second text as date format';

/**
 * Creates two functions to check strings for being a time.
 * The first function checks it with the default format, the second with the
 * format given as parameter.
 *
 * @author: "Stefanie Janine Stölting"<stefanie@proopensource.eu>
 * @license: PostgreSQL https://opensource.org/licenses/postgresql
 */

CREATE OR REPLACE FUNCTION is_time(s text) RETURNS BOOLEAN AS $$
BEGIN
	PERFORM s::TIME;
	RETURN TRUE;
EXCEPTION WHEN OTHERS THEN
	RETURN FALSE;
END;
$$
STRICT
LANGUAGE plpgsql IMMUTABLE
;
COMMENT ON FUNCTION is_time(s text) IS 'Takes a text and checks if it is a time, uses standard date format HH24:MI:SS.US';


CREATE OR REPLACE FUNCTION is_time(s text, f text) RETURNS BOOLEAN AS $$
BEGIN
	PERFORM to_timestamp(s, f)::TIME;
	RETURN TRUE;
EXCEPTION WHEN OTHERS THEN
	RETURN FALSE;
END;
$$
STRICT
LANGUAGE plpgsql IMMUTABLE
;
COMMENT ON FUNCTION is_time(s text, f text) IS 'Takes a text and checks if it is a time by taking the second text as time format';

/**
 * Creates two functions to check strings for being timestamps.
 * The first function checks it with the default format, the second with the
 * format given as parameter.
 *
 * @author: "Stefanie Janine Stölting"<stefanie@proopensource.eu>
 * @license: PostgreSQL https://opensource.org/licenses/postgresql
 */

CREATE OR REPLACE FUNCTION is_timestamp(s text) RETURNS BOOLEAN AS $$
BEGIN
	PERFORM s::TIMESTAMP;
	RETURN TRUE;
EXCEPTION WHEN others THEN
	RETURN FALSE;
END;
$$
STRICT
LANGUAGE plpgsql IMMUTABLE;
COMMENT ON FUNCTION is_timestamp(s text) IS 'Takes a text and checks if it is a timestamp, uses standard timestamp format YYYY-MM-DD HH24:MI:SS';


CREATE OR REPLACE FUNCTION is_timestamp(s text, f text) RETURNS BOOLEAN AS $$
BEGIN
	PERFORM to_timestamp(s, f)::TIMESTAMP;
	RETURN TRUE;
EXCEPTION WHEN others THEN
	RETURN FALSE;
END;
$$
STRICT
LANGUAGE plpgsql IMMUTABLE;
COMMENT ON FUNCTION is_timestamp(s text) IS 'Takes a text and checks if it is a timestamp by taking the second text as date format';

/**
 * Creates a function to check strings for being nunbers.
 *
 * @author: "Stefanie Janine Stölting"<stefanie@proopensource.eu>
 * @license: PostgreSQL https://opensource.org/licenses/postgresql
 */
CREATE OR REPLACE FUNCTION is_numeric(s text) RETURNS BOOLEAN AS $$
BEGIN
	PERFORM s::NUMERIC;
	RETURN TRUE;
EXCEPTION WHEN others THEN
	RETURN FALSE;
END;
$$
STRICT
LANGUAGE plpgsql IMMUTABLE
;
COMMENT ON FUNCTION is_numeric(s text) IS 'Checks, whether the given parameter is a number';

/**
 * Creates a function to check strings for being BIGINT.
 *
 * @author: "Stefanie Janine Stölting"<stefanie@proopensource.eu>
 * @license: PostgreSQL https://opensource.org/licenses/postgresql
 */

CREATE OR REPLACE FUNCTION is_bigint(s text) RETURNS BOOLEAN AS $$
BEGIN
	PERFORM s::BIGINT;
	RETURN TRUE;
EXCEPTION WHEN others THEN
	RETURN FALSE;
END;
$$
STRICT
LANGUAGE plpgsql IMMUTABLE
;
COMMENT ON FUNCTION is_bigint(s text) IS 'Checks, whether the given parameter is a BIGINT';

/**
 * Creates a function to check strings for being INTEGER.
 *
 * @author: "Stefanie Janine Stölting"<stefanie@proopensource.eu>
 * @license: PostgreSQL https://opensource.org/licenses/postgresql
 */

CREATE OR REPLACE FUNCTION is_integer(s text) RETURNS BOOLEAN AS $$
BEGIN
	PERFORM s::INTEGER;
	RETURN TRUE;
EXCEPTION WHEN others THEN
	RETURN FALSE;
END;
$$
STRICT
LANGUAGE plpgsql IMMUTABLE
;
COMMENT ON FUNCTION is_integer(s text) IS 'Checks, whether the given parameter is an INTEGER';

/**
 * Creates a function to check strings for being SMALLINT.
 *
 * @author: "Stefanie Janine Stölting"<stefanie@proopensource.eu>
 * @license: PostgreSQL https://opensource.org/licenses/postgresql
 */

CREATE OR REPLACE FUNCTION is_smallint(s text) RETURNS BOOLEAN AS $$
BEGIN
	PERFORM s::SMALLINT;
	RETURN TRUE;
EXCEPTION WHEN others THEN
	RETURN FALSE;
END;
$$
STRICT
LANGUAGE plpgsql IMMUTABLE
;
COMMENT ON FUNCTION is_smallint(s text) IS 'Checks, whether the given parameter is a smallint';

/**
 * Creates a function to check strings for being INTEGER.
 *
 * @author: "Stefanie Janine Stölting"<stefanie@proopensource.eu>
 * @license: PostgreSQL https://opensource.org/licenses/postgresql
 */

CREATE OR REPLACE FUNCTION is_real(s text) RETURNS BOOLEAN AS $$
BEGIN
	PERFORM s::REAL;
	RETURN TRUE;
EXCEPTION WHEN others THEN
	RETURN FALSE;
END;
$$
STRICT
LANGUAGE plpgsql IMMUTABLE
;
COMMENT ON FUNCTION is_real(s text) IS 'Checks, whether the given parameter is a REAL';

/**
 * Creates a function to check strings for being INTEGER.
 *
 * @author: "Stefanie Janine Stölting"<stefanie@proopensource.eu>
 * @license: PostgreSQL https://opensource.org/licenses/postgresql
 */

CREATE OR REPLACE FUNCTION is_double_precision(s text) RETURNS BOOLEAN AS $$
BEGIN
	PERFORM s::DOUBLE PRECISION;
	RETURN TRUE;
EXCEPTION WHEN others THEN
	RETURN FALSE;
END;
$$
STRICT
LANGUAGE plpgsql IMMUTABLE
;
COMMENT ON FUNCTION is_double_precision(s text) IS 'Checks, whether the given parameter is a DOUBLE PRECISION';

/**
 * Creates a function to check strings for being BOOLEAN.
 *
 * @author: "Stefanie Janine Stölting"<stefanie@proopensource.eu>
 * @license: PostgreSQL https://opensource.org/licenses/postgresql
 */

CREATE OR REPLACE FUNCTION is_boolean(s text) RETURNS BOOLEAN AS $$
BEGIN
	PERFORM s::BOOLEAN;
	RETURN TRUE;
EXCEPTION WHEN others THEN
	RETURN FALSE;
END;
$$
STRICT
LANGUAGE plpgsql IMMUTABLE
;
COMMENT ON FUNCTION is_boolean(s text) IS 'Checks, whether the given parameter is a BOOLEAN';

/**
 * Creates a function to check strings for being JSON.
 *
 * @author: "Stefanie Janine Stölting"<stefanie@proopensource.eu>
 * @license: PostgreSQL https://opensource.org/licenses/postgresql
 */

CREATE OR REPLACE FUNCTION is_json(s text) RETURNS BOOLEAN AS $$
BEGIN
	PERFORM s::JSON;
	RETURN TRUE;
EXCEPTION WHEN others THEN
	RETURN FALSE;
END;
$$
STRICT
LANGUAGE plpgsql IMMUTABLE
;
COMMENT ON FUNCTION is_json(s text) IS 'Checks, whether the given text is a JSON';

/**
 * Creates a function to check strings for being JSONB.
 *
 * @author: "Stefanie Janine Stölting"<stefanie@proopensource.eu>
 * @license: PostgreSQL https://opensource.org/licenses/postgresql
 */

CREATE OR REPLACE FUNCTION is_jsonb(s text) RETURNS BOOLEAN AS $$
BEGIN
	PERFORM s::JSONB;
	RETURN TRUE;
EXCEPTION WHEN others THEN
	RETURN FALSE;
END;
$$
STRICT
LANGUAGE plpgsql IMMUTABLE
;
COMMENT ON FUNCTION is_jsonb(s text) IS 'Checks, whether the given text is a JSONB';

/**
 * Creates a function to check strings for being UUID.
 *
 * @author: "Stefanie Janine Stölting"<stefanie@proopensource.eu>
 * @license: PostgreSQL https://opensource.org/licenses/postgresql
 */

CREATE OR REPLACE FUNCTION is_uuid(s text) RETURNS BOOLEAN AS $$
BEGIN
	PERFORM s::UUID;
	RETURN TRUE;
EXCEPTION WHEN others THEN
	RETURN FALSE;
END;
$$
STRICT
LANGUAGE plpgsql IMMUTABLE
;
COMMENT ON FUNCTION is_uuid(s text) IS 'Checks, whether the given parameter is a uuid';

/**
 * Creates a function which returns the size of a schema.
 *
 * @author: "Stefanie Janine Stölting"<stefanie@proopensource.eu>
 * @license: PostgreSQL https://opensource.org/licenses/postgresql
 */

CREATE OR REPLACE FUNCTION pg_schema_size(text) RETURNS BIGINT AS $$
	SELECT COALESCE(SUM(pg_total_relation_size(quote_ident(schemaname) || '.' || quote_ident(tablename)))::BIGINT, 0) AS schema_size
	FROM pg_tables
	WHERE schemaname = $1
$$ LANGUAGE SQL STRICT IMMUTABLE;
COMMENT ON FUNCTION pg_schema_size(text) IS 'Returns the size for given schema name';

/**
 * Creates two functions to check strings about encodings.
 * The first function checks if an UTF-8 string does only contain characters
 * in the given second parameter.
 * The second parameter takes as third parameter the encoding in which the
 * string is and checks if the string does only contain characters as given in
 * the second parameter.
 *
 * @author: "Stefanie Janine Stölting"<stefanie@proopensource.eu>
 * @license: PostgreSQL https://opensource.org/licenses/postgresql
 */

CREATE OR REPLACE FUNCTION is_encoding(s text, enc text) RETURNS BOOLEAN AS $$
BEGIN
	PERFORM convert(s::bytea, 'UTF8', enc);
	RETURN TRUE;
EXCEPTION WHEN others THEN
	RETURN FALSE;
END;
$$
STRICT
LANGUAGE plpgsql IMMUTABLE
;
COMMENT ON FUNCTION is_encoding(s text, enc text) IS 'Checks, whether the given UTF8 sting contains only encodings in the given encoding characters';


CREATE OR REPLACE FUNCTION is_encoding(s text, enc text, enc_from text) RETURNS BOOLEAN AS $$
BEGIN
	PERFORM convert(s::bytea, enc_from, enc);
	RETURN TRUE;
EXCEPTION WHEN others THEN
	RETURN FALSE;
END;
$$
STRICT
LANGUAGE plpgsql IMMUTABLE
;
COMMENT ON FUNCTION is_encoding(s text, enc text, enc_from text) IS 'Checks, whether the given encoding sting contains only encodings in the given encoding characters';

/**
 * Creates a function to check UTF-8 strings for containing only Latin1
 * characters.
 *
 * @author: "Stefanie Janine Stölting"<stefanie@proopensource.eu>
 * @license: PostgreSQL https://opensource.org/licenses/postgresql
 */

CREATE OR REPLACE FUNCTION is_latin1(s text) RETURNS BOOLEAN AS $$
BEGIN
	PERFORM convert(s::bytea, 'UTF8', 'LATIN1');
	RETURN TRUE;
EXCEPTION WHEN others THEN
	RETURN FALSE;
END;
$$
STRICT
LANGUAGE plpgsql IMMUTABLE
;
COMMENT ON FUNCTION is_latin1(s text) IS 'Checks, whether the given parameter contains only latin1 characters';

/**
 * Creates a function which returns a distinct array with all non latin1
 * characters . Depends on function is_latin1 which is part of this repository.
 *
 * @author: "Stefanie Janine Stölting"<stefanie@proopensource.eu>
 * @license: PostgreSQL https://opensource.org/licenses/postgresql
 */

CREATE OR REPLACE FUNCTION return_not_part_of_latin1(s text) RETURNS text[] AS $$
DECLARE
	i INTEGER := 0;
	res text[];
	current_s text := NULL::text[];
BEGIN

	LOOP
		EXIT WHEN i > length(s);
		i := i + 1;
		current_s := substring(s FROM i FOR 1);
		IF (NOT is_latin1(current_s)) THEN
			SELECT array_append(res, current_s) INTO res;
		END IF;
	END LOOP;

	WITH t1 AS
		(
			SELECT unnest(res) AS c1
		)
	, t2 AS
		(
			SELECT DISTINCT c1
			FROM t1
		)
	, t3 AS
		(
			SELECT array_agg(c1) AS res_array
			FROM t2
		)
	SELECT res_array
	FROM t3
	INTO res;

	RETURN res;
END;
$$
STRICT
LANGUAGE plpgsql IMMUTABLE
;
COMMENT ON FUNCTION return_not_part_of_latin1(s text) IS 'Creates a function which returns a distinct array with all non latin1 characters';

/**
 * Creates three function to replace characters, that are not part of the given
 * encoding.
 * The function does depend on the function is_encoding which is part of this
 * repository.
 *
 * @author: "Stefanie Janine Stölting"<stefanie@proopensource.eu>
 * @license: PostgreSQL https://opensource.org/licenses/postgresql
 */

CREATE OR REPLACE FUNCTION replace_encoding(s text, e text) RETURNS text AS $$
DECLARE
	i INTEGER := 0;
	res text;
BEGIN
	res := s;

	LOOP
		EXIT WHEN i > length(res);
		i := i + 1;
		IF (NOT is_encoding(substring(res FROM i FOR 1 ), e)) THEN
			res := OVERLAY(res PLACING '' FROM i FOR 1);
		END IF;
	END LOOP;

	RETURN res;
END;
$$
STRICT
LANGUAGE plpgsql IMMUTABLE
;
COMMENT ON FUNCTION replace_encoding(s text, e text) IS 'Replaces all characters, which are not part of the given encoding, with spaces and returns the result only with characters which are part of the given encoding';


CREATE OR REPLACE FUNCTION replace_encoding(s text, e text, replacement text) RETURNS text AS $$
DECLARE
	i INTEGER := 0;
	res text;
BEGIN
	res := s;

	LOOP
		EXIT WHEN i > length(res);
		i := i + 1;
		IF (NOT is_encoding(substring(res FROM i FOR 1 ), e)) THEN
			res := OVERLAY(res PLACING replacement FROM i FOR 1);
		END IF;
	END LOOP;

	RETURN res;
END;
$$
STRICT
LANGUAGE plpgsql IMMUTABLE
;
COMMENT ON FUNCTION replace_encoding(s text, e text, replacement text) IS 'Replaces all characters, which are not part of the given encoding, with the given replacement in the third parameter and returns the result only with characters which are part of the given encoding';


CREATE OR REPLACE FUNCTION replace_encoding(s text, s_search text[], s_replace text[]) RETURNS text AS $$
DECLARE
	i INTEGER := 0;
	res text;
	length_equal BOOLEAN;
	a_count INTEGER;
BEGIN

	SELECT array_length(s_search, 1) = array_length(s_replace, 1) INTO length_equal;

	IF NOT length_equal THEN
		RAISE 'Search and replacement arrays do not have the same count of entries' USING ERRCODE = '22000';
	END IF;

	SELECT array_length(s_search, 1) INTO a_count;
	res := s;

	LOOP
		EXIT WHEN i >= a_count;
		i := i + 1;

		res := REPLACE(res, s_search[i], s_replace[i]);

	END LOOP;

	RETURN res;

END;
$$
STRICT
LANGUAGE plpgsql IMMUTABLE
;
COMMENT ON FUNCTION replace_encoding(s text, s_search text[], s_replace text[]) IS 'Replaces charactes given in s_search with characters given in s_replace at the same array position';

/**
 * Creates two function to replace characters, that are not part of latin1.
 * The function does depend on the function is_latin1 which is part of this
 * repository.
 *
 * @author: "Stefanie Janine Stölting"<stefanie@proopensource.eu>
 * @license: PostgreSQL https://opensource.org/licenses/postgresql
 */

CREATE OR REPLACE FUNCTION replace_latin1(s text) RETURNS text AS $$
DECLARE
	i INTEGER := 0;
	res text;
BEGIN
	res := s;

	LOOP
		EXIT WHEN i > length(res);
		i := i + 1;
		IF (NOT is_latin1(substring(res FROM i FOR 1 ))) THEN
			res := OVERLAY(res PLACING '' FROM i FOR 1);
		END IF;
	END LOOP;

	RETURN res;
END;
$$
STRICT
LANGUAGE plpgsql IMMUTABLE
;
COMMENT ON FUNCTION replace_latin1(s text) IS 'Replaces all not latin1 characters with spaces and returns the result with only containing latin1 characters';


CREATE OR REPLACE FUNCTION replace_latin1(s text, replacement text) RETURNS text AS $$
DECLARE
	i INTEGER := 0;
	res text;
BEGIN
	res := s;

	LOOP
		EXIT WHEN i > length(res);
		i := i + 1;
		IF (NOT is_latin1(substring(res FROM i FOR 1 ))) THEN
			res := OVERLAY(res PLACING replacement FROM i FOR 1);
		END IF;
	END LOOP;

	RETURN res;
END;
$$
STRICT
LANGUAGE plpgsql IMMUTABLE
;
COMMENT ON FUNCTION replace_latin1(s text, replacement text) IS 'Replaces all not latin1 characters with the given replacement in the second parameter and returns the result with only containing latin1 characters';


CREATE OR REPLACE FUNCTION replace_latin1(s text, s_search text[], s_replace text[]) RETURNS text AS $$
DECLARE
	res text;
BEGIN

	SELECT replace_encoding(s, s_search, s_replace) INTO res;

	RETURN res;

END;
$$
STRICT
LANGUAGE plpgsql IMMUTABLE
;
COMMENT ON FUNCTION replace_latin1(s text, s_search text[], s_replace text[]) IS 'Replaces charactes given in s_search with characters given in s_replace at the same array position. The function is an alias for replace_encoding.';

/**
 * Creates a function which returns a distinct array with all non latin1 characters .
 *
 * @author: "Stefanie Janine Stölting"<stefanie@proopensource.eu>
 * @license: PostgreSQL https://opensource.org/licenses/postgresql
 */

CREATE OR REPLACE FUNCTION return_not_part_of_encoding(s text, e text) RETURNS text[] AS $$
DECLARE
	i INTEGER := 0;
	res text[];
	current_s text := NULL::text[];
BEGIN

	LOOP
		EXIT WHEN i > length(s);
		i := i + 1;
		current_s := substring(s FROM i FOR 1);
		IF (NOT is_encoding(current_s, e)) THEN
			SELECT array_append(res, current_s) INTO res;
		END IF;
	END LOOP;

	WITH t1 AS
		(
			SELECT unnest(res) AS c1
		)
	, t2 AS
		(
			SELECT DISTINCT c1
			FROM t1
		)
	, t3 AS
		(
			SELECT array_agg(c1) AS res_array
			FROM t2
		)
	SELECT res_array
	FROM t3
	INTO res;

	RETURN res;
END;
$$
STRICT
LANGUAGE plpgsql IMMUTABLE
;
COMMENT ON FUNCTION return_not_part_of_encoding(s text, e text) IS 'Creates a function which returns a distinct array with all characters which are not part of the encoding give in e';

/**
 * Create a window function to calculate values for gaps.
 *
 * @author: "Stefanie Janine Stölting"<stefanie@proopensource.eu>
 * @license: PostgreSQL https://opensource.org/licenses/postgresql
 */

CREATE OR REPLACE FUNCTION gap_fill_internal(s anyelement, v anyelement)
RETURNS anyelement AS
$$
BEGIN
	RETURN COALESCE(v, s);
END;
$$ LANGUAGE PLPGSQL IMMUTABLE;
COMMENT ON FUNCTION gap_fill_internal(s anyelement, v anyelement) IS 'The function is used to fill gaps in window functions';


-- The Window function needs an aggregate
DROP AGGREGATE IF EXISTS gap_fill(anyelement);
CREATE AGGREGATE gap_fill(anyelement) (
	SFUNC=gap_fill_internal,
	STYPE = anyelement
)
;
COMMENT ON AGGREGATE gap_fill(anyelement) IS 'Implements the aggregate function to fill gaps using the function GapFillInternal';

/**
 * Creates a function which returns the given date in German format.
 *
 * @author: "Stefanie Janine Stölting"<stefanie@proopensource.eu>
 * @license: PostgreSQL https://opensource.org/licenses/postgresql
 */

 CREATE OR REPLACE FUNCTION date_de(d DATE) RETURNS text AS $$
 BEGIN
	RETURN to_char(d, 'DD.MM.YYYY');
 END;
 $$
 STRICT
 LANGUAGE plpgsql IMMUTABLE;
 COMMENT ON FUNCTION date_de(d DATE) IS 'Creates a function which returns the given date in German format';

/**
 * Creates a function which returns the given timestamp in German format.
 * The second parameter indicates, if the result is with or without time zone,
 * default is with thime zone
 *
 * @author: "Stefanie Janine Stölting"<stefanie@proopensource.eu>
 * @license: PostgreSQL https://opensource.org/licenses/postgresql
 */

 CREATE OR REPLACE FUNCTION datetime_de(t TIMESTAMP WITH TIME ZONE, with_tz BOOLEAN DEFAULT TRUE) RETURNS text AS $$
 BEGIN
 	IF with_tz THEN
 		RETURN to_char(t, 'DD.MM.YYYY HH24:MI:SS TZ');
 	ELSE
 		RETURN to_char(t, 'DD.MM.YYYY HH24:MI:SS');
 	END IF;
 END;
 $$
 STRICT
 LANGUAGE plpgsql IMMUTABLE;
 COMMENT ON FUNCTION datetime_de(t TIMESTAMP WITH TIME ZONE, with_tz BOOLEAN) IS 'Creates a function which returns the given timestamp in German format';

/**
 * Creates two functions which returns unix timestamp for the a given timestamp
 * or a given timestamp with time zone.
 *
 * @author: "Stefanie Janine Stölting"<stefanie@proopensource.eu>
 * @license: PostgreSQL https://opensource.org/licenses/postgresql
 */

CREATE OR REPLACE FUNCTION to_unix_timestamp(ts timestamp) RETURNS bigint AS $$
	SELECT EXTRACT (EPOCH FROM ts)::bigint;
$$ LANGUAGE SQL STRICT IMMUTABLE
;
COMMENT ON FUNCTION to_unix_timestamp(ts timestamp) IS 'Returns an unix timestamp for the given timestamp';

CREATE OR REPLACE FUNCTION to_unix_timestamp(ts timestamp with time zone) RETURNS bigint AS $$
	SELECT EXTRACT (EPOCH FROM ts)::bigint;
$$ LANGUAGE SQL STRICT IMMUTABLE
;
COMMENT ON FUNCTION to_unix_timestamp(ts timestamp with time zone) IS 'Returns an unix timestamp for the given timestamp with time zone';

/**
 * Creates a function to checks a string variable for being either, NULL or ''.
 *
 * @author: "Stefanie Janine Stölting"<stefanie@proopensource.eu>
 * @license: PostgreSQL https://opensource.org/licenses/postgresql
 */

CREATE OR REPLACE FUNCTION is_empty(s text) RETURNS BOOLEAN AS $f1$
BEGIN
        RETURN COALESCE(s, '') = '';
END;
$f1$
LANGUAGE plpgsql IMMUTABLE
;

COMMENT ON FUNCTION is_empty(s text) IS 'Checks, whether the given parameter is NULL or ''''';

/**
 * Returns the maximum value of an array.
 * Implementation for BIGINT, INTEGER, SMALLINT, TEXT
 *
 * @author: "Stefanie Janine Stölting"<stefanie@proopensource.eu>
 * @license: PostgreSQL https://opensource.org/licenses/postgresql
 */

-- BIGINT implementation
CREATE OR REPLACE FUNCTION array_max(a BIGINT[]) RETURNS BIGINT AS $$
DECLARE
	res BIGINT;
BEGIN

	WITH unnested AS
 		(
			SELECT UNNEST(a) AS vals
		)
	SELECT max(vals) FROM unnested AS x INTO res;

	RETURN res;

END;
$$
STRICT
LANGUAGE plpgsql IMMUTABLE
;
COMMENT ON FUNCTION array_max(a BIGINT[]) IS 'Returns the maximum value of a BIGINT array';

-- INTEGER implementation
CREATE OR REPLACE FUNCTION array_max(a INTEGER[]) RETURNS BIGINT AS $$
DECLARE
	res INTEGER;
BEGIN

	WITH unnested AS
 		(
			SELECT UNNEST(a) AS vals
		)
		SELECT max(vals) FROM unnested AS x INTO res;

	RETURN res;

END;
$$
STRICT
LANGUAGE plpgsql IMMUTABLE
;
COMMENT ON FUNCTION array_max(a INTEGER[]) IS 'Returns the maximum value of an INTEGER array';

-- SMALLINT implementation
CREATE OR REPLACE FUNCTION array_max(a SMALLINT[]) RETURNS BIGINT AS $$
DECLARE
	res SMALLINT;
BEGIN

	WITH unnested AS
 		(
			SELECT UNNEST(a) AS vals
		)
		SELECT max(vals) FROM unnested AS x INTO res;

	RETURN res;

END;
$$
STRICT
LANGUAGE plpgsql IMMUTABLE
;
COMMENT ON FUNCTION array_max(a SMALLINT[]) IS 'Returns the maximum value of a SMALLINT array';

-- TEXT implementation
CREATE OR REPLACE FUNCTION array_max(a TEXT[]) RETURNS TEXT AS $$
DECLARE
	res TEXT;
BEGIN

	WITH unnested AS
 		(
			SELECT UNNEST(a) AS vals
		)
		SELECT max(vals) FROM unnested AS x INTO res;

	RETURN res;

END;
$$
STRICT
LANGUAGE plpgsql IMMUTABLE
;
COMMENT ON FUNCTION array_max(a TEXT[]) IS 'Returns the maximum value of a TEXT array';

-- REAL implementation
CREATE OR REPLACE FUNCTION array_max(a REAL[]) RETURNS NUMERIC AS $$
DECLARE
	res NUMERIC;
BEGIN

	WITH unnested AS
 		(
			SELECT UNNEST(a) AS vals
		)
		SELECT max(vals) FROM unnested AS x INTO res;

	RETURN res;

END;
$$
STRICT
LANGUAGE plpgsql IMMUTABLE
;
COMMENT ON FUNCTION array_max(a REAL[]) IS 'Returns the maximum value of a REAL array';

-- DOUBLE PRECISION implementation
CREATE OR REPLACE FUNCTION array_max(a DOUBLE PRECISION[]) RETURNS NUMERIC AS $$
DECLARE
	res NUMERIC;
BEGIN

	WITH unnested AS
 		(
			SELECT UNNEST(a) AS vals
		)
		SELECT max(vals) FROM unnested AS x INTO res;

	RETURN res;

END;
$$
STRICT
LANGUAGE plpgsql IMMUTABLE
;
COMMENT ON FUNCTION array_max(a DOUBLE PRECISION[]) IS 'Returns the maximum value of a DOUBLE PRECISION array';

-- NUMERIC implementation
CREATE OR REPLACE FUNCTION array_max(a NUMERIC[]) RETURNS NUMERIC AS $$
DECLARE
	res NUMERIC;
BEGIN

	WITH unnested AS
 		(
			SELECT UNNEST(a) AS vals
		)
		SELECT max(vals) FROM unnested AS x INTO res;

	RETURN res;

END;
$$
STRICT
LANGUAGE plpgsql IMMUTABLE
;
COMMENT ON FUNCTION array_max(a NUMERIC[]) IS 'Returns the maximum value of a NUMERIC array';

/**
 * Returns the minumum value of an array.
 * Implementation for BIGINT, INTEGER, SMALLINT, TEXT
 *
 * @author: "Stefanie Janine Stölting"<stefanie@proopensource.eu>
 * @license: PostgreSQL https://opensource.org/licenses/postgresql
 */

-- BIGINT implementation
CREATE OR REPLACE FUNCTION array_min(a BIGINT[]) RETURNS BIGINT AS $$
DECLARE
	res BIGINT;
BEGIN

	WITH unnested AS
 		(
			SELECT UNNEST(a) AS vals
		)
	SELECT min(vals) FROM unnested AS x INTO res;

	RETURN res;

END;
$$
STRICT
LANGUAGE plpgsql IMMUTABLE
;
COMMENT ON FUNCTION array_min(a BIGINT[]) IS 'Returns the minumum value of a BIGINT array';

-- INTEGER implementation
CREATE OR REPLACE FUNCTION array_min(a INTEGER[]) RETURNS INTEGER AS $$
DECLARE
	res INTEGER;
BEGIN

	WITH unnested AS
 		(
			SELECT UNNEST(a) AS vals
		)
		SELECT min(vals) FROM unnested AS x INTO res;

	RETURN res;

END;
$$
STRICT
LANGUAGE plpgsql IMMUTABLE
;
COMMENT ON FUNCTION array_min(a INTEGER[]) IS 'Returns the minumum value of an INTEGER array';

-- SMALLINT implementation
CREATE OR REPLACE FUNCTION array_min(a SMALLINT[]) RETURNS SMALLINT AS $$
DECLARE
	res SMALLINT;
BEGIN

	WITH unnested AS
 		(
			SELECT UNNEST(a) AS vals
		)
		SELECT min(vals) FROM unnested AS x INTO res;

	RETURN res;

END;
$$
STRICT
LANGUAGE plpgsql IMMUTABLE
;
COMMENT ON FUNCTION array_min(a SMALLINT[]) IS 'Returns the minumum value of a SMALLINT array';

-- TEXT implementation
CREATE OR REPLACE FUNCTION array_min(a TEXT[]) RETURNS TEXT AS $$
DECLARE
	res TEXT;
BEGIN

	WITH unnested AS
 		(
			SELECT UNNEST(a) AS vals
		)
		SELECT min(vals) FROM unnested AS x INTO res;

	RETURN res;

END;
$$
STRICT
LANGUAGE plpgsql IMMUTABLE
;
COMMENT ON FUNCTION array_min(a TEXT[]) IS 'Returns the minumum value of a TEXT array';

-- REAL implementation
CREATE OR REPLACE FUNCTION array_min(a REAL[]) RETURNS NUMERIC AS $$
DECLARE
	res NUMERIC;
BEGIN

	WITH unnested AS
 		(
			SELECT UNNEST(a) AS vals
		)
		SELECT min(vals) FROM unnested AS x INTO res;

	RETURN res;

END;
$$
STRICT
LANGUAGE plpgsql IMMUTABLE
;
COMMENT ON FUNCTION array_min(a REAL[]) IS 'Returns the minumum value of a REAL array';

-- DOUBLE PRECISION implementation
CREATE OR REPLACE FUNCTION array_min(a DOUBLE PRECISION[]) RETURNS NUMERIC AS $$
DECLARE
	res NUMERIC;
BEGIN

	WITH unnested AS
 		(
			SELECT UNNEST(a) AS vals
		)
		SELECT min(vals) FROM unnested AS x INTO res;

	RETURN res;

END;
$$
STRICT
LANGUAGE plpgsql IMMUTABLE
;
COMMENT ON FUNCTION array_min(a DOUBLE PRECISION[]) IS 'Returns the minumum value of a DOUBLE PRECISION array';

-- NUMERIC implementation
CREATE OR REPLACE FUNCTION array_min(a NUMERIC[]) RETURNS NUMERIC AS $$
DECLARE
	res REAL;
BEGIN

	WITH unnested AS
 		(
			SELECT UNNEST(a) AS vals
		)
		SELECT min(vals) FROM unnested AS x INTO res;

	RETURN res;

END;
$$
STRICT
LANGUAGE plpgsql IMMUTABLE
;
COMMENT ON FUNCTION array_min(a NUMERIC[]) IS 'Returns the minumum value of a NUMERIC array';

/**
 * Returns the average value of an array.
 * Implementation for BIGINT, INTEGER, SMALLINT
 *
 * @author: "Stefanie Janine Stölting"<stefanie@proopensource.eu>
 * @license: PostgreSQL https://opensource.org/licenses/postgresql
 */

-- BIGINT implementation
CREATE OR REPLACE FUNCTION array_avg(a BIGINT[]) RETURNS NUMERIC AS $$
DECLARE
	res NUMERIC;
BEGIN

	WITH unnested AS
 		(
			SELECT UNNEST(a) AS vals
		)
	SELECT avg(vals) FROM unnested AS x INTO res;

	RETURN res;

END;
$$
STRICT
LANGUAGE plpgsql IMMUTABLE
;
COMMENT ON FUNCTION array_avg(a BIGINT[]) IS 'Returns the average value of a BIGINT array';

-- INTEGER implementation
CREATE OR REPLACE FUNCTION array_avg(a INTEGER[]) RETURNS NUMERIC AS $$
DECLARE
	res NUMERIC;
BEGIN

	WITH unnested AS
 		(
			SELECT UNNEST(a) AS vals
		)
		SELECT avg(vals) FROM unnested AS x INTO res;

	RETURN res;

END;
$$
STRICT
LANGUAGE plpgsql IMMUTABLE
;
COMMENT ON FUNCTION array_avg(a INTEGER[]) IS 'Returns the average value of an INTEGER array';

-- SMALLINT implementation
CREATE OR REPLACE FUNCTION array_avg(a SMALLINT[]) RETURNS NUMERIC AS $$
DECLARE
	res NUMERIC;
BEGIN

	WITH unnested AS
 		(
			SELECT UNNEST(a) AS vals
		)
		SELECT avg(vals) FROM unnested AS x INTO res;

	RETURN res;

END;
$$
STRICT
LANGUAGE plpgsql IMMUTABLE
;
COMMENT ON FUNCTION array_avg(a SMALLINT[]) IS 'Returns the average value of a SMALLINT array';

--REAL implementation
CREATE OR REPLACE FUNCTION array_avg(a REAL[]) RETURNS NUMERIC AS $$
DECLARE
	res NUMERIC;
BEGIN

	WITH unnested AS
 		(
			SELECT UNNEST(a) AS vals
		)
		SELECT avg(vals) FROM unnested AS x INTO res;

	RETURN res;

END;
$$
STRICT
LANGUAGE plpgsql IMMUTABLE
;
COMMENT ON FUNCTION array_avg(a REAL[]) IS 'Returns the average value of a REAL array';

-- DOUBLE PRECISION implementation
CREATE OR REPLACE FUNCTION array_avg(a DOUBLE PRECISION[]) RETURNS NUMERIC AS $$
DECLARE
	res NUMERIC;
BEGIN

	WITH unnested AS
 		(
			SELECT UNNEST(a) AS vals
		)
		SELECT avg(vals) FROM unnested AS x INTO res;

	RETURN res;

END;
$$
STRICT
LANGUAGE plpgsql IMMUTABLE
;
COMMENT ON FUNCTION array_avg(a DOUBLE PRECISION[]) IS 'Returns the average value of a DOUBLE PRECISION array';

-- NUMERIC implementation
CREATE OR REPLACE FUNCTION array_avg(a NUMERIC[]) RETURNS NUMERIC AS $$
DECLARE
	res NUMERIC;
BEGIN

	WITH unnested AS
 		(
			SELECT UNNEST(a) AS vals
		)
		SELECT avg(vals) FROM unnested AS x INTO res;

	RETURN res;

END;
$$
STRICT
LANGUAGE plpgsql IMMUTABLE
;
COMMENT ON FUNCTION array_avg(a NUMERIC[]) IS 'Returns the average value of a NUMERIC array';

/**
 * Returns the sum of values of an array.
 * Implementation for BIGINT, INTEGER, SMALLINT
 *
 * @author: "Stefanie Janine Stölting"<stefanie@proopensource.eu>
 * @license: PostgreSQL https://opensource.org/licenses/postgresql
 */

-- BIGINT implementation
CREATE OR REPLACE FUNCTION array_sum(a BIGINT[]) RETURNS BIGINT AS $$
DECLARE
	res BIGINT;
BEGIN

	WITH unnested AS
 		(
			SELECT UNNEST(a) AS vals
		)
	SELECT sum(vals) FROM unnested AS x INTO res;

	RETURN res;

END;
$$
STRICT
LANGUAGE plpgsql IMMUTABLE
;
COMMENT ON FUNCTION array_sum(a BIGINT[]) IS 'Returns the sum of values of a BIGINT array';

-- INTEGER implementation
CREATE OR REPLACE FUNCTION array_sum(a INTEGER[]) RETURNS BIGINT AS $$
DECLARE
	res BIGINT;
BEGIN

	WITH unnested AS
 		(
			SELECT UNNEST(a) AS vals
		)
		SELECT sum(vals) FROM unnested AS x INTO res;

	RETURN res;

END;
$$
STRICT
LANGUAGE plpgsql IMMUTABLE
;
COMMENT ON FUNCTION array_sum(a INTEGER[]) IS 'Returns the sum of values of an INTEGER array';

-- SMALLINT implementation
CREATE OR REPLACE FUNCTION array_sum(a SMALLINT[]) RETURNS BIGINT AS $$
DECLARE
	res BIGINT;
BEGIN

	WITH unnested AS
 		(
			SELECT UNNEST(a) AS vals
		)
		SELECT sum(vals) FROM unnested AS x INTO res;

	RETURN res;

END;
$$
STRICT
LANGUAGE plpgsql IMMUTABLE
;
COMMENT ON FUNCTION array_sum(a SMALLINT[]) IS 'Returns the sum of values of a SMALLINT array';

-- REAL implementation
CREATE OR REPLACE FUNCTION array_sum(a REAL[]) RETURNS NUMERIC AS $$
DECLARE
	res NUMERIC;
BEGIN

	WITH unnested AS
 		(
			SELECT UNNEST(a) AS vals
		)
		SELECT sum(vals) FROM unnested AS x INTO res;

	RETURN res;

END;
$$
STRICT
LANGUAGE plpgsql IMMUTABLE
;
COMMENT ON FUNCTION array_sum(a REAL[]) IS 'Returns the sum of values of a REAL array';

-- DOUBLE PRECISION implementation
CREATE OR REPLACE FUNCTION array_sum(a DOUBLE PRECISION[]) RETURNS NUMERIC AS $$
DECLARE
	res NUMERIC;
BEGIN

	WITH unnested AS
 		(
			SELECT UNNEST(a) AS vals
		)
		SELECT sum(vals) FROM unnested AS x INTO res;

	RETURN res;

END;
$$
STRICT
LANGUAGE plpgsql IMMUTABLE
;
COMMENT ON FUNCTION array_sum(a DOUBLE PRECISION[]) IS 'Returns the sum of values of a DOUBLE PRECISION array';

-- NUMERIC implementation
CREATE OR REPLACE FUNCTION array_sum(a NUMERIC[]) RETURNS NUMERIC AS $$
DECLARE
	res NUMERIC;
BEGIN

	WITH unnested AS
 		(
			SELECT UNNEST(a) AS vals
		)
		SELECT sum(vals) FROM unnested AS x INTO res;

	RETURN res;

END;
$$
STRICT
LANGUAGE plpgsql IMMUTABLE
;
COMMENT ON FUNCTION array_sum(a NUMERIC[]) IS 'Returns the sum of values of a NUMERIC array';

/**
 * Removes empty strings and null entries from a given array. In addition the
 * function can remove duplicate entries. The function supports strings,
 * numbers, dates, and timestamps with or without time zone
 *
 * @author: "Stefanie Janine Stölting"<stefanie@proopensource.eu>
 * @license: PostgreSQL https://opensource.org/licenses/postgresql
 */

CREATE OR REPLACE FUNCTION array_trim(a text[], rd BOOLEAN DEFAULT FALSE) RETURNS text[] AS $$
DECLARE
	res text[];
BEGIN

	IF rd THEN
		WITH t1 AS
			(
				SELECT DISTINCT unnest(a) AS vals
			)
		SELECT array_agg(vals)
		FROM t1
		WHERE vals != ''
		INTO res;
	ELSE
		WITH t1 AS
			(
				SELECT unnest(a) AS vals
			)
		SELECT array_agg(vals)
		FROM t1
		WHERE vals != ''
		INTO res;
	END IF;

	RETURN res;
END;
$$
STRICT
LANGUAGE plpgsql IMMUTABLE
;
COMMENT ON FUNCTION array_trim(a text[], rd BOOLEAN) IS 'Removes empty entries from a text array, can remove duplicates, too';

CREATE OR REPLACE FUNCTION array_trim(a SMALLINT[], rd BOOLEAN DEFAULT FALSE) RETURNS SMALLINT[] AS $$
DECLARE
	res SMALLINT[];
BEGIN

	IF rd THEN
		WITH t1 AS
			(
				SELECT DISTINCT unnest(a) AS vals
			)
		SELECT array_agg(vals)
		FROM t1
		WHERE NOT vals IS NULL
		INTO res;
	ELSE
		WITH t1 AS
			(
				SELECT unnest(a) AS vals
			)
		SELECT array_agg(vals)
		FROM t1
		WHERE NOT vals IS NULL
		INTO res;
	END IF;

	RETURN res;
END;
$$
STRICT
LANGUAGE plpgsql IMMUTABLE
;
COMMENT ON FUNCTION array_trim(a SMALLINT[], rd BOOLEAN) IS 'Removes empty entries from a SMALLINT array, can remove duplicates, too';

CREATE OR REPLACE FUNCTION array_trim(a INTEGER[], rd BOOLEAN DEFAULT FALSE) RETURNS INTEGER[] AS $$
DECLARE
	res INTEGER[];
BEGIN

	IF rd THEN
		WITH t1 AS
			(
				SELECT DISTINCT unnest(a) AS vals
			)
		SELECT array_agg(vals)
		FROM t1
		WHERE NOT vals IS NULL
		INTO res;
	ELSE
		WITH t1 AS
			(
				SELECT unnest(a) AS vals
			)
		SELECT array_agg(vals)
		FROM t1
		WHERE NOT vals IS NULL
		INTO res;
	END IF;

	RETURN res;
END;
$$
STRICT
LANGUAGE plpgsql IMMUTABLE
;
COMMENT ON FUNCTION array_trim(a INTEGER[], rd BOOLEAN) IS 'Removes empty entries from a INTEGER array, can remove duplicates, too';

CREATE OR REPLACE FUNCTION array_trim(a BIGINT[], rd BOOLEAN DEFAULT FALSE) RETURNS BIGINT[] AS $$
DECLARE
	res BIGINT[];
BEGIN

	IF rd THEN
		WITH t1 AS
			(
				SELECT DISTINCT unnest(a) AS vals
			)
		SELECT array_agg(vals)
		FROM t1
		WHERE NOT vals IS NULL
		INTO res;
	ELSE
		WITH t1 AS
			(
				SELECT unnest(a) AS vals
			)
		SELECT array_agg(vals)
		FROM t1
		WHERE NOT vals IS NULL
		INTO res;
	END IF;

	RETURN res;
END;
$$
STRICT
LANGUAGE plpgsql IMMUTABLE
;
COMMENT ON FUNCTION array_trim(a BIGINT[], rd BOOLEAN) IS 'Removes empty entries from a BIGINT array, can remove duplicates, too';

CREATE OR REPLACE FUNCTION array_trim(a NUMERIC[], rd BOOLEAN DEFAULT FALSE) RETURNS NUMERIC[] AS $$
DECLARE
	res NUMERIC[];
BEGIN

	IF rd THEN
		WITH t1 AS
			(
				SELECT DISTINCT unnest(a) AS vals
			)
		SELECT array_agg(vals)
		FROM t1
		WHERE NOT vals IS NULL
		INTO res;
	ELSE
		WITH t1 AS
			(
				SELECT unnest(a) AS vals
			)
		SELECT array_agg(vals)
		FROM t1
		WHERE NOT vals IS NULL
		INTO res;
	END IF;

	RETURN res;
END;
$$
STRICT
LANGUAGE plpgsql IMMUTABLE
;
COMMENT ON FUNCTION array_trim(a NUMERIC[], rd BOOLEAN) IS 'Removes empty entries from a NUMERIC array, can remove duplicates, too';

CREATE OR REPLACE FUNCTION array_trim(a REAL[], rd BOOLEAN DEFAULT FALSE) RETURNS REAL[] AS $$
DECLARE
	res REAL[];
BEGIN

	IF rd THEN
		WITH t1 AS
			(
				SELECT DISTINCT unnest(a) AS vals
			)
		SELECT array_agg(vals)
		FROM t1
		WHERE NOT vals IS NULL
		INTO res;
	ELSE
		WITH t1 AS
			(
				SELECT unnest(a) AS vals
			)
		SELECT array_agg(vals)
		FROM t1
		WHERE NOT vals IS NULL
		INTO res;
	END IF;

	RETURN res;
END;
$$
STRICT
LANGUAGE plpgsql IMMUTABLE
;
COMMENT ON FUNCTION array_trim(a REAL[], rd BOOLEAN) IS 'Removes empty entries from a REAL array, can remove duplicates, too';

CREATE OR REPLACE FUNCTION array_trim(a DOUBLE PRECISION[], rd BOOLEAN DEFAULT FALSE) RETURNS DOUBLE PRECISION[] AS $$
DECLARE
	res DOUBLE PRECISION[];
BEGIN

	IF rd THEN
		WITH t1 AS
			(
				SELECT DISTINCT unnest(a) AS vals
			)
		SELECT array_agg(vals)
		FROM t1
		WHERE NOT vals IS NULL
		INTO res;
	ELSE
		WITH t1 AS
			(
				SELECT unnest(a) AS vals
			)
		SELECT array_agg(vals)
		FROM t1
		WHERE NOT vals IS NULL
		INTO res;
	END IF;

	RETURN res;
END;
$$
STRICT
LANGUAGE plpgsql IMMUTABLE
;
COMMENT ON FUNCTION array_trim(a DOUBLE PRECISION[], rd BOOLEAN) IS 'Removes empty entries from a DOUBLE PRECISION array, can remove duplicates, too';

CREATE OR REPLACE FUNCTION array_trim(a DATE[], rd BOOLEAN DEFAULT FALSE) RETURNS DATE[] AS $$
DECLARE
	res DATE[];
BEGIN

	IF rd THEN
		WITH t1 AS
			(
				SELECT DISTINCT unnest(a) AS vals
			)
		SELECT array_agg(vals)
		FROM t1
		WHERE NOT vals IS NULL
		INTO res;
	ELSE
		WITH t1 AS
			(
				SELECT unnest(a) AS vals
			)
		SELECT array_agg(vals)
		FROM t1
		WHERE NOT vals IS NULL
		INTO res;
	END IF;

	RETURN res;
END;
$$
STRICT
LANGUAGE plpgsql IMMUTABLE
;
COMMENT ON FUNCTION array_trim(a DATE[], rd BOOLEAN) IS 'Removes empty entries from a DATE array, can remove duplicates, too';

CREATE OR REPLACE FUNCTION array_trim(a TIMESTAMP[], rd BOOLEAN DEFAULT FALSE) RETURNS TIMESTAMP[] AS $$
DECLARE
	res TIMESTAMP[];
BEGIN

	IF rd THEN
		WITH t1 AS
			(
				SELECT DISTINCT unnest(a) AS vals
			)
		SELECT array_agg(vals)
		FROM t1
		WHERE NOT vals IS NULL
		INTO res;
	ELSE
		WITH t1 AS
			(
				SELECT unnest(a) AS vals
			)
		SELECT array_agg(vals)
		FROM t1
		WHERE NOT vals IS NULL
		INTO res;
	END IF;

	RETURN res;
END;
$$
STRICT
LANGUAGE plpgsql IMMUTABLE
;
COMMENT ON FUNCTION array_trim(a TIMESTAMP[], rd BOOLEAN) IS 'Removes empty entries from a TIMESTAMP array, can remove duplicates, too';

CREATE OR REPLACE FUNCTION array_trim(a TIMESTAMP WITH TIME ZONE[], rd BOOLEAN DEFAULT FALSE) RETURNS TIMESTAMP WITH TIME ZONE[] AS $$
DECLARE
	res TIMESTAMP WITH TIME ZONE[];
BEGIN

	IF rd THEN
		WITH t1 AS
			(
				SELECT DISTINCT unnest(a) AS vals
			)
		SELECT array_agg(vals)
		FROM t1
		WHERE NOT vals IS NULL
		INTO res;
	ELSE
		WITH t1 AS
			(
				SELECT unnest(a) AS vals
			)
		SELECT array_agg(vals)
		FROM t1
		WHERE NOT vals IS NULL
		INTO res;
	END IF;

	RETURN res;
END;
$$
STRICT
LANGUAGE plpgsql IMMUTABLE
;
COMMENT ON FUNCTION array_trim(a TIMESTAMP WITH TIME ZONE[], rd BOOLEAN) IS 'Removes empty entries from a TIMESTAMP WITH TIME ZONE array, can remove duplicates, too';

/**
 * Creates a function to convert a hexadicimal number given as string into a BIGINT.
 *
 * @author: "Stefanie Janine Stölting"<stefanie@proopensource.eu>
 * @license: PostgreSQL https://opensource.org/licenses/postgresql
 */

 CREATE OR REPLACE FUNCTION hex2bigint(s TEXT) RETURNS BIGINT AS $$
 	WITH RECURSIVE hex_sep AS
 		(
 			SELECT
 			  	cast (cast (cast ('x0' || substring (s, length (s), 1) AS BIT(8)) AS INT) * POWER(16, 0) AS BIGINT ) int_res
 				, substring (s, 1 , length (s) - 1) AS rest
 				, length (s) - 1 AS len
 				, 1 AS row_num
 			UNION ALL
 			SELECT
 				cast (cast (cast ('x0' || substring (rest, length (rest), 1) AS BIT(8)) AS INT)  * POWER(16, row_num) AS BIGINT) int_res
 				, substring (rest, 1 , length (rest) - 1) AS rest
 				, length (rest) - 1 AS len
 				, row_num + 1 AS row_num
 			FROM hex_sep
 			WHERE len > 0
 		)
 	SELECT cast (sum(int_res)AS BIGINT)
 	FROM hex_sep
 	;
 $$ LANGUAGE sql IMMUTABLE STRICT
 ;
COMMENT ON FUNCTION hex2bigint(s TEXT) IS 'Converts a hexadicimal number given as string into a BIGINT';

/**
 * Creates a function to check hexadeciaml numbers passed as strings for being hexadeciaml fitting into a 0BIGINT.
 *
 * @author: "Stefanie Janine Stölting"<stefanie@proopensource.eu>
 * @license: PostgreSQL https://opensource.org/licenses/postgresql
 */

CREATE OR REPLACE FUNCTION is_hex(s TEXT) RETURNS BOOLEAN AS $$
BEGIN
   PERFORM hex2bigint (s);
   RETURN TRUE;
EXCEPTION WHEN others THEN
   RETURN FALSE;
END;
$$
STRICT
LANGUAGE plpgsql IMMUTABLE
;
COMMENT ON FUNCTION is_hex(s TEXT) IS 'Checks, whether the given parameter is a hexadeciaml number fitting into a BIGINT';

/**
 * Creates a function to check strings for being a BIGINT array.
 *
 * @author: "Stefanie Janine Stölting"<stefanie@proopensource.eu>
 * @license: PostgreSQL https://opensource.org/licenses/postgresql
 */

CREATE OR REPLACE FUNCTION is_bigint_array(s text) RETURNS BOOLEAN AS $$
BEGIN
	PERFORM s::BIGINT[];
	RETURN TRUE;
EXCEPTION WHEN others THEN
	RETURN FALSE;
END;
$$
STRICT
LANGUAGE plpgsql IMMUTABLE
;
COMMENT ON FUNCTION is_bigint_array(s text) IS 'Checks, whether the given parameter is a BIGINT array';

/**
 * Creates a function to check strings for being an INTEGER array.
 *
 * @author: "Stefanie Janine Stölting"<stefanie@proopensource.eu>
 * @license: PostgreSQL https://opensource.org/licenses/postgresql
 */

CREATE OR REPLACE FUNCTION is_integer_array(s text) RETURNS BOOLEAN AS $$
BEGIN
	PERFORM s::INTEGER[];
	RETURN TRUE;
EXCEPTION WHEN others THEN
	RETURN FALSE;
END;
$$
STRICT
LANGUAGE plpgsql IMMUTABLE
;
COMMENT ON FUNCTION is_integer_array(s text) IS 'Checks, whether the given parameter is an INTEGER array';

/**
 * Creates a function to check strings for being an SMALLINT array.
 *
 * @author: "Stefanie Janine Stölting"<stefanie@proopensource.eu>
 * @license: PostgreSQL https://opensource.org/licenses/postgresql
 */

CREATE OR REPLACE FUNCTION is_smallint_array(s text) RETURNS BOOLEAN AS $$
BEGIN
	PERFORM s::SMALLINT[];
	RETURN TRUE;
EXCEPTION WHEN others THEN
	RETURN FALSE;
END;
$$
STRICT
LANGUAGE plpgsql IMMUTABLE
;
COMMENT ON FUNCTION is_smallint_array(s text) IS 'Checks, whether the given parameter is a SMALLINT array';

/**
 * Creates a function to check strings for being an SMALLINT array.
 *
 * @author: "Stefanie Janine Stölting"<stefanie@proopensource.eu>
 * @license: PostgreSQL https://opensource.org/licenses/postgresql
 */

CREATE OR REPLACE FUNCTION is_text_array(s text) RETURNS BOOLEAN AS $$
BEGIN
	PERFORM s::TEXT[];
	RETURN TRUE;
EXCEPTION WHEN others THEN
	RETURN FALSE;
END;
$$
STRICT
LANGUAGE plpgsql IMMUTABLE
;
COMMENT ON FUNCTION is_text_array(s text) IS 'Checks, whether the given parameter is a TEXT array';

/**
 * Function to generate a documentation from database comments as Markdown for
 * a given Schema.
 * The result is a text in Markdown format.
 *
 * Currently the following objects are supported:
 * 	- Database
 * 	- Schema
 * 	- Tables with columns
 * 	- Views with columns
 * 	- Materialized Views with columns
 * 	- Foreign Tables with columns
 * 	- Functions
 * 	- Procedures
 *
 *
 * @author: "Stefanie Janine Stölting"<stefanie@proopensource.eu>
 * @license: PostgreSQL https://opensource.org/licenses/postgresql
 */

CREATE OR REPLACE FUNCTION get_markdown_doku_by_schema(in_schema_name TEXT, time_zone TEXT DEFAULT 'Europe/Berlin')
RETURNS TEXT
LANGUAGE PLPGSQL
AS
$$
DECLARE
	res TEXT DEFAULT '';
	current_table_name TEXT DEFAULT '';
	current_table_type TEXT DEFAULT '';
	current_program_name TEXT DEFAULT '';
	current_mv_name TEXT DEFAULT '';
	table_header TEXT;
	time_zone_exists BOOLEAN;
	curs_tables CURSOR FOR
		WITH indexes AS
			(
				SELECT schemaname
					, tablename
					, array_agg(indexname) AS indexes
				FROM pg_indexes
				GROUP BY schemaname
					, tablename
			)
		SELECT t.table_type
			, c.table_catalog AS database_name
			, c.table_schema AS schema_name
			, c.table_name
			, c.column_name
			, COALESCE (array_to_string (i.indexes, ';'), '') AS indexes
			, COALESCE (c.column_default, '') AS column_default
			, c.is_nullable
			, c.data_type
			, COALESCE (c.character_maximum_length::TEXT, '') AS character_maximum_length
			, COALESCE (de.description, '') AS database_description
			, obj_description(('"' || c.table_schema || '"')::regnamespace) AS schema_description
			, COALESCE (OBJ_DESCRIPTION(('"' || c.table_schema || '"."' || c.table_name || '"')::regclass), '') AS table_description
			, COALESCE (COL_DESCRIPTION(('"' || c.table_schema || '"."' || c.table_name || '"')::regclass, c.ordinal_position), '') AS column_description
		FROM information_schema.columns AS c
		INNER JOIN information_schema."tables" AS t
			USING (table_catalog, table_schema, table_name)
		INNER JOIN pg_catalog.pg_database AS d
			ON c.table_catalog = d.datname
		LEFT OUTER JOIN pg_catalog.pg_shdescription AS de
			ON d."oid" = de.objoid		LEFT OUTER JOIN indexes AS i
			ON c.table_schema = i.schemaname
			AND c.table_name = i.tablename
		WHERE c.table_schema = in_schema_name
		ORDER BY t.table_type
			, c.table_name
			, c.ordinal_position
		;
	table_record RECORD;
	curs_programs CURSOR FOR
		SELECT (current_database())::information_schema.sql_identifier AS database_name
			, pg_namespace.nspname AS schema_name
			, pg_proc.proname AS program_name
			, COALESCE (pg_catalog.pg_get_function_arguments(pg_proc.oid), '') AS parameters
			, COALESCE (pg_catalog.pg_get_function_result(pg_proc.oid), '') AS returning_data_type
			, CASE
					WHEN pg_proc.prokind = 'a' THEN
						'AGGREGATE'
					WHEN pg_proc.prokind = 'w' THEN
						'WINDOW'
					WHEN pg_proc.prokind = 'f' THEN
						'FUNCTION'
					WHEN pg_proc.prorettype = 'pg_catalog.trigger'::pg_catalog.regtype THEN
						'TRIGGER'
					ELSE
						'UNKNOWN'
				END AS program_type
			, pg_language.lanname AS program_language
			, COALESCE (pg_description.description, '') AS program_description
		FROM pg_catalog.pg_proc
		INNER JOIN pg_catalog.pg_language
			ON pg_proc.prolang = pg_language.oid
		LEFT OUTER JOIN pg_catalog.pg_namespace
			ON pg_proc.pronamespace = pg_namespace.oid
		LEFT OUTER JOIN pg_catalog.pg_description
			ON pg_proc.oid = pg_description.objoid
		WHERE pg_catalog.pg_function_is_visible(pg_proc.oid)
			AND pg_namespace.nspname = in_schema_name
		ORDER BY schema_name
			, program_name
			, parameters
		;
	program_record RECORD;
	curs_mv CURSOR FOR
		WITH indexes AS
			(
				SELECT schemaname
					, tablename
					, array_agg(indexname) AS indexes
				FROM pg_indexes
				GROUP BY schemaname
					, tablename
			)
		SELECT (current_database())::information_schema.sql_identifier AS database_name
			  , n.nspname AS schema_name
		    , c.relname AS table_name
		    , col.attname AS column_name
			, COALESCE (array_to_string (i.indexes, ';'), '') AS indexes
		    , format_type (col.atttypid, col.atttypmod) AS data_type
			, COALESCE (obj_description (c."oid"), '') AS materialized_view_description
			, COALESCE (COL_DESCRIPTION(('"' || n.nspname || '"."' || c.relname || '"')::regclass, col.attnum), '') AS column_description
		FROM pg_catalog.pg_class AS c
		INNER JOIN pg_catalog.pg_namespace AS n
			ON c.relnamespace = n.oid
		INNER JOIN pg_catalog.pg_attribute AS col
			ON c.oid = col.attrelid
		LEFT OUTER JOIN indexes AS i
			ON n.nspname = i.schemaname
			AND c.relname = i.tablename
		WHERE c.relkind = 'm'
		    AND col.attnum >= 1
		    AND n.nspname = in_schema_name
		ORDER BY schema_name
		    , table_name
		    , column_name
		;
	mv_record RECORD;
BEGIN
	-- Check given time zone for existence
	EXECUTE 'SELECT count (*) = 1 AS exist
		FROM pg_catalog.pg_timezone_names
		WHERE name::TEXT = $1'
		USING time_zone
		INTO time_zone_exists
	;

	IF NOT time_zone_exists THEN
		RAISE EXCEPTION 'Time zone % does not exist!', time_zone;
	END IF;

	table_header = '| Column | Default | Is Nullable | Data Type | Maximum Length | Description |' || CHR(13) || '|--|--|--|--|--|--|'  || CHR(13);

	-- TABLES and VIEWS
	OPEN curs_tables;

	FETCH NEXT FROM curs_tables INTO table_record;
	WHILE FOUND LOOP
		IF current_table_name = '' THEN
			res = '# DATABASE ' ||
				table_record.database_name || ' SCHEMA ' || table_record.schema_name ||
				CHR(13) || CHR(13) ||
				'Generated: ' || TO_CHAR (now() AT time ZONE time_zone, 'YYYY-MM-DD HH24:MI') ||
				CHR(13) || CHR(13) ||
				'## Database Description' ||
				CHR(13) || CHR(13) ||
				table_record.database_description ||
				CHR(13) || CHR(13) ||
				'## Schema Description' ||
				CHR(13) || CHR(13) ||
				table_record.schema_description ||
				CHR(13) || CHR(13)
			;

		END IF;

		IF current_table_type <> table_record.table_type THEN
			current_table_type = table_record.table_type;
			CASE table_record.table_type
				WHEN 'BASE TABLE' THEN
					res = res ||
						'## Tables in Schema ' || table_record.schema_name ||
						CHR(13) || CHR(13)
					;

				WHEN 'VIEW' THEN
					res = res ||
						'## Views in Schema ' || table_record.schema_name ||
						CHR(13) || CHR(13)
					;

				WHEN 'FOREIGN' THEN
					res = res ||
						'## Foreign Tables in Schema ' || table_record.schema_name ||
						CHR(13) || CHR(13)
					;
				ELSE
					res = res;
			END CASE;
		END IF;

		IF current_table_name <> table_record.table_name THEN
			current_table_name = table_record.table_name;
			res = res || '### ' || table_record.table_name
				|| CHR(13) || CHR(13)
			;

			res = res || table_record.table_description ||
				CHR(13) || CHR(13)
			;

			IF table_record.indexes <> '' THEN
				res = res || 'Indexes: ' || table_record.indexes ||
					CHR(13) || CHR(13)
				;
			END IF;

			res = res || table_header;

		END IF;

		res = res || '| ' || table_record.column_name || ' | ' ||
			table_record.column_default || ' | ' ||
			table_record.is_nullable || ' | ' ||
			table_record.data_type || ' | ' ||
			table_record.character_maximum_length || ' | ' ||
			table_record.column_description || ' |' ||
			CHR(13)
		;

		FETCH NEXT FROM curs_tables INTO table_record;
	END LOOP;

	CLOSE curs_tables;

	-- MATERIALIZED VIEWS
	OPEN curs_mv;

	FETCH NEXT FROM curs_mv INTO mv_record;
	WHILE FOUND LOOP

			IF current_mv_name = '' THEN
			res = res || CHR(13) ||
				'## MATERIALIZED VIEWS in DATABASE ' ||
				mv_record.database_name ||
				' SCHEMA ' ||
				mv_record.schema_name ||
				CHR(13) || CHR(13)
			;
		END IF;

		IF current_mv_name <> mv_record.table_name THEN
			current_mv_name = mv_record.table_name;
			res = res || '### ' || mv_record.table_name ||
				CHR(13) || CHR(13) ||
				mv_record.materialized_view_description ||
				CHR(13) || CHR(13)
			;

			IF mv_record.indexes <> '' THEN
				res = res || 'Indexes: ' || mv_record.indexes ||
					CHR(13) || CHR(13)
				;
			END IF;

			res = res || '| Column | Data Type |  | Description |' || CHR(13) ||
				'|--|--|--|'  || CHR(13)
			;
		END IF;


		res = res || '| ' || mv_record.column_name || ' | ' ||
			mv_record.data_type || ' | ' ||
			mv_record.column_description || ' |' ||
			CHR(13)
		;

		FETCH NEXT FROM curs_mv INTO mv_record;
	END LOOP;

	CLOSE curs_mv;

	-- PROGRAMS AND FUNCTIONS
	OPEN curs_programs;
	FETCH NEXT FROM curs_programs INTO program_record;
	WHILE FOUND LOOP


		IF current_program_name = '' THEN
			current_program_name = program_record.program_name;
			res = res || CHR(13) || '## FUNCTIONS and PROCEDURES in DATABASE ' || program_record.database_name || ' SCHEMA ' || program_record.schema_name ||
				CHR(13) || CHR(13) ||
				'| Program Name | Type | Language | Parameters | Returning | Description |' || CHR(13) ||
				'|--|--|--|--|--|--|' || CHR(13)
			;
		END IF;
		res = res || '| ' || program_record.program_name || ' | ' ||
			program_record.program_type || ' | ' ||
			program_record.program_language || ' | ' ||
			program_record.parameters || ' | ' ||
			program_record.returning_data_type || ' |' ||
			program_record.program_description || ' | ' ||
			CHR(13)
		;

		FETCH NEXT FROM curs_programs INTO program_record;
	END LOOP;

	CLOSE curs_programs;

	RETURN res;
END;
$$
;

COMMENT ON FUNCTION get_markdown_doku_by_schema(TEXT, TEXT) IS 'Function to generate a documentation from database comments as Markdown. The result is a text in Markdown format.';

/**
 * Returns a table contianing the inheritance path of a given rolename.
 *
 * @author: "Stefanie Janine Stölting"<stefanie@proopensource.eu>
 * @license: PostgreSQL https://opensource.org/licenses/postgresql
 */

 DO $$
 DECLARE
 	version_greater_15 BOOLEAN;
	function_source TEXT;
 BEGIN
 	SELECT to_number((string_to_array(version(), ' '))[2], '999.99') >= 16
	INTO version_greater_15;

 	IF version_greater_15 THEN
 		-- Create the function for PostgreSQL 16 or newer
		function_source :=
$string$
CREATE OR REPLACE FUNCTION role_inheritance (role_name text)
	RETURNS table("oid" oid, rolename text, steps integer, role_path text, inherit_option boolean, set_option boolean)
AS $function$
BEGIN
	RETURN QUERY
	WITH RECURSIVE rcte AS
		(
			SELECT pr.oid AS "oid"
				, 0 AS steps
				, pr.oid::regrole::text AS path
				, pr.rolinherit AS inherit_option
				, NULL::boolean AS set_option
			FROM   pg_roles AS pr
			WHERE  rolname = role_name
			UNION ALL
			SELECT m.roleid
				, rcte.steps + 1
				, rcte.path || '->' || m.roleid::regrole::text
				, rcte.inherit_option AND m.inherit_option
				, m.set_option
			FROM rcte
			INNER JOIN pg_auth_members AS m
				ON rcte."oid" = m.member
	   )
	SELECT rcte."oid"
		, rcte."oid"::regrole::text AS rolename
		, rcte.steps
		, rcte.path AS role_path
		, rcte.inherit_option
		, rcte.set_option
	FROM rcte
	;
END;
$function$
LANGUAGE plpgsql
;
$string$
;
	ELSE
		-- Create the function for PostgreSQL older than 16
		function_source :=
$string$
CREATE OR REPLACE FUNCTION role_inheritance (role_name text)
	RETURNS table("oid" oid, rolename text, steps integer, role_path text)
AS $function$
BEGIN
	RETURN QUERY
	WITH RECURSIVE rcte AS
		(
			SELECT pr.oid AS "oid"
				, 0 AS steps
				, pr.oid::regrole::text AS path
			FROM   pg_roles AS pr
			WHERE  rolname = role_name
			UNION ALL
			SELECT m.roleid
				, rcte.steps + 1
				, rcte.path || '->' || m.roleid::regrole::text
			FROM rcte
			INNER JOIN pg_auth_members AS m
				ON rcte."oid" = m.member
	   )
	SELECT rcte."oid"
		, rcte."oid"::regrole::text AS rolename
		, rcte.steps
		, rcte.path AS role_path
	FROM rcte
	;
END;
$function$
LANGUAGE plpgsql
;
$string$
;
	END IF;

	EXECUTE function_source;

	COMMENT ON FUNCTION role_inheritance(s text) IS 'Returns a table contianing the inheritance path of a given rolename.';
ENd
$$;

/**
 * Creates a view to get all views of the current database but excluding system views and all views which do start with 'pg' or '_pg'.
 *
 * @author: "Stefanie Janine Stölting"<stefanie@proopensource.eu>
 * @license: PostgreSQL https://opensource.org/licenses/postgresql
 */

CREATE OR REPLACE VIEW pg_db_views AS
SELECT table_catalog AS view_catalog
	, table_schema AS view_schema
	, table_name AS view_name
	, view_definition
FROM INFORMATION_SCHEMA.views
WHERE NOT table_name LIKE 'pg%'
	AND NOT table_name LIKE '\\_pg%'
	AND table_schema NOT IN ('pg_catalog', 'information_schema')
ORDER BY table_catalog
	, table_schema
	, table_name
;

COMMENT ON VIEW pg_db_views IS 'Creates a view to get all views of the current database but excluding system views and all views which do start with ''pg'' or ''_pg''';
COMMENT ON COLUMN pg_db_views.view_catalog IS 'The database, where the view is created in.';
COMMENT ON COLUMN pg_db_views.view_schema IS 'The schema, where the view is created in.';
COMMENT ON COLUMN pg_db_views.view_name IS 'The name of the view.';
COMMENT ON COLUMN pg_db_views.view_definition IS 'The source code of the view.';

/**
 * Creates a view to get all foreign keys of the current database.
 *
 * @author: "Stefanie Janine Stölting"<stefanie@proopensource.eu>
 * @license: PostgreSQL https://opensource.org/licenses/postgresql
 */

CREATE OR REPLACE VIEW pg_foreign_keys AS
SELECT ccu.constraint_name
	, tc.is_deferrable
	, tc.initially_deferred
	, tc."enforced"
	, tc.table_schema
	, tc.table_name
	, kcu.column_name
	, ccu.table_schema AS foreign_table_schema
	, ccu.TABLE_NAME AS foreign_table_name
	, ccu.COLUMN_NAME AS foreign_column_name
	, EXISTS
		(
			SELECT 1
			FROM pg_catalog.pg_index AS i
			WHERE i.indrelid = cs.conrelid
				AND i.indpred IS NULL
				AND (i.indkey::smallint[])[0:cardinality(cs.conkey)-1] OPERATOR(pg_catalog.@>) cs.conkey
		) AS is_indexed
	FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS AS tc
	INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE AS kcu
		ON tc.CONSTRAINT_NAME = kcu.CONSTRAINT_NAME
	INNER JOIN INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE AS ccu
		ON ccu.CONSTRAINT_NAME = tc.CONSTRAINT_NAME
	INNER JOIN PG_CATALOG.PG_NAMESPACE AS n
		ON tc.table_schema = n.nspname
	INNER JOIN PG_CATALOG.PG_CONSTRAINT AS cs
		ON n."oid" = cs.connamespace
		AND tc.constraint_name = cs.conname
WHERE tc.CONSTRAINT_TYPE = 'FOREIGN KEY'
;

COMMENT ON VIEW pg_foreign_keys IS 'The view returns all foreign keys of the current database';
COMMENT ON COLUMN pg_foreign_keys.constraint_name IS 'The name of the constraint.';
COMMENT ON COLUMN pg_foreign_keys.is_deferrable IS 'Whether the setting can be changed within a transaction.';
COMMENT ON COLUMN pg_foreign_keys.initially_deferred IS 'Whether each row is checked on insert or at the end of the transaction.';
COMMENT ON COLUMN pg_foreign_keys.enforced IS 'For PostgreSQL versions <18 it is alway TRUE as the feature is not availabe, starting with PostgreSQL 18 it is TRUE, when the constraint is enforced.';
COMMENT ON COLUMN pg_foreign_keys.table_schema IS 'The name of the schema';
COMMENT ON COLUMN pg_foreign_keys.table_name IS 'The name of the table.';
COMMENT ON COLUMN pg_foreign_keys.column_name IS 'Name of the column used in the foreign key constraint.';
COMMENT ON COLUMN pg_foreign_keys.foreign_table_schema IS 'The name of the foreign schema.';
COMMENT ON COLUMN pg_foreign_keys.foreign_table_name IS 'The name of the foreign table.';
COMMENT ON COLUMN pg_foreign_keys.foreign_column_name IS 'Name of the column used in the foreign key constraint of a foreign table.';
COMMENT ON COLUMN pg_foreign_keys.is_indexed IS 'Whether an index exists for the foreign key constraint, or not.';

/**
 * Creates a view to get all functions of the current database.
 *
 * @author: "Stefanie Janine Stölting"<stefanie@proopensource.eu>
 * @license: PostgreSQL https://opensource.org/licenses/postgresql
 */

CREATE OR REPLACE VIEW pg_functions AS
SELECT pg_namespace.nspname AS schema_name
	, pg_proc.proname AS function_name
	, pg_catalog.pg_get_function_result(pg_proc.oid) AS returning_data_type
	, pg_catalog.pg_get_function_arguments(pg_proc.oid) AS parameters
	, CASE
			WHEN pg_proc.prokind = 'a' THEN
				'aggregate'
			WHEN pg_proc.prokind = 'w' THEN
				'window'
			WHEN pg_proc.prokind = 'f' THEN
				'function'
			WHEN pg_proc.prorettype = 'pg_catalog.trigger'::pg_catalog.regtype THEN
				'trigger'
			ELSE
				'unknown'
		END AS function_type
	, pg_description.description AS function_comment
FROM pg_catalog.pg_proc
	LEFT OUTER JOIN pg_catalog.pg_namespace
		ON pg_proc.pronamespace = pg_namespace.oid
	LEFT OUTER JOIN pg_catalog.pg_description
		ON pg_proc.oid = pg_description.objoid
WHERE pg_catalog.pg_function_is_visible(pg_proc.oid)
	AND pg_namespace.nspname NOT IN ('pg_catalog', 'information_schema')
ORDER BY schema_name
	, function_name
	, parameters
;

-- Add a comment
COMMENT ON VIEW pg_functions IS 'The view returns all functions of the current database, excluding those in the schema pg_catalog and information_schema';
COMMENT ON COLUMN pg_functions.schema_name IS 'The name of the schema where the function is stored in.';
COMMENT ON COLUMN pg_functions.function_name IS 'The name of the function or procedure.';
COMMENT ON COLUMN pg_functions.returning_data_type IS 'The data type that the function is returning.';
COMMENT ON COLUMN pg_functions.parameters IS 'Parameters used to call the function.';
COMMENT ON COLUMN pg_functions.function_type IS 'The type of function, can be aggregate, window, function, trigger, or unknown.';
COMMENT ON COLUMN pg_functions.function_comment IS 'The comment, that is added to descrbe the function.';

/**
 * Creates a view to get information about table and materialized views in the
 * current database. It includes their sizes and indexes.
 *
 * @author: "Stefanie Janine Stölting"<stefanie@proopensource.eu>
 * @license: PostgreSQL https://opensource.org/licenses/postgresql
 */

CREATE OR REPLACE VIEW pg_table_matview_infos AS
WITH indexes AS
	(
		SELECT schemaname
			, tablename
			, array_agg(indexname) AS indexes
		FROM pg_indexes
		GROUP BY schemaname
			, tablename
	)
SELECT 'table' AS type
	, n.nspname AS schema_name
	, c.relname AS object_name
	, pg_get_userbyid (c.relowner) AS object_owner
	, t.spcname AS TABLESPACE
	, i.indexes
	, pg_table_size (c.oid) AS object_size
	, pg_indexes_size(c.oid) AS indexes_size
	, pg_total_relation_size(c.oid) AS total_object_size
	, pg_size_pretty(pg_table_size(c.oid)) AS total_object_size_pretty
	, pg_size_pretty(pg_indexes_size(c.oid)) AS indexes_size_pretty
	, pg_size_pretty(pg_total_relation_size(c.oid)) AS total_relation_size_pretty
FROM pg_class AS c
	LEFT OUTER JOIN pg_namespace AS n
		ON n.oid = c.relnamespace
	LEFT OUTER JOIN pg_tablespace AS t
		ON t.oid = c.reltablespace
	LEFT OUTER JOIN indexes AS i
		ON n.nspname = i.schemaname
		AND c.relname = i.tablename
WHERE c.relkind = ANY (ARRAY['r'::"char", 'p'::"char"])
	AND n.nspname NOT IN ('pg_catalog', 'information_schema')
UNION ALL
SELECT 'matview' AS type
	, n.nspname AS schema_name
	, c.relname AS object_name
	, pg_get_userbyid(c.relowner) AS object_owner
	, t.spcname AS tablespace
	, i.indexes
	, pg_table_size(c.oid) AS object_size
	, pg_indexes_size(c.oid) AS indexes_size
	, pg_total_relation_size(c.oid) AS total_object_size
	, pg_size_pretty(pg_table_size(c.oid)) AS object_size_pretty
	, pg_size_pretty(pg_indexes_size(c.oid)) AS indexes_size_pretty
	, pg_size_pretty(pg_total_relation_size(c.oid)) AS total_relation_size_pretty
FROM pg_class AS c
	LEFT OUTER JOIN pg_namespace AS n
		ON n.oid = c.relnamespace
	LEFT OUTER JOIN pg_tablespace t
		ON t.oid = c.reltablespace
	LEFT OUTER JOIN indexes AS i
		ON n.nspname = i.schemaname
		AND c.relname = i.tablename
WHERE c.relkind = 'm'::"char"
;

COMMENT ON VIEW pg_table_matview_infos IS 'The view shows detailed information about sizes and indexes of tables and materialized views';
COMMENT ON COLUMN pg_table_matview_infos.type IS 'The type of the result row, can be TABLE or MATERIALIZED VIEW.';
COMMENT ON COLUMN pg_table_matview_infos.schema_name IS 'The name of the schema where the object is stored.';
COMMENT ON COLUMN pg_table_matview_infos.object_name IS 'The name of the table or materialized view.';
COMMENT ON COLUMN pg_table_matview_infos.object_owner IS 'The role which is owning the obejct.';
COMMENT ON COLUMN pg_table_matview_infos.tablespace IS 'The table space where the object is stored.';
COMMENT ON COLUMN pg_table_matview_infos.indexes IS 'All indexes that have been created on this object.';
COMMENT ON COLUMN pg_table_matview_infos.object_size IS 'The size of the data of this object.';
COMMENT ON COLUMN pg_table_matview_infos.indexes_size IS 'The size of the indexes of this object.';
COMMENT ON COLUMN pg_table_matview_infos.total_object_size IS 'The overall size of this object.';
COMMENT ON COLUMN pg_table_matview_infos.total_object_size_pretty IS 'The size of the data of this object with unit.';
COMMENT ON COLUMN pg_table_matview_infos.indexes_size_pretty IS 'The size of the indexes of this object with unit.';
COMMENT ON COLUMN pg_table_matview_infos.total_relation_size_pretty IS 'The overall size of this object with the unit.';

/**
 * Creates a view to get information about partitioned tables.
 *
 * @author: "Stefanie Janine Stölting"<stefanie@proopensource.eu>
 * @license: PostgreSQL https://opensource.org/licenses/postgresql
 */

DO $$
DECLARE
	version_greater_10 BOOLEAN;
BEGIN
	SELECT to_number((string_to_array(version(), ' '))[2], '999.99') >= 10 INTO version_greater_10;

	IF version_greater_10 THEN
		-- Create the view pg_functions for PostgreSQL 10 or newer
		CREATE OR REPLACE VIEW pg_partitioned_tables_infos AS
		SELECT cl.oid AS parent_relid
			, n.nspname AS parent_schemaname
			, cl.relname AS parent_tablename
			, r.rolname AS parent_owner
			, CASE pt.partstrat
				WHEN 'l' THEN
					'LIST'
				WHEN 'r' THEN
					'RANGE'
				WHEN 'h' THEN
					'HASH'
			END AS partition_strategy
			, count (cl2.oid) OVER (PARTITION BY cl.oid) AS count_of_partitions
			, COALESCE (sum (pg_relation_size (cl2.oid)) OVER (PARTITION BY cl.oid), 0) AS overall_size
			, cl2.oid AS child_relid
			, n2.nspname AS child_schemaname
			, cl2.relname AS child_tablename
			, r2.rolname AS child_owner
			, pg_relation_size (cl2.oid) AS child_size
		FROM pg_catalog.pg_class AS cl
		INNER JOIN pg_catalog.pg_partitioned_table AS pt
			ON cl.oid = pt.partrelid
		INNER JOIN pg_catalog.pg_namespace AS n
			ON cl.relnamespace = n.oid
		INNER JOIN pg_catalog.pg_roles AS r
			 ON cl.relowner = r.oid
		LEFT OUTER JOIN pg_catalog.pg_inherits AS i
			ON cl.oid = i.inhparent
		LEFT OUTER JOIN pg_catalog.pg_class AS cl2
			ON i.inhrelid = cl2.oid
			AND cl2.relispartition
			AND cl2.relkind = 'r'
		LEFT OUTER JOIN pg_catalog.pg_namespace AS n2
			ON cl2.relnamespace = n2.oid
		LEFT OUTER JOIN pg_catalog.pg_roles AS r2
			 ON cl2.relowner = r2.oid
		WHERE cl.relkind = 'p'
		;

		COMMENT ON VIEW pg_partitioned_tables_infos IS 'Creates a view to get information about partitioned tables';
		COMMENT ON COLUMN pg_partitioned_tables_infos.parent_relid IS 'The oid of the parent table.';
		COMMENT ON COLUMN pg_partitioned_tables_infos.parent_schemaname IS 'The schema where the parent table is located.';
		COMMENT ON COLUMN pg_partitioned_tables_infos.parent_tablename IS 'The name of the parent table.';
		COMMENT ON COLUMN pg_partitioned_tables_infos.parent_owner IS 'The role name of the owner of the parent table.';
		COMMENT ON COLUMN pg_partitioned_tables_infos.partition_strategy IS 'The partition strategy of the partitioned table, this can be LIST, RANGE, or HASH.';
		COMMENT ON COLUMN pg_partitioned_tables_infos.count_of_partitions IS 'The number of partitions of the parent table.';
		COMMENT ON COLUMN pg_partitioned_tables_infos.overall_size IS 'The overall size of the table including all partitions.';
		COMMENT ON COLUMN pg_partitioned_tables_infos.child_relid IS 'The oid of the partition of a partitioned table.';
		COMMENT ON COLUMN pg_partitioned_tables_infos.child_schemaname IS 'The name of the schema where the partition of a partitioned table is located.';
		COMMENT ON COLUMN pg_partitioned_tables_infos.child_tablename IS 'The name of the partition of a partitioned table.';
		COMMENT ON COLUMN pg_partitioned_tables_infos.child_owner IS 'The role name of the owner of the partition of a partitioned table.';
		COMMENT ON COLUMN pg_partitioned_tables_infos.child_size IS 'The size of this partition.';
	END IF;

END $$;

/**
 * Creates a view to get all connections and their locks.
 *
 * @author: "Stefanie Janine Stölting"<stefanie@proopensource.eu>
 * @license: PostgreSQL https://opensource.org/licenses/postgresql
 */
 CREATE OR REPLACE VIEW pg_active_locks AS
 SELECT DISTINCT pid
 	, state
 	, datname AS database_name
 	, usename AS rolename
 	, application_name
 	, client_addr AS client_address
 	, query_start
	, age (now(), a.query_start) AS query_age
 	, wait_event_type
 	, wait_event
 	, locktype
 	, mode
 	, query
 FROM pg_stat_activity AS a
 INNER JOIN pg_locks AS l
 	USING(pid)
 ;

COMMENT ON VIEW pg_active_locks IS 'Creates a view to get all connections and their locks';
COMMENT ON COLUMN pg_active_locks.pid IS 'The process id of the backend, needed in case a connection should be terminated with the function pg-terminate_backend(pid). For details see [SERVER SIGNALING FUNCTIONS](https://www.postgresql.org/docs/current/functions-admin.html#FUNCTIONS-ADMIN-SIGNAL).';
COMMENT ON COLUMN pg_active_locks.state IS 'The current state of the connection.';
COMMENT ON COLUMN pg_active_locks.database_name IS 'The database name in which the queries have been executed.';
COMMENT ON COLUMN pg_active_locks.rolename IS 'The role/user who executed the queries.';
COMMENT ON COLUMN pg_active_locks.application_name IS 'The name of the application, could be empty if not set by a client.';
COMMENT ON COLUMN pg_active_locks.client_address IS 'The clients IP address.';
COMMENT ON COLUMN pg_active_locks.query_start IS 'The timestamp of the start of the query.';
COMMENT ON COLUMN pg_active_locks.query_age IS 'How long the query is already running.';
COMMENT ON COLUMN pg_active_locks.wait_event_type IS 'The type of event for which the backend is waiting, if any; otherwise NULL, for details see [WAIT EVENT TABLE](https://www.postgresql.org/docs/current/monitoring-pg_html#WAIT-EVENT-TABLE).';
COMMENT ON COLUMN pg_active_locks.wait_event IS 'Wait event name if backend is currently waiting, otherwise NULL. For details see [WAIT EVENT ACTIVITY TABLE](https://www.postgresql.org/docs/current/monitoring-pg_html#WAIT-EVENT-ACTIVITY-TABLE) and [WAIT EVENT TIMEOUT TABLE](https://www.postgresql.org/docs/current/monitoring-pg_html#WAIT-EVENT-TIMEOUT-TABLE)';
COMMENT ON COLUMN pg_active_locks.locktype IS 'The type of the lockable object: relation, extend, frozenid, page, tuple, transactionid, virtualxid, spectoken, object, userlock, advisory, or applytransaction, see [Wait Events of Type Lock](https://www.postgresql.org/docs/current/monitoring-stats.html#WAIT-EVENT-LOCK-TABLE).';
COMMENT ON COLUMN pg_active_locks.mode IS 'The name of the lock mode held or desired by this process. For details see [TABLE LEVEL LOCKS](https://www.postgresql.org/docs/current/explicit-locking.html#LOCKING-TABLES) and [SERIALIZABLE ISOLATION LEVEL](https://www.postgresql.org/docs/current/transaction-iso.html#XACT-SERIALIZABLE)';
COMMENT ON COLUMN pg_active_locks.query IS 'This is the SQL source of the query.';

/**
 * Creates a view to get the ownership of all objects in the current database.
 *
 * @author: "Stefanie Janine Stölting"<stefanie@proopensource.eu>
 * @license: PostgreSQL https://opensource.org/licenses/postgresql
 */

CREATE OR REPLACE VIEW pg_object_ownership AS
WITH dbobjects AS
	(
		SELECT cls.oid
			, nsp.nspname AS object_schema
			, cls.relname AS object_name
			, rol.rolname AS owner
			, CASE cls.relkind
				WHEN 'I' THEN
					'PARTITIONED INDEX'
				WHEN 'S' THEN
					'SEQUENCE'
				WHEN 'c' THEN
					'COMPOSITE TYPE'
				WHEN 'f' THEN
					'FOREIGN TABLE'
				WHEN 'i' THEN
					'INDEX'
				WHEN 'm' THEN
					'MATERIALIZED_VIEW'
				WHEN 'p' THEN
					'PARTITIONED TABLE'
				WHEN 'r' THEN
					'TABLE'
				WHEN 'v' THEN
					'VIEW'
				ELSE
					cls.relkind::text
			END AS object_type
		FROM pg_catalog.pg_class AS cls
		INNER JOIN pg_roles AS rol
			ON cls.relowner = rol.oid
		INNER JOIN pg_catalog.pg_namespace AS nsp
			ON cls.relnamespace = nsp.oid
		UNION ALL
		SELECT db.oid
			, NULL AS object_schema
			, db.datname AS object_name
			, rol.rolname AS owner
			, 'DATABASE' AS object_type
		FROM pg_catalog.pg_database AS db
		INNER JOIN pg_roles AS rol
			ON db.datdba = rol.oid
		UNION ALL
		SELECT ext.oid
			, NULL AS object_schema
			, ext.extname
			, rol.rolname AS owner
			, 'EXTENSION' AS object_type
		FROM pg_catalog.pg_extension AS ext
		INNER JOIN pg_roles AS rol
			ON ext.extowner = rol.oid
		UNION ALL
		SELECT fdw.oid
			, NULL AS object_schema
			, fdw.fdwname AS object_name
			, rol.rolname AS owner
			, 'FOREIGN DATA WRAPPER' AS object_type
		FROM pg_catalog.pg_foreign_data_wrapper AS fdw
		INNER JOIN pg_roles AS rol
			ON fdw.fdwowner = rol.oid
		UNION ALL
		SELECT srv.oid
			, NULL AS object_schema
			, srv.srvname AS object_name
			, rol.rolname AS owner
			, 'FOREIGN SERVER' AS object_type
		FROM pg_catalog.pg_foreign_server AS srv
		INNER JOIN pg_roles AS rol
			ON srv.srvowner = rol.oid
		UNION ALL
		SELECT lang.oid
			, NULL AS object_schema
			, lang.lanname AS object_name
			, rol.rolname AS owner
			, 'LANGUAGE' AS object_type
		FROM pg_catalog.pg_language AS lang
		INNER JOIN pg_roles AS rol
			ON lang.lanowner = rol.oid
		UNION ALL
		SELECT nsp.oid
			, NULL AS object_schema
			, nsp.nspname AS object_name
			, rol.rolname AS owner
			, 'SCHEMA' AS object_type
		FROM pg_catalog.pg_namespace AS nsp
		INNER JOIN pg_roles AS rol
			ON nsp.nspowner = rol.oid
		UNION ALL
		SELECT opc.oid
			, NULL AS object_schema
			, opc.opcname AS object_name
			, rol.rolname AS owner
			, 'OPERATOR CLASS' AS object_type
		FROM pg_catalog.pg_opclass AS opc
		INNER JOIN pg_roles AS rol
			ON opc.opcowner = rol.oid
		UNION ALL
		SELECT pro.oid
			, nsp.nspname AS object_schema
			, pro.proname AS object_name
			, rol.rolname AS owner
			, CASE lower(pro.prokind)
				WHEN 'f' THEN
					'FUNCTION'
				WHEN 'p' THEN
					'PROCEDURE'
				WHEN 'a' THEN
					'AGGREGATE FUNCTION'
				WHEN 'w' THEN
					'WINDOW FUNCTION'
				ELSE
					lower(pro.prokind)
			END AS object_type
		FROM pg_catalog.pg_proc AS pro
		INNER JOIN pg_roles AS rol
			ON pro.proowner = rol.oid
		INNER JOIN pg_catalog.pg_namespace nsp
			ON pro.pronamespace = nsp.oid
			WHERE nsp.nspname NOT IN ('pg_catalog', 'information_schema')
		UNION ALL
		SELECT col.oid
			, NULL AS object_schema
			, col.collname AS object_name
			, rol.rolname AS owner
			, 'COLLATION' AS object_type
		FROM pg_catalog.pg_collation AS col
		INNER JOIN pg_roles AS rol
			ON col.collowner = rol.oid
		UNION ALL
		SELECT con.oid
			, NULL AS object_schema
			, con.conname AS object_name
			, rol.rolname AS owner
			, 'CONVERSION' AS object_type
		FROM pg_catalog.pg_conversion AS con
		INNER JOIN pg_roles AS rol
			ON con.conowner = rol.oid
		UNION ALL
		SELECT evt.oid
			, NULL AS object_schema
			, evt.evtname AS object_name
			, rol.rolname AS owner
			, 'EVENT TRIGGER' AS object_type
		FROM pg_catalog.pg_event_trigger AS evt
		INNER JOIN pg_roles AS rol
			ON evt.evtowner = rol.oid
		UNION ALL
		SELECT opf.oid
			, NULL AS object_schema
			, opf.opfname AS object_name
			, rol.rolname AS owner
			, 'OPERATION FAMILY' AS object_type
		FROM pg_catalog.pg_opfamily AS opf
		INNER JOIN pg_roles AS rol
			ON opf.opfowner = rol.oid
		UNION ALL
		SELECT pub.oid
			, NULL AS object_schema
			, pub.pubname AS object_name
			, rol.rolname AS owner
			, 'PUBLICATIONS' AS object_type
		FROM pg_catalog.pg_publication AS pub
		INNER JOIN pg_roles AS rol
			ON pub.pubowner = rol.oid
	)
SELECT dbobjects.oid
	, dbobjects.object_schema
	, dbobjects.owner
	, dbobjects.object_type
	, depend.deptype
	, CASE depend.deptype
		WHEN 'n' THEN
			'DEPENDENCY_NORMAL'
		WHEN 'a' THEN
			'DEPENDENCY_AUTO'
		WHEN 'i' THEN
			'DEPENDENCY_INTERNAL'
		WHEN 'P' THEN
			'DEPENDENCY_PARTITION_PRI'
		WHEN 'S' THEN
			'DEPENDENCY_PARTITION_SEC'
		WHEN 'e' THEN
			'DEPENDENCY_EXTENSION'
		WHEN 'x' THEN
			'DEPENDENCY_EXTENSION'
		WHEN 'p' THEN
			'DEPENDENCY_PIN'
		ELSE
			'NOT DEFINED, SEE DOCUMENTATION'
	END AS dependency_type
FROM dbobjects
LEFT OUTER JOIN pg_catalog.pg_depend AS depend
	ON dbobjects.oid = depend.objid
WHERE object_schema NOT IN ('information_schema', 'pg_catalog')
	AND object_schema NOT LIKE 'pg_toast%'
;

-- Add a comment
COMMENT ON VIEW pg_object_ownership IS 'The view returns all objects, its type, and its ownership in the current database, excluding those in the schema pg_catalog and information_schema';
COMMENT ON VIEW pg_object_ownership IS 'The view returns all objects, its type, and its ownership in the current database, excluding those in the schema pg_catalog and information_schema';
COMMENT ON COLUMN pg_object_ownership.oid IS 'The object identifier of the object.';
COMMENT ON COLUMN pg_object_ownership.object_schema IS 'The schema name where the object is stored in.';
COMMENT ON COLUMN pg_object_ownership.owner IS 'The role name that owns the object.';
COMMENT ON COLUMN pg_object_ownership.object_type IS 'The type of object, for example TABLE, INDEX, etc.';
COMMENT ON COLUMN pg_object_ownership.deptype IS 'The dependency shortcut, see dependency_type for further description.';
COMMENT ON COLUMN pg_object_ownership.dependency_type IS 'The description of the deptype: n = DEPENDENCY_NORMAL, a = DEPENDENCY_AUTO, i = DEPENDENCY_INTERNAL, P = DEPENDENCY_PARTITION_PRI, S = DEPENDENCY_PARTITION_SEC, e = DEPENDENCY_EXTENSION, p = DEPENDENCY_PIN, others: NOT DEFINED, SEE DOCUMENTATION.';

/**
 * The view shows bloat in tables and indexes.
 *
 * @author: "Stefanie Janine Stölting"<stefanie@proopensource.eu>
 * @license: PostgreSQL https://opensource.org/licenses/postgresql
 */
CREATE OR REPLACE VIEW pg_bloat_info AS
WITH constants AS
	(
		SELECT current_setting('block_size')::numeric AS bs
			, 23 AS hdr
			, 4 AS ma
	)
, bloat_info AS
	(
		SELECT ma
			,bs
			,schemaname
			,tablename
			, (datawidth + (hdr + ma - (case when hdr%ma = 0 THEN ma ELSE hdr%ma END)))::numeric AS datahdr
			, (maxfracsum * (nullhdr + ma - (case when nullhdr%ma = 0 THEN ma ELSE nullhdr%ma END))) AS nullhdr2
		FROM
			(
				SELECT schemaname
					, tablename
					, hdr
					, ma
					, bs
					, sum ((1 - null_frac) * avg_width) AS datawidth
					, max (null_frac) AS maxfracsum
					, hdr +
						(
        						SELECT 1 + count(*) / 8
							FROM pg_stats s2
							WHERE null_frac != 0
							AND s2.schemaname = s.schemaname
							AND s2.tablename = s.tablename
						) AS nullhdr
				FROM pg_stats AS s, constants
				GROUP BY 1
					,2
					,3
					,4
					,5
			) AS foo
	)
, table_bloat AS
	(
		SELECT schemaname
			, tablename
			, cc.relpages
			, bs
			, CEIL ((cc.reltuples * ((datahdr + ma - (CASE WHEN datahdr%ma = 0 THEN ma ELSE datahdr%ma END)) + nullhdr2 + 4)) / (bs - 20::float)) AS otta
		FROM bloat_info
		INNER JOIN pg_class cc
			ON cc.relname = bloat_info.tablename
		INNER JOIN pg_namespace nn
			ON cc.relnamespace = nn.oid
			AND nn.nspname = bloat_info.schemaname
			AND nn.nspname NOT IN
				(
					'information_schema',
					'pg_catalog'
				)
	)
, index_bloat AS
	(
		SELECT schemaname
			, tablename
			, bs
			, COALESCE (c2.relname,'?') AS iname
			, COALESCE (c2.reltuples,0) AS ituples
			, COALESCE (c2.relpages,0) AS ipages
			, COALESCE (CEIL ((c2.reltuples * (datahdr - 12)) / (bs - 20::float)), 0) AS iotta -- very rough approximation, assumes all cols
		FROM bloat_info
		INNER JOIN pg_class AS cc
			ON cc.relname = bloat_info.tablename
		INNER JOIN pg_namespace AS nn
			ON cc.relnamespace = nn.oid
			AND nn.nspname = bloat_info.schemaname
			AND nn.nspname NOT IN
				(
					'information_schema',
					'pg_catalog'
				)
		INNER JOIN pg_index AS i
			ON indrelid = cc.oid
		INNER JOIN pg_class AS c2
			ON c2.oid = i.indexrelid
	)
SELECT type
	, schemaname
	, object_name
	, bloat
	, pg_size_pretty(raw_waste) AS waste
FROM
	(
		SELECT 'table' AS type
			, schemaname
			, tablename AS object_name
			, ROUND (CASE WHEN otta = 0 THEN 0.0 ELSE table_bloat.relpages/otta::numeric END, 1) AS bloat
			, CASE WHEN relpages < otta THEN '0' ELSE (bs * (table_bloat.relpages-otta)::bigint)::bigint END AS raw_waste
		FROM table_bloat
		UNION
		SELECT 'index' AS type
			, schemaname
			, tablename || '::' || iname as object_name
			, ROUND (CASE WHEN iotta = 0 OR ipages = 0 THEN 0.0 ELSE ipages / iotta::numeric END, 1) AS bloat
			, CASE WHEN ipages < iotta THEN '0' ELSE (bs*(ipages-iotta))::bigint END AS raw_waste
		FROM index_bloat
	) AS bloat_summary
ORDER BY raw_waste DESC
	, bloat DESC
;

COMMENT ON VIEW pg_bloat_info IS 'The view shows bloat in tables and indexes.';
COMMENT ON COLUMN pg_bloat_info.type IS 'Object type, can be table or index.';
COMMENT ON COLUMN pg_bloat_info.schemaname IS 'The schema where the object is located.';
COMMENT ON COLUMN pg_bloat_info.object_name IS 'The name of the table or index.';
COMMENT ON COLUMN pg_bloat_info.bloat IS 'The estimated bloat ratio.';
COMMENT ON COLUMN pg_bloat_info.waste IS 'The estimated wasted disk spae with unit (kB, MB, GB).';

/**
 * The view shows unused indexes with further information about the table.
 *
 * @author: "Stefanie Janine Stölting"<stefanie@proopensource.eu>
 * @license: PostgreSQL https://opensource.org/licenses/postgresql
 */

CREATE OR REPLACE VIEW pg_unused_indexes AS
SELECT schemaname AS schema_name
	, relname AS table_name
	, indexrelname AS index_name
	, idx_scan
	, pg_size_pretty (pg_table_size ('"' || schemaname || '"."' || relname || '"')) AS table_size
	, pg_size_pretty (pg_total_relation_size ('"' || schemaname || '"."' || relname || '"')) AS table_total_size
	, pg_size_pretty (pg_indexes_size ('"' || schemaname || '"."' || relname || '"')) AS all_indexes_size
	, pg_size_pretty (pg_relation_size (indexrelid)) AS index_size
	, pg_size_pretty (sum (pg_relation_size (indexrelid)) over ()) AS  size_of_all_indexes
FROM pg_stat_all_indexes
WHERE idx_scan = 0
AND schemaname NOT IN
	(
		'information_schema',
		'pg_catalog',
		'pg_toast'
	)
;

COMMENT ON VIEW pg_unused_indexes IS 'The view shows unused indexes with further information about the table.';
COMMENT ON COLUMN pg_unused_indexes.schema_name IS 'The name of the schema.';
COMMENT ON COLUMN pg_unused_indexes.table_name IS 'The name of the table.';
COMMENT ON COLUMN pg_unused_indexes.index_name IS 'The name of the index';
COMMENT ON COLUMN pg_unused_indexes.idx_scan IS 'The number of query executions where this index has been used, is always zero.';
COMMENT ON COLUMN pg_unused_indexes.table_size IS 'The size of the table without indexes.';
COMMENT ON COLUMN pg_unused_indexes.table_total_size IS 'The size of the table including all indexes.';
COMMENT ON COLUMN pg_unused_indexes.all_indexes_size IS 'The size of all indexes in this table.';
COMMENT ON COLUMN pg_unused_indexes.index_size IS 'The size of the index mentioned in index_name.';
COMMENT ON COLUMN pg_unused_indexes.size_of_all_indexes IS 'The size of all indexes over all tables.';

/**
 * Creates a view to get information about bloat in tables.
 * The view requires the extension pgstattuple to be installed.
 *
 * @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(*) pgstattuple_exists
	FROM pg_extension
	WHERE extname = 'pgstattuple'
	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 pg_table_bloat AS
WITH table_list AS
	(
		SELECT t.schemaname AS schema_name
			, t.tablename AS table_name
			, t.tableowner AS table_owner
			, t.schemaname || '.' || t.tablename AS schema_table_name
		FROM pg_catalog.pg_tables AS t
		LEFT OUTER JOIN pg_catalog.pg_partitioned_table AS pt
			ON (t.schemaname || '.' || t.tablename)::regclass::oid = pt.partrelid
		WHERE t.schemaname NOT IN
			(
				'pg_catalog',
				'information_schema'
			)
		AND pt.partrelid IS NULL
	)
SELECT table_list.schema_name
	, table_list.table_name
	, table_list.table_owner
	, st.table_len AS table_length_in_bytes
	, st.tuple_count
	, st.tuple_len AS tuple_length_in_bytes
	, st.tuple_percent
	, st.dead_tuple_count
	, st.dead_tuple_len AS dead_tuple_length_in_bytes
	, st.dead_tuple_percent
	, st.free_space
	, st.free_percent
FROM table_list
CROSS JOIN LATERAL pgstattuple(table_list.schema_table_name) AS st
ORDER BY schema_name , table_name
;
$string$
;
		EXECUTE view_source;

		COMMENT ON VIEW pg_table_bloat IS 'The list of tables and current bloat.';
		COMMENT ON COLUMN pg_table_bloat.schema_name IS 'The name of the schema.';
		COMMENT ON COLUMN pg_table_bloat.table_name IS 'The name of the table.';
		COMMENT ON COLUMN pg_table_bloat.table_owner IS 'The name of the role that owns the table.';
		COMMENT ON COLUMN pg_table_bloat.table_length_in_bytes IS 'The physical table size in bytes.';
		COMMENT ON COLUMN pg_table_bloat.tuple_count IS 'The number of live tuples.';
		COMMENT ON COLUMN pg_table_bloat.tuple_length_in_bytes IS 'The total length of live tuples in bytes.';
		COMMENT ON COLUMN pg_table_bloat.tuple_percent IS 'The percentage of live tuples compared to dead tuples.';
		COMMENT ON COLUMN pg_table_bloat.dead_tuple_count IS 'The number of dead tuples.';
		COMMENT ON COLUMN pg_table_bloat.dead_tuple_length_in_bytes IS 'The total length of dead tuples in bytes.';
		COMMENT ON COLUMN pg_table_bloat.dead_tuple_percent IS 'The percentage of dead tuples compared to live tuples.';
		COMMENT ON COLUMN pg_table_bloat.free_space IS 'The total free space in bytes of this table.';
		COMMENT ON COLUMN pg_table_bloat.free_percent IS 'The percentage of free space of this tuple compared to used space.';

	END IF;

END $$;

/**
 * The view tables with sequential scans, which probably are missing indexes
 *
 * @author: "Stefanie Janine Stölting"<stefanie@proopensource.eu>
 * @license: PostgreSQL https://opensource.org/licenses/postgresql
 */

 DO $$
 DECLARE
 	version_greater_15 BOOLEAN;
 BEGIN
 	SELECT to_number((string_to_array(version(), ' '))[2], '999.99') >= 16 INTO version_greater_15;


 	IF version_greater_15 THEN
 		-- Create the view pg_functions for PostgreSQL 15 or newer
		CREATE OR REPLACE VIEW pg_missing_indexes AS
		SELECT t.schemaname AS schema_name
			, t.relname AS table_name
			, t.seq_scan AS sequential_scans
			, t.last_seq_scan AS last_sequential_scans
		FROM pg_stat_all_tables AS t
		WHERE schemaname NOT IN
			(
				'pg_toast',
				'pg_catalog',
				'information_schema'
			)
		AND t.seq_scan > 0
		;

		COMMENT ON VIEW pg_missing_indexes IS 'The view tables with sequential scans, which probably are missing indexes. The higher the number the more likely an index is might be needed.';
		COMMENT ON COLUMN pg_missing_indexes.schema_name IS 'The name of the schema.';
		COMMENT ON COLUMN pg_missing_indexes.table_name IS 'The name of the table.';
		COMMENT ON COLUMN pg_missing_indexes.sequential_scans IS 'Number of sequential scans on this table.';
		COMMENT ON COLUMN pg_missing_indexes.last_sequential_scans IS 'Last time of a sequential scan on this table.';
	ELSE
		-- Create the view pg_functions for PostgreSQL 15 or newer
		CREATE OR REPLACE VIEW pg_missing_indexes AS
		SELECT t.schemaname AS schema_name
			, t.relname AS table_name
			, t.seq_scan AS sequential_scans
		FROM pg_stat_all_tables AS t
		WHERE schemaname NOT IN
			(
				'pg_toast',
				'pg_catalog',
				'information_schema'
			)
		AND t.seq_scan > 0
		;

		COMMENT ON VIEW pg_missing_indexes IS 'The view tables with sequential scans, which probably are missing indexes. The higher the number the more likely an index is might be needed.';
		COMMENT ON COLUMN pg_missing_indexes.schema_name IS 'The name of the schema.';
		COMMENT ON COLUMN pg_missing_indexes.table_name IS 'The name of the table.';
		COMMENT ON COLUMN pg_missing_indexes.sequential_scans IS 'Number of sequential scans on this table.';
	END IF;
END $$;

/**
 * Creates a view to get information rights on database objects.
 *
 * @author: "Stefanie Janine Stölting"<stefanie@proopensource.eu>
 * @license: PostgreSQL https://opensource.org/licenses/postgresql
 */

CREATE OR REPLACE VIEW pg_role_permissions AS
WITH rtg AS
	(
		SELECT rtg.grantee AS role_name
			, rtg.table_catalog AS database_name
			, rtg.table_schema  AS schema_name
			, rtg.table_name
			, array_agg(rtg.privilege_type) AS privilege
			, rtg.is_grantable
		FROM information_schema.role_table_grants AS rtg
		GROUP BY rtg.grantee
			, rtg.table_catalog
			, rtg.table_schema
			, rtg.table_name
			, rtg.is_grantable
	)
, rtg_res AS
	(
		SELECT rtg.role_name
			, rtg.database_name
			, rtg.schema_name
			, rtg.table_name AS object_name
			, 'TABLE' AS object_type
			, rtg.privilege
			, 'SELECT' = ANY (rtg.privilege) AS "SELECT"
			, 'INSERT' = ANY (rtg.privilege) AS "INSERT"
			, 'UPDATE' = ANY (rtg.privilege) AS "UPDATE"
			, 'DELETE' = ANY (rtg.privilege) AS "DELETE"
			, 'TRUNCATE' = ANY (rtg.privilege) AS "TRUNCATE"
			, 'REFERENCES' = ANY (rtg.privilege) AS "REFERENCES"
			, 'TRIGGER' = ANY (rtg.privilege) AS "TRIGGER"
			, NULL::boolean AS "USAGE"
			, NULL::boolean AS "EXECUTE"
			, rtg.is_grantable = 'YES' AS "GRANTABLE"
		FROM rtg
	)
,rug AS
	(
		SELECT rug.grantee AS role_name
			, rug.udt_catalog AS database_name
			, rug.udt_schema AS schema_name
			, rug.udt_name AS object_name
			, 'USER DEFINED TYPE' AS object_type
			, string_to_array(substring(rug.privilege_type, 6, 5), '.') AS privilege
			, rug.is_grantable
		FROM information_schema.role_udt_grants AS rug
	)
, rug_res AS
	(
		SELECT rug.role_name
			, rug.database_name
			, rug.schema_name
			, rug.object_name
			, rug.object_type
			, rug.privilege
			, NULL::boolean AS "SELECT"
			, NULL::boolean AS "INSERT"
			, NULL::boolean AS "UPDATE"
			, NULL::boolean AS "DELETE"
			, NULL::boolean AS "TRUNCATE"
			, NULL::boolean AS "REFERENCES"
			, NULL::boolean AS "TRIGGER"
			, 'USAGE' = ANY (rug.privilege) AS "USAGE"
			, NULL::boolean AS "EXECUTE"
			, rug.is_grantable = 'YES' AS "GRANTABLE"
		FROM rug
	)
, rrg AS
	(
		SELECT rrg.grantee AS role_name
			, rrg.routine_catalog AS database_name
			, rrg.routine_schema AS schema_name
			, rrg.routine_name AS object_name
			, 'FUNCTION/PROCEDURE' AS object_type
			, string_to_array(rrg.privilege_type, '.') AS privilege
			, rrg.is_grantable
		FROM information_schema.role_routine_grants AS rrg
	)
, rrg_res AS
	(
		SELECT rrg.role_name
			, rrg.database_name
			, rrg.schema_name
			, rrg.object_name
			, rrg.object_type
			, rrg.privilege
			, NULL::boolean AS "SELECT"
			, NULL::boolean AS "INSERT"
			, NULL::boolean AS "UPDATE"
			, NULL::boolean AS "DELETE"
			, NULL::boolean AS "TRUNCATE"
			, NULL::boolean AS "REFERENCES"
			, NULL::boolean AS "TRIGGER"
			, NULL::boolean AS "USAGE"
			, 'EXECUTE' = ANY (rrg.privilege) AS "EXECUTE"
			, rrg.is_grantable = 'YES' AS "GRANTABLE"
		FROM rrg
	)
, rcg AS
	(
		SELECT rcg.grantee AS role_name
			, rcg.table_catalog AS database_name
			, rcg.table_schema AS schema_name
			, rcg.table_name || '.' || rcg.column_name AS object_name
			, 'TABLE COLUMN' AS object_type
			, array_agg(rcg.privilege_type) AS privilege
			, rcg.is_grantable
		FROM information_schema.role_column_grants AS rcg
		GROUP BY rcg.grantee
			, rcg.table_catalog
			, rcg.table_schema
			, rcg.table_name
			, rcg.column_name
			, rcg.is_grantable
	)
, rcg_res AS
	(
		SELECT rcg.role_name
			, rcg.database_name
			, rcg.schema_name
			, rcg.object_name
			, 'TABLE' AS object_type
			, rcg.privilege
			, 'SELECT' = ANY (rcg.privilege) AS "SELECT"
			, 'INSERT' = ANY (rcg.privilege) AS "INSERT"
			, 'UPDATE' = ANY (rcg.privilege) AS "UPDATE"
			, NULL::boolean AS "DELETE"
			, NULL::boolean AS "TRUNCATE"
			, 'REFERENCES' = ANY (rcg.privilege) AS "REFERENCES"
			, NULL::boolean AS "TRIGGER"
			, NULL::boolean AS "USAGE"
			, NULL::boolean AS "EXECUTE"
			, rcg.is_grantable = 'YES' AS "GRANTABLE"
		FROM rcg
	)
, res AS
	(
		SELECT *
		FROM rtg_res
		UNION ALL
		SELECT *
		FROM rug_res
		UNION ALL
		SELECT *
		FROM rrg_res
		UNION ALL
		SELECT *
		FROM rcg_res
	)
SELECT *
FROM res
WHERE res.schema_name NOT IN
	(
		'information_schema',
		'pg_catalog'
	)
;

COMMENT ON VIEW pg_role_permissions IS 'Information about rights of roles on database objects.';
COMMENT ON COLUMN pg_role_permissions.role_name IS 'The name of the role.';
COMMENT ON COLUMN pg_role_permissions.database_name IS 'The name of the database.';
COMMENT ON COLUMN pg_role_permissions.schema_name IS 'The name of the schema.';
COMMENT ON COLUMN pg_role_permissions.object_name IS 'The name of the object.';
COMMENT ON COLUMN pg_role_permissions.object_type IS 'The type of the object, can be one of TABLE, FUNCTION/PROCEDURE, USER DEFINED TYPE, or TABLE COLUMN.';
COMMENT ON COLUMN pg_role_permissions.privilege IS 'All privileges as an array.';
COMMENT ON COLUMN pg_role_permissions."SELECT" IS 'SELECT right, NULL if not supported by the object.';
COMMENT ON COLUMN pg_role_permissions."INSERT" IS 'INSERT right, NULL if not supported by the object.';
COMMENT ON COLUMN pg_role_permissions."UPDATE" IS 'UPDATE right, NULL if not supported by the object.';
COMMENT ON COLUMN pg_role_permissions."DELETE" IS 'DELETE right, NULL if not supported by the object.';
COMMENT ON COLUMN pg_role_permissions."TRUNCATE" IS 'TRUNCATE right, NULL if not supported by the object.';
COMMENT ON COLUMN pg_role_permissions."REFERENCES" IS 'REFERENCES right, NULL if not supported by the object.';
COMMENT ON COLUMN pg_role_permissions."TRIGGER" IS 'TRIGGER right, NULL if not supported by the object.';
COMMENT ON COLUMN pg_role_permissions."USAGE" IS 'USAGE right, NULL if not supported by the object.';
COMMENT ON COLUMN pg_role_permissions."EXECUTE" IS 'EXECUTE right, NULL if not supported by the object.';
COMMENT ON COLUMN pg_role_permissions."GRANTABLE" IS 'GRANTABLE right, role can grant rights on this object.';

/**
 * Role infos based on pg_roles and role_inheritance.
 * NOTE: The function role_inheritance must exist!
 *
 * @author: "Stefanie Janine Stölting"<stefanie@proopensource.eu>
 * @license: PostgreSQL https://opensource.org/licenses/postgresql
 */

DO $$
DECLARE
	function_exists BOOLEAN;
 	version_greater_15 BOOLEAN;
	view_source TEXT;
BEGIN

	SELECT count(*) = 1 AS function_exists
	INTO function_exists
	FROM pg_catalog.pg_proc AS f
	WHERE f.proname = 'role_inheritance'
		AND f.pronargs = 1
	;

	IF function_exists THEN
		-- The view is only created when the function role_inheritance
	 	SELECT to_number((string_to_array(version(), ' '))[2], '999.99') >= 16
		INTO version_greater_15;
	
	 	IF version_greater_15 THEN
	 		-- Create the view for PostgreSQL 16 or newer
			view_source :=
$string$
CREATE OR REPLACE VIEW pg_role_infos AS
SELECT pr.oid
	, pr.rolname AS rolename
	, pr.rolsuper
	, pr.rolcreaterole
	, pr.rolcreatedb
	, pr.rolreplication
	, pr.rolcanlogin
	, pr.rolvaliduntil
	, pr.rolconfig
	, ri.steps
	, ri.role_path
	, ri.inherit_option
FROM pg_catalog.pg_roles AS pr
, role_inheritance(pr.rolname) AS ri
;
$string$
;
			EXECUTE view_source;

			COMMENT ON COLUMN pg_role_infos.inherit_option IS 'True if the member can SET ROLE to the granted role.';
		ELSE
			-- Create the view for PostgreSQL older than 16
			view_source :=
$string$
CREATE OR REPLACE VIEW pg_role_infos AS
SELECT pr.oid
	, pr.rolname AS rolename
	, pr.rolsuper
	, pr.rolcreaterole
	, pr.rolcreatedb
	, pr.rolreplication
	, pr.rolcanlogin
	, pr.rolvaliduntil
	, pr.rolconfig
	, ri.steps
	, ri.role_path
FROM pg_catalog.pg_roles AS pr
, role_inheritance(pr.rolname) AS ri
;
$string$
;
			EXECUTE view_source;
		END IF;

		COMMENT ON VIEW pg_role_infos IS 'Role infos based on pg_roles and role_inheritance.';
		COMMENT ON COLUMN pg_role_infos.oid IS 'The object identifier of the role.';
		COMMENT ON COLUMN pg_role_infos.rolename IS 'The name of the role.';
		COMMENT ON COLUMN pg_role_infos.rolsuper IS 'The role has superuser privileges.';
		COMMENT ON COLUMN pg_role_infos.rolcreaterole IS 'The role can create more roles.';
		COMMENT ON COLUMN pg_role_infos.rolcreatedb IS 'The role can create databases.';
		COMMENT ON COLUMN pg_role_infos.rolreplication IS 'The role is a replication role. A replication role can initiate replication connections and create and drop replication slots.';
		COMMENT ON COLUMN pg_role_infos.rolcanlogin IS 'The role can log in. Usually group roles cannot login.';
		COMMENT ON COLUMN pg_role_infos.rolvaliduntil IS 'The password expiry time, NULL if no expiration.';
		COMMENT ON COLUMN pg_role_infos.rolconfig IS 'The role specific defaults for run-time configuration variables, for example the search path.';
		COMMENT ON COLUMN pg_role_infos.steps IS 'The order im which the inheritance is going.';
		COMMENT ON COLUMN pg_role_infos.role_path IS 'The path of the inheritance.';

	END IF;

END $$;

/**
 * Top ten queries with statistics about
 * - shared buffer read/write/dirty
 * 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_query_times AS
SELECT queryid
	, query
	, round (total_exec_time::NUMERIC, 2) AS total_time
	, calls
	, round (mean_exec_time::NUMERIC, 2) AS mean_time
	, round
		(
			(
				100 * total_exec_time /
				sum(total_exec_time::numeric)
				OVER ()
			)::NUMERIC, 2
		) AS percentage_of_total_time
	, shared_blks_hit AS shared_buffer_hit
	, shared_blks_read AS shared_buffer_read
	, shared_blks_written AS shared_buffer_written
	, shared_blks_dirtied AS shared_buffer_dirty
FROM pg_stat_statements
ORDER BY total_time DESC
LIMIT 10
;
$string$
;
		EXECUTE view_source;

		COMMENT ON VIEW statistics_top_ten_query_times IS 'Top ten queries in execution times.';
		COMMENT ON COLUMN statistics_top_ten_query_times.queryid IS 'Query identifier, can be used to join the view with pg_stat_statements to get more information';
		COMMENT ON COLUMN statistics_top_ten_query_times.query IS 'This is the SQL source of the query.';
		COMMENT ON COLUMN statistics_top_ten_query_times.total_time IS 'This is the total execution time of the query in milliseconds.';
		COMMENT ON COLUMN statistics_top_ten_query_times.mean_time IS 'This is the average execution time of the query in milliseconds.';
		COMMENT ON COLUMN statistics_top_ten_query_times.calls IS 'This is many times a statement has been executed.';
		COMMENT ON COLUMN statistics_top_ten_query_times.percentage_of_total_time IS 'Percentage of execution time of all executed queries.';
		COMMENT ON COLUMN statistics_top_ten_query_times.shared_buffer_hit IS 'This is the count of times disk blocks were found already cached in memory (no I/O was needed).';
		COMMENT ON COLUMN statistics_top_ten_query_times.shared_buffer_read IS 'This is the read is the count of times disk blocks had to be read into memory, which indicates actual I/O operations. High values in shared_blks_read suggest that these queries are the most I/O intensive, which can be a starting point for performance optimization.';
		COMMENT ON COLUMN statistics_top_ten_query_times.shared_buffer_written IS 'This is the number of shared blocks written into shared buffer and written to disk.';
		COMMENT ON COLUMN statistics_top_ten_query_times.shared_buffer_dirty IS 'This is the number of shared blocks "dirtied" by the query. Dirteid means the number of blocks where at least one tuple got modified and hat ot be written to disk.';

	END IF;

END $$;

/**
 * Top ten queries with high i/o activity
 * 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;
	version_greater_17 BOOLEAN;
BEGIN

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

	IF pg_extension_installed THEN
		SELECT to_number((string_to_array(version(), ' '))[2], '999.99') >= 17 INTO version_greater_17;

	  IF version_greater_17 THEN
			-- The view is only created when pgstattuple is installed
			CREATE OR REPLACE VIEW statistics_top_ten_query_average_time_in_seconds AS
			SELECT s.userid::regrole AS rolename
				, d.datname AS database_name
				, s.query
				, s.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 (s.shared_blk_read_time + s.shared_blk_write_time) DESC
			LIMIT 10
			;
		ELSE
			-- The view is only created when pgstattuple is installed
			CREATE OR REPLACE VIEW statistics_top_ten_query_average_time_in_seconds AS
			SELECT s.userid::regrole AS rolename
				, d.datname AS database_name
				, s.query
				, s.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 s.mean_exec_time DESC
			LIMIT 10
			;
		END IF;

		COMMENT ON VIEW statistics_top_ten_query_average_time_in_seconds IS 'Top ten queries with high i/o activity with the average execution time in seconds.';
		COMMENT ON COLUMN statistics_top_ten_query_average_time_in_seconds.rolename IS 'The role/user who executed the query.';
		COMMENT ON COLUMN statistics_top_ten_query_average_time_in_seconds.database_name IS 'The database name in which the query has been executed.';
		COMMENT ON COLUMN statistics_top_ten_query_average_time_in_seconds.query IS 'This is the SQL source of the query.';
		COMMENT ON COLUMN statistics_top_ten_query_average_time_in_seconds.mean_time_seconds IS 'The average execution time of the query in seconds';

	END IF;

END $$;

/**
 * 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 $$;

/**
 * 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_memory_usage_queries AS
SELECT s.userid::regrole AS rolename
	, d.datname AS database_name
	, s.query
	, (s.shared_blks_hit + s.shared_blks_dirtied) AS memory_hit_dirty
FROM pg_stat_statements AS s
INNER JOIN pg_catalog.pg_database AS d
	ON s.dbid = d.oid
ORDER BY (s.shared_blks_hit + s.shared_blks_dirtied) DESC
LIMIT 10
;
$string$
;
		EXECUTE view_source;

		COMMENT ON VIEW statistics_top_ten_memory_usage_queries IS 'Top ten queries with high memory usage.';
		COMMENT ON COLUMN statistics_top_ten_memory_usage_queries.rolename IS 'The role/user who executed the query.';
		COMMENT ON COLUMN statistics_top_ten_memory_usage_queries.database_name IS 'The database name in which the query has been executed.';
		COMMENT ON COLUMN statistics_top_ten_memory_usage_queries.query IS 'This is the SQL source of the query.';
		COMMENT ON COLUMN statistics_top_ten_memory_usage_queries.memory_hit_dirty IS 'This the sum of shared_blks_hit + s.shared_blks_dirtied, the calculated memory usage in byte.';

	END IF;

END $$;

/**
 * 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 $$;

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

		COMMENT ON VIEW statistics_top_ten_rows_returned_queries IS 'Top ten queries by rows returned.';
		COMMENT ON COLUMN statistics_top_ten_rows_returned_queries.rolename IS 'The role/user who executed the query.';
		COMMENT ON COLUMN statistics_top_ten_rows_returned_queries.database_name IS 'The database name in which the query has been executed.';
		COMMENT ON COLUMN statistics_top_ten_rows_returned_queries.rows IS 'This is the count of the effected rows by this query.';
		COMMENT ON COLUMN statistics_top_ten_rows_returned_queries.query IS 'This is the SQL source of the query.';

	END IF;

END $$;

/**
 * Top ten queries by shared block hits
 * 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_shared_block_hits_queries AS
SELECT s.userid::regrole AS rolename
	, d.datname AS database_name
	, s.shared_blks_hit AS shared_buffer_hit
	, s.shared_blks_read AS shared_buffer_read
	, s.query
FROM pg_stat_statements AS s
INNER JOIN pg_catalog.pg_database AS d
	ON s.dbid = d.oid
ORDER BY s.shared_blks_hit DESC
LIMIT 10
;
$string$
;
		EXECUTE view_source;

		COMMENT ON VIEW statistics_top_ten_shared_block_hits_queries IS 'Top ten queries by shared block hits.';
		COMMENT ON COLUMN statistics_top_ten_shared_block_hits_queries.rolename IS 'The role/user who executed the query.';
		COMMENT ON COLUMN statistics_top_ten_shared_block_hits_queries.database_name IS 'The database name in which the query has been executed.';
		COMMENT ON COLUMN statistics_top_ten_shared_block_hits_queries.shared_buffer_hit IS 'This is the count of times disk blocks were found already cached in memory (no I/O was needed).';
		COMMENT ON COLUMN statistics_top_ten_shared_block_hits_queries.shared_buffer_read IS 'This is the read is the count of times disk blocks had to be read into memory, which indicates actual I/O operations. High values in shared_blks_read suggest that these queries are the most I/O intensive, which can be a starting point for performance optimization.';
		COMMENT ON COLUMN statistics_top_ten_shared_block_hits_queries.query IS 'This is the SQL source of the query.';

	END IF;

END $$;

/**
 * Top ten queries by block writes
 * 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_block_writes_queries AS
SELECT s.userid::regrole AS rolename
	, d.datname AS database_name
	, s.shared_blks_written AS shared_buffer_written
	, s.shared_blks_read AS shared_buffer_read
	, s.query
FROM pg_stat_statements AS s
INNER JOIN pg_catalog.pg_database AS d
	ON s.dbid = d.oid
ORDER BY s.shared_blks_written DESC
LIMIT 10
;
$string$
;
		EXECUTE view_source;

		COMMENT ON VIEW statistics_top_ten_block_writes_queries IS 'Top ten queries by block writes.';
		COMMENT ON COLUMN statistics_top_ten_block_writes_queries.rolename IS 'The role/user who executed the query.';
		COMMENT ON COLUMN statistics_top_ten_block_writes_queries.database_name IS 'The database name in which the query has been executed.';
		COMMENT ON COLUMN statistics_top_ten_block_writes_queries.shared_buffer_written IS 'This is the number of shared blocks written into shared buffer and written to disk.';
		COMMENT ON COLUMN statistics_top_ten_block_writes_queries.shared_buffer_read IS 'This is the read is the count of times disk blocks had to be read into memory, which indicates actual I/O operations. High values in shared_blks_read suggest that these queries are the most I/O intensive, which can be a starting point for performance optimization.';
		COMMENT ON COLUMN statistics_top_ten_block_writes_queries.query IS 'This is the SQL source of the query.';

	END IF;

END $$;

/**
 * Top ten queries by WAL records generated, works on primary only
 * 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_wal_records_generated_queries AS
SELECT s.userid::regrole AS rolename
	, d.datname AS database_name
	, s.wal_records
	, s.query
FROM pg_stat_statements AS s
INNER JOIN pg_catalog.pg_database AS d
	ON s.dbid = d.oid
ORDER BY s.wal_records DESC
LIMIT 10
;
$string$
;
		EXECUTE view_source;

		COMMENT ON VIEW statistics_top_ten_wal_records_generated_queries IS 'Top ten queries by WAL records generated, works on primary only.';
		COMMENT ON COLUMN statistics_top_ten_wal_records_generated_queries.rolename IS 'The role/user who executed the query.';
		COMMENT ON COLUMN statistics_top_ten_wal_records_generated_queries.database_name IS 'The database name in which the query has been executed.';
		COMMENT ON COLUMN statistics_top_ten_wal_records_generated_queries.wal_records IS 'This is the total number of WAL records generated by the query.';
		COMMENT ON COLUMN statistics_top_ten_wal_records_generated_queries.query IS 'This is the SQL source of the query.';

	END IF;

END $$;

/**
 * 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 $$;

/**
 * View to monitor write ahead logs (WAL).
 *
 * @author: "Stefanie Janine Stölting"<stefanie@proopensource.eu>
 * @license: PostgreSQL https://opensource.org/licenses/postgresql
 */

CREATE OR REPLACE VIEW monitoring_wal AS
SELECT count (*) FILTER
		(
			WHERE pg_ls_dir~'done'
		) AS wals_done
	, count(*) FILTER
		(
			WHERE pg_ls_dir~'ready'
		) AS wals_ready
from pg_ls_dir('pg_wal/archive_status')
;

COMMENT ON VIEW monitoring_wal IS 'View to monitor write ahead logs (WAL).';
COMMENT ON COLUMN monitoring_wal.wals_done IS 'Number of WAL files, that have been done.';
COMMENT ON COLUMN monitoring_wal.wals_ready IS 'Number of WAL files, that have to be done.';

/**
 * View to monitor write ahead log (WAL) archiving
 *
 * @author: "Stefanie Janine Stölting"<stefanie@proopensource.eu>
 * @license: PostgreSQL https://opensource.org/licenses/postgresql
 */

CREATE OR REPLACE VIEW monitoring_wal_archiving AS
SELECT psa.archived_count
	, psa.failed_count
	, psa.last_archived_time
	, psa.last_archived_wal
	, psa.last_failed_time
	, psa.last_failed_wal
	, psa.stats_reset
FROM pg_catalog.pg_stat_archiver AS psa
;

COMMENT ON VIEW monitoring_wal_archiving IS 'View to monitor write ahead log (WAL) archiving.';
COMMENT ON COLUMN monitoring_wal_archiving.archived_count IS 'The number of WAL files that have been successfully archived.';
COMMENT ON COLUMN monitoring_wal_archiving.failed_count IS 'The number of failed attempts for archiving WAL files.';
COMMENT ON COLUMN monitoring_wal_archiving.last_archived_time IS 'Last time WAL files have been archived.';
COMMENT ON COLUMN monitoring_wal_archiving.last_archived_wal IS 'The name of the WAL file most recently successfully archived.';
COMMENT ON COLUMN monitoring_wal_archiving.last_failed_time IS 'The last time WAL files failed to be archived.';
COMMENT ON COLUMN monitoring_wal_archiving.last_failed_wal IS 'The name of the WAL file that most recently failed to get archived.';
COMMENT ON COLUMN monitoring_wal_archiving.stats_reset IS 'The last time the WAL statistics have been reset.';

/**
 * Monitoring active database connections and their lock state
 *
 * @author: "Stefanie Janine Stölting"<stefanie@proopensource.eu>
 * @license: PostgreSQL https://opensource.org/licenses/postgresql
 */

CREATE OR REPLACE VIEW monitoring_active_locks AS
SELECT DISTINCT l.pid
	, a.state
	, a.datname AS database_name
	, a.usename AS rolename
	, a.application_name
	, a.client_addr AS client_address
	, a.query_start
	, age (now(), a.query_start) AS query_age
	, a.wait_event_type
	, a.wait_event
	, l.locktype
	, l.mode
	, a.query
FROM pg_catalog.pg_stat_activity AS a
INNER JOIN pg_catalog.pg_locks AS l
	USING(pid)
;

COMMENT ON VIEW monitoring_active_locks IS 'Monitoring active database connections and their lock state';
COMMENT ON COLUMN monitoring_active_locks.pid IS 'The process id of the backend, needed in case a connection should be terminated with the function pg-terminate_backend(pid). For details see [SERVER SIGNALING FUNCTIONS](https://www.postgresql.org/docs/current/functions-admin.html#FUNCTIONS-ADMIN-SIGNAL).';
COMMENT ON COLUMN monitoring_active_locks.state IS 'The current state of the connection.';
COMMENT ON COLUMN monitoring_active_locks.database_name IS 'The database name in which the queries have been executed.';
COMMENT ON COLUMN monitoring_active_locks.rolename IS 'The role/user who executed the queries.';
COMMENT ON COLUMN monitoring_active_locks.application_name IS 'The name of the application, could be empty if not set by a client.';
COMMENT ON COLUMN monitoring_active_locks.client_address IS 'The clients IP address.';
COMMENT ON COLUMN monitoring_active_locks.query_start IS 'The timestamp of the start of the query.';
COMMENT ON COLUMN monitoring_active_locks.query_age IS 'How long the query is already running.';
COMMENT ON COLUMN monitoring_active_locks.wait_event_type IS 'The type of event for which the backend is waiting, if any; otherwise NULL, for details see [WAIT EVENT TABLE](https://www.postgresql.org/docs/current/monitoring-monitoring_html#WAIT-EVENT-TABLE).';
COMMENT ON COLUMN monitoring_active_locks.wait_event IS 'Wait event name if backend is currently waiting, otherwise NULL. For details see [WAIT EVENT ACTIVITY TABLE](https://www.postgresql.org/docs/current/monitoring-monitoring_html#WAIT-EVENT-ACTIVITY-TABLE) and [WAIT EVENT TIMEOUT TABLE](https://www.postgresql.org/docs/current/monitoring-monitoring_html#WAIT-EVENT-TIMEOUT-TABLE)';
COMMENT ON COLUMN monitoring_active_locks.locktype IS 'The type of the lockable object: relation, extend, frozenid, page, tuple, transactionid, virtualxid, spectoken, object, userlock, advisory, or applytransaction, see [Wait Events of Type Lock](https://www.postgresql.org/docs/current/monitoring-stats.html#WAIT-EVENT-LOCK-TABLE).';
COMMENT ON COLUMN monitoring_active_locks.mode IS 'The name of the lock mode held or desired by this process. For details see [TABLE LEVEL LOCKS](https://www.postgresql.org/docs/current/explicit-locking.html#LOCKING-TABLES) and [SERIALIZABLE ISOLATION LEVEL](https://www.postgresql.org/docs/current/transaction-iso.html#XACT-SERIALIZABLE).';COMMENT ON COLUMN monitoring_active_locks.query IS 'This is the SQL source of the query.';

/**
 * Replication monitoring of primaries and followers.
 * Columns with names starting with standby_ are important to monitor followers.
 * Monitoring itself is done on the current primary.
 *
 * @author: "Stefanie Janine Stölting"<stefanie@proopensource.eu>
 * @license: PostgreSQL https://opensource.org/licenses/postgresql
 */

CREATE OR REPLACE VIEW monitoring_replication AS
SELECT r.pid
	, r.usesysid AS oid_user
	, r.usename AS user_name
	, r.application_name
	, r.client_addr
	, r.client_hostname
	, r.client_port
	, r.backend_start
	, r.backend_xmin
	, r.state
	, r.sent_lsn AS last_wal_sent
	, r.write_lsn AS standby_lastwal_written
	, r.replay_lsn AS standby_last_wal_replayed
	, r.flush_lsn AS standby_last_wal_flushed
	, r.write_lag AS standby_wal_write_lag
	, r.flush_lag AS standby_flush_lag
	, r.replay_lag  AS standby_wal_replay_lag
	, r.sync_priority AS standby_sync_priority
	, r.sync_state AS standby_sync_state
	, r.reply_time AS last_replay_message_from_standby
FROM pg_catalog.pg_stat_replication AS r
;

COMMENT ON VIEW monitoring_replication IS 'Replication monitoring of primaries and followers. Columns with names starting with standby_ are important to monitor followers. Monitoring itself is done on the current primary.';
COMMENT ON COLUMN monitoring_replication.pid IS 'The process id of the WAL sender process.';
COMMENT ON COLUMN monitoring_replication.oid_user IS 'The OID of the user logged into this WAL sender process.';
COMMENT ON COLUMN monitoring_replication.user_name IS 'The name of user the logged into this WAL sender process.';
COMMENT ON COLUMN monitoring_replication.application_name IS 'The name of the application that is connected to this WAL sender.';
COMMENT ON COLUMN monitoring_replication.client_addr IS 'The IP address of the client connected to this WAL sender. If this field is null, it indicates that the client is connected via a Unix socket on the server machine.';
COMMENT ON COLUMN monitoring_replication.client_hostname IS 'The host name of the connected client, as reported by a reverse DNS lookup of client_addr. This field will only be non-null for IP connections, and only when log_hostname is enabled.';
COMMENT ON COLUMN monitoring_replication.client_port IS 'The TCP port number that the client is using for communication with this WAL sender, or -1 if a Unix socket is used.';
COMMENT ON COLUMN monitoring_replication.backend_start IS 'The time when this process was started, i.e., when the client connected to this WAL sender.';
COMMENT ON COLUMN monitoring_replication.backend_xmin IS 'This standby''s xmin horizon reported by [hot_standby_feedback](https://www.postgresql.org/docs/current/runtime-config-replication.html#GUC-HOT-STANDBY-FEEDBACK).';
COMMENT ON COLUMN monitoring_replication.state IS 'The current WAL sender state. Possible values are: startup: This WAL sender is starting up.  catchup: This WAL sender''s connected standby is catching up with the primary. streaming: This WAL sender is streaming changes after its connected standby server has caught up with the primary. backup: This WAL sender is sending a backup. stopping: This WAL sender is stopping.';
COMMENT ON COLUMN monitoring_replication.last_wal_sent IS 'The last write-ahead log location sent on this connection.';
COMMENT ON COLUMN monitoring_replication.standby_last_wal_replayed IS 'The last write-ahead log location replayed into the database on this standby server';
COMMENT ON COLUMN monitoring_replication.standby_wal_replay_lag IS 'The last write-ahead log location replayed into the database on this standby server.';
COMMENT ON COLUMN monitoring_replication.standby_sync_priority IS 'The priority of this standby server for being chosen as the synchronous standby in a priority-based synchronous replication. This has no effect in a quorum-based synchronous replication.';
COMMENT ON COLUMN monitoring_replication.standby_sync_state IS 'Synchronous state of this standby server. Possible values are: async: This standby server is asynchronous. potential: This standby server is now asynchronous, but can potentially become synchronous if one of current synchronous ones fails. sync: This standby server is synchronous. quorum: This standby server is considered as a candidate for quorum standbys.';
COMMENT ON COLUMN monitoring_replication.last_replay_message_from_standby IS 'The send time of last reply message received from this standby server';

/**
 * This view returns numbers of conflicts happend on standby servers, AKA followers.
 * Therefore the qeury has to run against the followers.
 * The conflicts are shown per database.
 *
 * @author: "Stefanie Janine Stölting"<stefanie@proopensource.eu>
 * @license: PostgreSQL https://opensource.org/licenses/postgresql
 */

 DO $$
 DECLARE
  version_greater_15 BOOLEAN;
 BEGIN
  SELECT to_number((string_to_array(version(), ' '))[2], '999.99') >= 16 INTO version_greater_15;

  IF version_greater_15 THEN
		CREATE OR REPLACE VIEW monitoring_database_conflicts AS
		SELECT c.datid AS database_id
			, c.datname AS database_name
			, c.confl_tablespace AS conflicts_tablespace
			, c.confl_lock AS conflicts_locks
			, c.confl_snapshot AS conflichts_snapshot
			, c.confl_bufferpin AS conflichts_pinned_buffers
			, c.confl_deadlock AS conflicts_deadlocks
			, c.confl_active_logicalslot AS conflicts_logical_replication_slots
		FROM pg_catalog.pg_stat_database_conflicts AS c
		;

		COMMENT ON VIEW monitoring_database_conflicts IS 'This view returns numbers of conflicts happend on standby servers, AKA followers. Therefore the qeury has to run against the followers. The conflicts are shown per database.';
		COMMENT ON COLUMN monitoring_database_conflicts.database_id IS 'The OID of this database.';
		COMMENT ON COLUMN monitoring_database_conflicts.database_name IS 'The name of this database.';
		COMMENT ON COLUMN monitoring_database_conflicts.conflicts_tablespace IS 'The number of queries in this database that have been canceled due to dropped tablespaces.';
		COMMENT ON COLUMN monitoring_database_conflicts.conflicts_locks IS 'The number of queries in this database that have been canceled due to lock timeouts.';
		COMMENT ON COLUMN monitoring_database_conflicts.conflichts_snapshot IS 'The number of queries in this database that have been canceled due to old snapshots.';
		COMMENT ON COLUMN monitoring_database_conflicts.conflichts_pinned_buffers IS 'The number of queries in this database that have been canceled due to pinned buffers.';
		COMMENT ON COLUMN monitoring_database_conflicts.conflicts_deadlocks IS 'The number of queries in this database that have been canceled due to deadlocks.';
		COMMENT ON COLUMN monitoring_database_conflicts.conflicts_logical_replication_slots IS 'The number of uses of logical slots in this database that have been canceled due to old snapshots or too low a wal_level on the primary server.';
	ELSE
	CREATE OR REPLACE VIEW monitoring_database_conflicts AS
	SELECT c.datid AS database_id
		, c.datname AS database_name
		, c.confl_tablespace AS conflicts_tablespace
		, c.confl_lock AS conflicts_locks
		, c.confl_snapshot AS conflichts_snapshot
		, c.confl_bufferpin AS conflichts_pinned_buffers
		, c.confl_deadlock AS conflicts_deadlocks
	FROM pg_catalog.pg_stat_database_conflicts AS c
	;

	COMMENT ON VIEW monitoring_database_conflicts IS 'This view returns numbers of conflicts happend on standby servers, AKA followers. Therefore the qeury has to run against the followers. The conflicts are shown per database.';
	COMMENT ON COLUMN monitoring_database_conflicts.database_id IS 'The OID of this database.';
	COMMENT ON COLUMN monitoring_database_conflicts.database_name IS 'The name of this database.';
	COMMENT ON COLUMN monitoring_database_conflicts.conflicts_tablespace IS 'The number of queries in this database that have been canceled due to dropped tablespaces.';
	COMMENT ON COLUMN monitoring_database_conflicts.conflicts_locks IS 'The number of queries in this database that have been canceled due to lock timeouts.';
	COMMENT ON COLUMN monitoring_database_conflicts.conflichts_snapshot IS 'The number of queries in this database that have been canceled due to old snapshots.';
	COMMENT ON COLUMN monitoring_database_conflicts.conflichts_pinned_buffers IS 'The number of queries in this database that have been canceled due to pinned buffers.';
	COMMENT ON COLUMN monitoring_database_conflicts.conflicts_deadlocks IS 'The number of queries in this database that have been canceled due to deadlocks.';
	END IF;
END $$;

/**
 * Retuns the current status about blocked user and query information
 *
 * @author: "Stefanie Janine Stölting"<stefanie@proopensource.eu>
 * @license: PostgreSQL https://opensource.org/licenses/postgresql
 */

CREATE OR REPLACE VIEW monitoring_blocked_and_blocking_activity AS
SELECT blocked_locks.pid AS blocked_pid
	, blocking_locks.database AS database_name
	, blocked_activity.usename AS blocked_rolename
	, blocking_locks.pid AS blocking_pid
	, blocking_activity.usename AS blocking_rolename
	, blocked_activity.query AS blocked_statement
	, blocking_activity.query AS current_statement_in_blocking_procblocked_statementess
	, age(now(), blocked_activity.query_start) AS blocked_query_age
	, age(now(), blocking_activity.query_start) AS blocking_query_age
FROM pg_catalog.pg_locks AS blocked_locks
INNER JOIN pg_catalog.pg_stat_activity AS blocked_activity
	ON blocked_activity.pid = blocked_locks.pid
INNER JOIN pg_catalog.pg_locks AS blocking_locks
	ON blocking_locks.locktype = blocked_locks.locktype
	AND blocking_locks.database IS NOT DISTINCT FROM blocked_locks.database
	AND blocking_locks.relation IS NOT DISTINCT FROM blocked_locks.relation
	AND blocking_locks.page IS NOT DISTINCT FROM blocked_locks.page
	AND blocking_locks.tuple IS NOT DISTINCT FROM blocked_locks.tuple
	AND blocking_locks.virtualxid IS NOT DISTINCT FROM blocked_locks.virtualxid
	AND blocking_locks.transactionid IS NOT DISTINCT FROM blocked_locks.transactionid
	AND blocking_locks.classid IS NOT DISTINCT FROM blocked_locks.classid
	AND blocking_locks.objid IS NOT DISTINCT FROM blocked_locks.objid
	AND blocking_locks.objsubid IS NOT DISTINCT FROM blocked_locks.objsubid
	AND blocking_locks.pid != blocked_locks.pid
INNER JOIN pg_catalog.pg_stat_activity AS blocking_activity
	ON blocking_activity.pid = blocking_locks.pid
WHERE NOT blocked_locks.GRANTED
;

COMMENT ON VIEW monitoring_blocked_and_blocking_activity IS 'Retuns the current status about blocked user and query information.';
COMMENT ON COLUMN monitoring_blocked_and_blocking_activity.blocked_pid IS 'The connection identifier, that is blocked.';
COMMENT ON COLUMN monitoring_blocked_and_blocking_activity.database_name IS 'The database name in which the query has been executed.';
COMMENT ON COLUMN monitoring_blocked_and_blocking_activity.blocked_rolename IS 'The role/user who is blocked.';
COMMENT ON COLUMN monitoring_blocked_and_blocking_activity.blocking_pid IS 'The connection identifier, that is blocking.';
COMMENT ON COLUMN monitoring_blocked_and_blocking_activity.blocking_rolename IS 'The role/user who is blocking.';
COMMENT ON COLUMN monitoring_blocked_and_blocking_activity.blocked_statement IS 'This is the SQL source of the query that is blocked.';
COMMENT ON COLUMN monitoring_blocked_and_blocking_activity.current_statement_in_blocking_procblocked_statementess IS 'This is the SQL source of the query that is currently blocking.';
COMMENT ON COLUMN monitoring_blocked_and_blocking_activity.blocked_query_age IS 'That is the duration, that the query is blocked.';
COMMENT ON COLUMN monitoring_blocked_and_blocking_activity.blocking_query_age IS 'That is the duration, that the query is blocking.';

/**
 * View to monitor a follower WAL status
 *
 * NOTE: This view is only working on a follower node, on a primary it will
 * throw an error
 *
 * @author: "Stefanie Janine Stölting"<stefanie@proopensource.eu>
 * @license: PostgreSQL https://opensource.org/licenses/postgresql
 */

CREATE OR REPLACE VIEW monitoring_follower_wal_status AS
SELECT statement_timestamp () AS timestamp_execution
	, pg_read_file('/etc/hostname') AS hostname
	, pg_is_in_recovery () AS in_recovery
	, pg_is_wal_replay_paused () AS wal_replay_paused
	, pg_last_wal_receive_lsn () AS last_wal_receive_lsn
	, pg_last_wal_replay_lsn () AS last_wal_replay_lsn
	, pg_last_xact_replay_timestamp () AS last_xact_replay_timestamp
;

COMMENT ON VIEW monitoring_follower_wal_status IS 'View to monitor a follower WAL status.';
COMMENT ON COLUMN monitoring_follower_wal_status.timestamp_execution IS 'Timestamp at exection of the statement.';
COMMENT ON COLUMN monitoring_follower_wal_status.hostname IS 'Does return the hostname on Linux servers.';
COMMENT ON COLUMN monitoring_follower_wal_status.in_recovery IS 'Whether the follower is in recovery state, or not.';
COMMENT ON COLUMN monitoring_follower_wal_status.wal_replay_paused IS 'Whether the WAL replay on the server has been paused.';
COMMENT ON COLUMN monitoring_follower_wal_status.last_wal_receive_lsn IS 'The last WAL segment, that has been received, type is [pg_lsn](https://www.postgresql.org/docs/current/datatype-pg-lsn.html).';
COMMENT ON COLUMN monitoring_follower_wal_status.last_wal_replay_lsn IS 'The last WAL segment, that has been replayed, type is [pg_lsn](https://www.postgresql.org/docs/current/datatype-pg-lsn.html).';
COMMENT ON COLUMN monitoring_follower_wal_status.last_xact_replay_timestamp IS 'Returns the last timestamp of a WAL segment replayed during recovery.';

/**
 * This view is monitoring active vacuum progress
 *
 * @author: "Stefanie Janine Stölting"<stefanie@proopensource.eu>
 * @license: PostgreSQL https://opensource.org/licenses/postgresql
 */

CREATE OR REPLACE VIEW monitoring_vacuum AS
SELECT spv.datname AS database_name
	, c.relnamespace::RegClass AS schema_name
	, spv.relid::RegClass AS table_name
	, spv.pid process_id
	,CASE phase
		WHEN 'scanning heap' THEN
				CASE
					WHEN spv.heap_blks_total > 0 THEN
						round(spv.heap_blks_scanned / spv.heap_blks_total, 1)
					ELSE
						0::float
					END
		WHEN 'vacuuming heap' THEN
			CASE WHEN spv.heap_blks_total > 0 THEN
				round(spv.heap_blks_vacuumed / spv.heap_blks_total, 1)
			ELSE
				0::float
			END
		ELSE
			NULL::float
	END AS progress
FROM pg_stat_progress_vacuum AS spv
INNER JOIN pg_class AS c
	ON spv.relid = c.oid
;

COMMENT ON VIEW monitoring_vacuum IS 'This view returns the current progress of active vacuum calls.';
COMMENT ON COLUMN monitoring_vacuum.database_name IS 'The name of the database.';
COMMENT ON COLUMN monitoring_vacuum.schema_name IS 'The name of the schema.';
COMMENT ON COLUMN monitoring_vacuum.table_name IS 'The name of the table.';
COMMENT ON COLUMN monitoring_vacuum.process_id IS 'The backend process id (pid).';
COMMENT ON COLUMN monitoring_vacuum.progress IS 'Current state of the vacuum process.';