SET client_min_messages TO warning;
SET log_min_messages    TO warning;

/*** files with test statements ***/

SELECT 'Test starting: function_is_date' AS next_test;
/**
 * Test for function is_date
 *
 * Every test does raise division by zero if it failes
 */
BEGIN;

-- Test if the function exists
WITH test AS
	(
		SELECT COUNT(*) AS exist
		FROM pg_catalog.pg_proc
		WHERE proname = 'is_date'
	)
SELECT 2 / test.exist = 1 AS res
FROM test
;

-- Test with date in default format
WITH test AS
	(
		SELECT is_date('2018-01-01') AS isdate
			, 0 AS zero
	)
SELECT
	CASE
		WHEN isdate THEN
			TRUE
		ELSE
			(1 / zero)::BOOLEAN
	END AS res
FROM test
;

-- Test if all implementations exists
WITH test AS
	(
		SELECT COUNT(*) AS exist
		FROM pg_catalog.pg_proc
		WHERE proname = 'is_date'
	)
SELECT test.exist = 2 AS res
FROM test
;

-- Test with wrong date in default format
WITH test AS
	(
		SELECT is_date('2018-02-31') AS isdate
			, 0 AS zero
	)
SELECT
	CASE
		WHEN NOT isdate THEN
			TRUE
		ELSE
			(1 / zero)::BOOLEAN
	END AS res
FROM test
;

-- Test with date in German format
WITH test AS
	(
		SELECT is_date('01.01.2018', 'DD.MM.YYYY') AS isdate
			, 0 AS zero
	)
SELECT
	CASE
		WHEN isdate THEN
			TRUE
		ELSE
			(1 / zero)::BOOLEAN
	END AS res
FROM test
;

-- Test with wrong date in German format
/**
 * As there has been a behaviour change in PostgreSQL 10, the result is only
 * false with version 10 in <9 it would be true a call to
 * SELECT to_date('31.02.2018', 'DD.MM.YYYY')::DATE;
 * would return 2018-03-03
 */
WITH test AS
	(
		SELECT is_date('31.02.2018', 'DD.MM.YYYY') AS isdate
			, 0 AS zero
			, current_setting('server_version_num')::INTEGER as version_num
	)
SELECT
	CASE
		WHEN version_num >= 100000 THEN
			CASE
				WHEN NOT isdate THEN
					TRUE
				ELSE
					(1 / zero)::BOOLEAN
			END
		ELSE
			CASE
				WHEN isdate THEN
					TRUE
				ELSE
					(1 / zero)::BOOLEAN
			END
	END AS res
FROM test
;

ROLLBACK;

SELECT 'Test starting: function_is_time' AS next_test;
/**
 * Test for function is_time
 *
 * Every test does raise division by zero if it failes
 */
BEGIN;

-- Test if the function exists
WITH test AS
	(
		SELECT COUNT(*) AS exist
		FROM pg_catalog.pg_proc
		WHERE proname = 'is_time'
	)
SELECT 2 / test.exist = 1 AS res
FROM test
;

-- Test if all implementations exists
WITH test AS
	(
		SELECT COUNT(*) AS exist
		FROM pg_catalog.pg_proc
		WHERE proname = 'is_time'
	)
SELECT test.exist = 2 AS res
FROM test
;

-- Test with time in default format
WITH test AS
	(
		SELECT is_time('14:33:55.456574') AS istime
			, 0 AS zero
	)
SELECT
	CASE
		WHEN istime THEN
			TRUE
		ELSE
			(1 / zero)::BOOLEAN
	END AS res
FROM test
;

-- Test with wrong time in default format
WITH test AS
	(
		SELECT is_time('25:33:55.456574') AS istime
			, 0 AS zero
	)
SELECT
	CASE
		WHEN NOT istime THEN
			TRUE
		ELSE
			(1 / zero)::BOOLEAN
	END AS res
FROM test
;

-- Test with time in some format
WITH test AS
	(
	SELECT is_time('14.33.55,456574', 'HH24.MI.SS,US') AS istime
		, 0 AS zero
	)
SELECT
	CASE
		WHEN istime THEN
			TRUE
		ELSE
			(1 / zero)::BOOLEAN
	END AS res
FROM test
;

-- Test with wrong time in some format
/**
 * As there has been a behaviour change in PostgreSQL 10, the result is only
 * false with version 10 in <9 it would be true a call to
 * SELECT to_timestamp('25:33:55.456574', 'HH24.MI.SS,US')::TIME;
 * would return 01:33:55.456574
 */
WITH test AS
	(
	SELECT is_time('25.33.55,456574', 'HH24.MI.SS,US') AS istime
		, 0 AS zero
		, current_setting('server_version_num')::INTEGER as version_num
	)
SELECT
	CASE
		WHEN version_num >= 100000 THEN
			CASE
				WHEN NOT istime THEN
					TRUE
				ELSE
					(1 / zero)::BOOLEAN
			END
		ELSE
			CASE
				WHEN istime THEN
					TRUE
				ELSE
					(1 / zero)::BOOLEAN
			END
	END AS res
FROM test
;

ROLLBACK;

SELECT 'Test starting: function_is_timestamp' AS next_test;
/**
 * Test for function is_timestamp
 *
 * Every test does raise division by zero if it failes
 */
BEGIN;

-- Test if the function exists
WITH test AS
	(
		SELECT COUNT(*) AS exist
		FROM pg_catalog.pg_proc
		WHERE proname = 'is_timestamp'
	)
SELECT 2 / test.exist = 1 AS res
FROM test
;

-- Test if all implementations exists
WITH test AS
	(
		SELECT COUNT(*) AS exist
		FROM pg_catalog.pg_proc
		WHERE proname = 'is_timestamp'
	)
SELECT test.exist = 2 AS res
FROM test
;

-- Test with timestamp in default format
WITH test AS
	(
		SELECT is_timestamp('2018-01-01 00:00:00') AS istimestamp
			, 0 AS zero
	)
SELECT
	CASE
		WHEN istimestamp THEN
			TRUE
		ELSE
			(1 / zero)::BOOLEAN
	END AS res
FROM test
;

-- Test with wrong timestamp in default format
WITH test AS
	(
		SELECT is_timestamp('2018-01-01 25:00:00') AS istimestamp
			, 0 AS zero
	)
SELECT
	CASE
		WHEN NOT istimestamp THEN
			TRUE
		ELSE
			(1 / zero)::BOOLEAN
	END AS res
FROM test
;

-- Test with timestamp in German format
WITH test AS
	(
		SELECT is_timestamp('01.01.2018 00:00:00', 'DD.MM.YYYY HH24.MI.SS') AS istimestamp
			, 0 AS zero
	)
SELECT
	CASE
		WHEN istimestamp THEN
			TRUE
		ELSE
			(1 / zero)::BOOLEAN
	END AS res
FROM test
;

-- Test with wrong timestamp in German format
/**
 * As there has been a behaviour change in PostgreSQL 10, the result is only
 * false with version 10 in <9 it would be true a call to
 * SELECT to_timestamp('01.01.2018 25:00:00', 'DD.MM.YYYY HH24.MI.SS')::TIMESTAMP;
 * would return 2018-01-02 01:00:00
 */
WITH test AS
	(
		SELECT is_timestamp('01.01.2018 25:00:00', 'DD.MM.YYYY HH24.MI.SS') AS istimestamp
			, 0 AS zero
			, current_setting('server_version_num')::INTEGER as version_num
	)
SELECT
	CASE
		WHEN version_num >= 100000 THEN
			CASE
				WHEN NOT istimestamp THEN
					TRUE
				ELSE
					(1 / zero)::BOOLEAN
			END
		ELSE
			CASE
			WHEN istimestamp THEN
				TRUE
			ELSE
				(1 / zero)::BOOLEAN
		END
	END AS res
FROM test
;

ROLLBACK;

SELECT 'Test starting: function_is_numeric' AS next_test;
/**
 * Test for function is_numeric
 *
 * Every test does raise division by zero if it failes
 */
BEGIN;

-- Test if the function exists
WITH test AS
	(
		SELECT COUNT(*) AS exist
		FROM pg_catalog.pg_proc
		WHERE proname = 'is_numeric'
	)
SELECT 1 / test.exist = 1 AS res
FROM test
;

-- Test integer
WITH test AS
	(
		SELECT is_numeric('123') AS isnumeric, 0 AS zero
	)
SELECT
	CASE
		WHEN isnumeric THEN
			TRUE
		ELSE
			(1 / zero)::BOOLEAN
	END AS res
FROM test
;

-- Test a number with decimal separator
WITH test AS
	(
		SELECT is_numeric('123.456') AS isnumeric, 0 AS zero
	)
SELECT
	CASE
		WHEN isnumeric THEN
			TRUE
		ELSE
			(1 / zero)::BOOLEAN
	END AS res
FROM test
;

-- Test not a number
WITH test AS
	(
		SELECT is_numeric('1 2') AS isnumeric, 0 AS zero
	)
SELECT
	CASE
		WHEN NOT isnumeric THEN
			TRUE
		ELSE
			(1 / zero)::BOOLEAN
	END AS res
FROM test
;

ROLLBACK;

SELECT 'Test starting: function_is_bigint' AS next_test;
/**
 * Test for function is_bigint
 *
 * Every test does raise division by zero if it failes
 */
BEGIN;

-- Test if the function exists
WITH test AS
	(
		SELECT COUNT(*) AS exist
		FROM pg_catalog.pg_proc
		WHERE proname = 'is_bigint'
	)
SELECT 1 / test.exist = 1 AS res
FROM test
;

-- Test integer
WITH test AS
	(
		SELECT is_bigint('123') AS isbigint, 0 AS zero
	)
SELECT
	CASE
		WHEN isbigint THEN
			TRUE
		ELSE
			(1 / zero)::BOOLEAN
	END AS res
FROM test
;

-- Test a number with decimal separator, not a bigint
WITH test AS
	(
		SELECT is_bigint('123.456') AS isbigint, 0 AS zero
	)
SELECT
	CASE
		WHEN NOT isbigint THEN
			TRUE
		ELSE
			(1 / zero)::BOOLEAN
	END AS res
FROM test
;

-- Test a number with out of range value, not a bigint
WITH test AS
	(
		SELECT is_bigint('32435463435745636545') AS isbigint, 0 AS zero
	)
SELECT
	CASE
		WHEN NOT isbigint THEN
			TRUE
		ELSE
			(1 / zero)::BOOLEAN
	END AS res
FROM test
;

ROLLBACK;

SELECT 'Test starting: function_is_integer' AS next_test;
/**
 * Test for function is_integer
 *
 * Every test does raise division by zero if it failes
 */
BEGIN;

-- Test if the function exists
WITH test AS
	(
		SELECT COUNT(*) AS exist
		FROM pg_catalog.pg_proc
		WHERE proname = 'is_integer'
	)
SELECT 1 / test.exist = 1 AS res
FROM test
;

-- Test integer
WITH test AS
	(
		SELECT is_integer('123') AS isinteger, 0 AS zero
	)
SELECT
	CASE
		WHEN isinteger THEN
			TRUE
		ELSE
			(1 / zero)::BOOLEAN
	END AS res
FROM test
;

-- Test a number with decimal separator, not an integer
WITH test AS
	(
		SELECT is_integer('123.456') AS isinteger, 0 AS zero
	)
SELECT
	CASE
		WHEN NOT isinteger THEN
			TRUE
		ELSE
			(1 / zero)::BOOLEAN
	END AS res
FROM test
;

-- Test a number with out of range value, not an integer
WITH test AS
	(
		SELECT is_integer('3243546343') AS isinteger, 0 AS zero
	)
SELECT
	CASE
		WHEN NOT isinteger THEN
			TRUE
		ELSE
			(1 / zero)::BOOLEAN
	END AS res
FROM test
;

ROLLBACK;

SELECT 'Test starting: function_is_smallint' AS next_test;
/**
 * Test for function is_smallint
 *
 * Every test does raise division by zero if it failes
 */
BEGIN;

-- Test if the function exists
WITH test AS
	(
		SELECT COUNT(*) AS exist
		FROM pg_catalog.pg_proc
		WHERE proname = 'is_smallint'
	)
SELECT 1 / test.exist = 1 AS res
FROM test
;

-- Test integer
WITH test AS
	(
		SELECT is_smallint('123') AS issmallint, 0 AS zero
	)
SELECT
	CASE
		WHEN issmallint THEN
			TRUE
		ELSE
			(1 / zero)::BOOLEAN
	END AS res
FROM test
;

-- Test a number with decimal separator, not a smallint
WITH test AS
	(
		SELECT is_smallint('123.456') AS issmallint, 0 AS zero
	)
SELECT
	CASE
		WHEN NOT issmallint THEN
			TRUE
		ELSE
			(1 / zero)::BOOLEAN
	END AS res
FROM test
;

-- Test a number with out of range value, not a smallint
WITH test AS
	(
		SELECT is_smallint('3243546343') AS issmallint, 0 AS zero
	)
SELECT
	CASE
		WHEN NOT issmallint THEN
			TRUE
		ELSE
			(1 / zero)::BOOLEAN
	END AS res
FROM test
;

ROLLBACK;

SELECT 'Test starting: function_is_real' AS next_test;
/**
 * Test for function is_real
 *
 * Every test does raise division by zero if it failes
 */
BEGIN;

-- Test if the function exists
WITH test AS
	(
		SELECT COUNT(*) AS exist
		FROM pg_catalog.pg_proc
		WHERE proname = 'is_real'
	)
SELECT 1 / test.exist = 1 AS res
FROM test
;

-- Test integer
WITH test AS
	(
		SELECT is_real('123') AS isreal, 0 AS zero
	)
SELECT
	CASE
		WHEN isreal THEN
			TRUE
		ELSE
			(1 / zero)::BOOLEAN
	END AS res
FROM test
;

-- Test a number with decimal separator
WITH test AS
	(
		SELECT is_real('123.456') AS isreal, 0 AS zero
	)
SELECT
	CASE
		WHEN isreal THEN
			TRUE
		ELSE
			(1 / zero)::BOOLEAN
	END AS res
FROM test
;

-- Test a number with out of range value, not an integer
WITH test AS
	(
		SELECT is_real('123,456') AS isreal, 0 AS zero
	)
SELECT
	CASE
		WHEN NOT isreal THEN
			TRUE
		ELSE
			(1 / zero)::BOOLEAN
	END AS res
FROM test
;

ROLLBACK;

SELECT 'Test starting: function_is_double_precision' AS next_test;
/**
 * Test for function is_double_precision
 *
 * Every test does raise division by zero if it failes
 */
BEGIN;

-- Test if the function exists
WITH test AS
	(
		SELECT COUNT(*) AS exist
		FROM pg_catalog.pg_proc
		WHERE proname = 'is_double_precision'
	)
SELECT 1 / test.exist = 1 AS res
FROM test
;

-- Test integer
WITH test AS
	(
		SELECT is_double_precision('123') AS isdoubleprecision, 0 AS zero
	)
SELECT
	CASE
		WHEN isdoubleprecision THEN
			TRUE
		ELSE
			(1 / zero)::BOOLEAN
	END AS res
FROM test
;

-- Test a number with decimal separator
WITH test AS
	(
		SELECT is_double_precision('123.456') AS isdoubleprecision, 0 AS zero
	)
SELECT
	CASE
		WHEN isdoubleprecision THEN
			TRUE
		ELSE
			(1 / zero)::BOOLEAN
	END AS res
FROM test
;

-- Test a number with out of range value, not an integer
WITH test AS
	(
		SELECT is_double_precision('123,456') AS isdoubleprecision, 0 AS zero
	)
SELECT
	CASE
		WHEN NOT isdoubleprecision THEN
			TRUE
		ELSE
			(1 / zero)::BOOLEAN
	END AS res
FROM test
;

ROLLBACK;

SELECT 'Test starting: function_is_boolean' AS next_test;
/**
 * Test for function is_boolean
 *
 * Every test does raise division by zero if it failes
 */
BEGIN;

-- Test if the function exists
WITH test AS
	(
		SELECT COUNT(*) AS exist
		FROM pg_catalog.pg_proc
		WHERE proname = 'is_boolean'
	)
SELECT 1 / test.exist = 1 AS res
FROM test
;

-- Test single letters beeing boolean
WITH test AS
	(
		SELECT is_boolean('t') AS isboolean_0
			, is_boolean('f') AS isboolean_1
			, is_boolean('T') AS isboolean_2
			, is_boolean('F') AS isboolean_3
			, is_boolean('y') AS isboolean_4
			, is_boolean('n') AS isboolean_5
			, is_boolean('Y') AS isboolean_6
			, is_boolean('N') AS isboolean_7
			, 0 AS zero
	)
SELECT
	CASE
		WHEN isboolean_0 AND isboolean_1 AND isboolean_2 AND isboolean_3 AND
			isboolean_4 AND isboolean_5 AND isboolean_6 AND isboolean_7
		THEN
			TRUE
		ELSE
			(1 / zero)::BOOLEAN
	END AS res
FROM test
;

-- Test words being boolean
WITH test AS
	(
		SELECT is_boolean('TRUE') AS isboolean_0
			, is_boolean('FALSE') AS isboolean_1
			, is_boolean('true') AS isboolean_2
			, is_boolean('false') AS isboolean_3
			, is_boolean('YES') AS isboolean_4
			, is_boolean('NO') AS isboolean_5
			, is_boolean('yes') AS isboolean_6
			, is_boolean('no') AS isboolean_7
			, 0 AS zero
	)
SELECT
CASE
	WHEN isboolean_0 AND isboolean_1 AND isboolean_2 AND isboolean_3 AND
		isboolean_4 AND isboolean_5 AND isboolean_6 AND isboolean_7
	THEN
		TRUE
	ELSE
		(1 / zero)::BOOLEAN
END AS res
FROM test
;

-- Test text for not beeing boolean
WITH test AS
	(
		SELECT is_boolean('Not a BOOLEAN') AS isboolean, 0 AS zero
	)
SELECT
	CASE
		WHEN NOT isboolean THEN
			TRUE
		ELSE
			(1 / zero)::BOOLEAN
	END AS res
FROM test
;

-- Test positive number for not beeing boolean
WITH test AS
	(
		SELECT is_boolean('3') AS isboolean, 0 AS zero
	)
SELECT
	CASE
		WHEN NOT isboolean THEN
			TRUE
		ELSE
			(1 / zero)::BOOLEAN
	END AS res
FROM test
;

-- Test negative number for not beeing boolean
WITH test AS
	(
		SELECT is_boolean('-1') AS isboolean, 0 AS zero
	)
SELECT
	CASE
		WHEN NOT isboolean THEN
			TRUE
		ELSE
			(1 / zero)::BOOLEAN
	END AS res
FROM test
;

ROLLBACK;

SELECT 'Test starting: function_pg_schema_size' AS next_test;
/**
 * Test for function pg_schema_size
 *
 * Every test does raise division by zero if it failes
 */
BEGIN;

-- Test if the function exists
WITH test AS
	(
		SELECT COUNT(*) AS exist
		FROM pg_catalog.pg_proc
		WHERE proname = 'pg_schema_size'
	)
SELECT 1 / test.exist = 1 AS res
FROM test
;

-- Prevent no result because of an empty database without any tables
-- Create at table
CREATE TABLE test_pg_schema_size(id INTEGER, some_value VARCHAR);

-- Insert some data
INSERT INTO test_pg_schema_size(id, some_value) VALUES
	(1, 'value 1'),
	(1, NULL),
	(2, 'value 2'),
	(2, NULL),
	(2, NULL),
	(3, 'value 3')
;


-- Test with date in default format
WITH test AS
	(
		SELECT pg_schema_size('public') AS schema_size, 0 AS zero
	)
SELECT
	CASE
		WHEN schema_size > 0 THEN
			TRUE
		ELSE
			(1 / zero)::BOOLEAN
	END AS res
FROM test
;

ROLLBACK;

SELECT 'Test starting: view_pg_db_views' AS next_test;
/**
 * Test for view pg_db_views
 *
 * Every test does raise division by zero if it failes
 */
BEGIN;

-- Test if the function exists
WITH test AS
	(
		SELECT COUNT(*) AS exist
		FROM pg_catalog.pg_views
		WHERE viewname = 'pg_db_views'
	)
SELECT 1 / test.exist = 1 AS res
FROM test
;

-- Test if the view runs without errors
WITH test AS
	(
		SELECT count(*) as key_count
			, 0 AS zero
	FROM pg_db_views
	)
SELECT
	CASE
		WHEN key_count >= 0 THEN
			TRUE
		ELSE
			(1 / zero)::BOOLEAN
	END AS res
FROM test
;

ROLLBACK;

SELECT 'Test starting: view_pg_foreign_keys' AS next_test;
/**
 * Test for view pg_foreign_keys
 *
 * Every test does raise division by zero if it failes
 */
BEGIN;

-- Test if the function exists
WITH test AS
	(
		SELECT COUNT(*) AS exist
		FROM pg_catalog.pg_views
		WHERE viewname = 'pg_foreign_keys'
	)
SELECT 1 / test.exist = 1 AS res
FROM test
;

-- Test if the view runs without errors
WITH test AS
	(
		SELECT count(*) as key_count
			, 0 AS zero
	FROM pg_foreign_keys
	)
SELECT
	CASE
		WHEN key_count >= 0 THEN
			TRUE
		ELSE
			(1 / zero)::BOOLEAN
	END AS res
FROM test
;

ROLLBACK;

SELECT 'Test starting: view_pg_functions' AS next_test;
/**
 * Test for view pg_functions
 *
 * Every test does raise division by zero if it failes
 */
BEGIN;

-- Test if the function exists
WITH test AS
	(
		SELECT COUNT(*) AS exist
		FROM pg_catalog.pg_views
		WHERE viewname = 'pg_functions'
	)
SELECT 1 / test.exist = 1 AS res
FROM test
;

-- Test if the view runs without errors
WITH test AS
	(
	SELECT count(*) as key_count
			, 0 AS zero
	FROM pg_functions
	)
SELECT
	CASE
		WHEN key_count >= 0 THEN
			TRUE
		ELSE
			(1 / zero)::BOOLEAN
	END AS res
FROM test
;

ROLLBACK;

SELECT 'Test starting: function_is_encoding' AS next_test;
/**
 * Test for function is_encoding
 *
 * Every test does raise division by zero if it failes
 */
BEGIN;

-- Test if the function exists
WITH test AS
	(
		SELECT COUNT(*) AS exist
		FROM pg_catalog.pg_proc
		WHERE proname = 'is_encoding'
	)
SELECT 2 / test.exist = 1 AS res
FROM test
;

-- Test if all implementations exists
WITH test AS
	(
		SELECT COUNT(*) AS exist
		FROM pg_catalog.pg_proc
		WHERE proname = 'is_encoding'
	)
SELECT test.exist = 2 AS res
FROM test
;

-- Test with a test string containing only latin1
WITH test AS
	(
		SELECT is_encoding('Some characters', 'LATIN1') AS isencoding, 0 AS zero
	)
SELECT
	CASE
		WHEN isencoding THEN
			TRUE
		ELSE
			(1 / zero)::BOOLEAN
	END AS res
FROM test
;

-- Test with a test string containing non latin1 characters
WITH test AS
	(
		SELECT is_encoding('Some characters, ğ is Turkish and not latin1', 'LATIN1') AS isencoding, 0 AS zero
	)
SELECT
	CASE
		WHEN NOT isencoding THEN
			TRUE
		ELSE
			(1 / zero)::BOOLEAN
	END AS res
FROM test
;

-- Test with a test string containing only latin1
WITH test AS
	(
		SELECT is_encoding('Some characters', 'LATIN1', 'UTF8') AS isencoding, 0 AS zero
	)
SELECT
	CASE
		WHEN isencoding THEN
			TRUE
		ELSE
			(1 / zero)::BOOLEAN
	END AS res
FROM test
;

-- Test with a test string containing non latin1 characters
WITH test AS
	(
		SELECT is_encoding('Some characters, ğ is Turkish and not latin1', 'LATIN1', 'UTF8') AS isencoding, 0 AS zero
	)
SELECT
	CASE
		WHEN NOT isencoding THEN
			TRUE
		ELSE
			(1 / zero)::BOOLEAN
	END AS res
FROM test
;

ROLLBACK;

SELECT 'Test starting: function_is_latin1' AS next_test;
/**
 * Test for function is_latin1
 *
 * Every test does raise division by zero if it failes
 */
BEGIN;

-- Test if the function exists
WITH test AS
	(
		SELECT COUNT(*) AS exist
		FROM pg_catalog.pg_proc
		WHERE proname = 'is_latin1'
	)
SELECT 1 / test.exist = 1 AS res
FROM test
;

-- Test with a test string containing only latin1
WITH test AS
	(
		SELECT is_latin1('Some characters') AS islatin1, 0 AS zero
	)
SELECT
	CASE
		WHEN islatin1 THEN
			TRUE
		ELSE
			(1 / zero)::BOOLEAN
	END AS res
FROM test
;

-- Test with a test string containing non latin1 characters
WITH test AS
	(
		SELECT is_latin1('Some characters, ğ is Turkish and not latin1') AS islatin1, 0 AS zero
	)
SELECT
	CASE
		WHEN NOT islatin1 THEN
			TRUE
		ELSE
			(1 / zero)::BOOLEAN
	END AS res
FROM test
;

ROLLBACK;

SELECT 'Test starting: function_return_not_part_of_latin1' AS next_test;
/**
 * Test for the functions return_not_part_of_latin1
 *
 * Every test does raise division by zero if it failes
 */
BEGIN;

-- Test if the function exists
WITH test AS
	(
		SELECT count(*) AS exist
		FROM pg_catalog.pg_proc
		WHERE proname = 'return_not_part_of_latin1'
	)
SELECT 1 / test.exist = 1 AS res
FROM test
;

-- Test the returning result which should contain two array elements
with test AS
	(
		SELECT return_not_part_of_latin1('ağbƵcğeƵ') AS res
			, 0 AS zero
	)
SELECT
	CASE
		WHEN array_length(res, 1) = 2 THEN
			TRUE
		ELSE
			(1 / test.zero)::BOOLEAN
	END as res_1
	, CASE
		WHEN 'ğ' = ANY (res) AND 'Ƶ' = ANY (res) THEN
			TRUE
		ELSE
			(1 / test.zero)::BOOLEAN
	END as res_2
FROM test
;

END;

SELECT 'Test starting: function_replace_encoding' AS next_test;
/**
 * Test for the functions replace_encoding
 *
 * Every test does raise division by zero if it failes
 */
BEGIN;

-- Test if the function exists
WITH test AS
	(
		SELECT count(*) AS exist
		FROM pg_catalog.pg_proc
		WHERE proname = 'replace_encoding'
	)
SELECT 3 / test.exist = 1 AS res
FROM test
;

-- Test if all three implementations exists
WITH test AS
	(
		SELECT count(*) AS exist
			, 0 AS zero
		FROM pg_catalog.pg_proc
		WHERE proname = 'replace_encoding'
	)
SELECT
	CASE
		WHEN test.exist = 3 THEN
			TRUE
		ELSE
			(1 / test.zero)::BOOLEAN
	END AS res
FROM test
;

-- Test of the first implementation which replaces none latin1 characters with
-- empty strings
WITH test AS
	(
		SELECT 'ağbƵcğeƵ'::TEXT AS test_string
			, 'latin1'::TEXT AS enc
		, 0 AS zero
	)
SELECT
	CASE
		WHEN length(test_string) = 8 AND length(replace_encoding(test_string, enc)) = 4 THEN
			TRUE
		ELSE
			(1 / test.zero)::BOOLEAN
	END AS res_1
	, CASE
		WHEN is_encoding(replace_encoding(test_string, enc), enc) THEN
			TRUE
		ELSE
			(1 / test.zero)::BOOLEAN
	END AS res_2
FROM test
;

-- Test of the second implementation which replaces none latin1 characters with
-- second parameter
WITH test AS
	(
		SELECT 'ağbcğe'::TEXT AS test_string
			, 'latin1'::TEXT AS enc
			, 'g'::TEXT AS replacement
			, 0 AS zero
	)
SELECT
	CASE
		WHEN length(test_string) = length(replace_encoding(test_string, enc, replacement)) THEN
			TRUE
		ELSE
			(1 / test.zero)::BOOLEAN
	END AS res_1
	, CASE
		WHEN is_encoding(replace_encoding(test_string, enc, replacement), enc) THEN
			TRUE
		ELSE
			(1 / test.zero)::BOOLEAN
	END AS res_2
FROM test
;

-- Test of the third implementation which replaces given none latin1 characters
-- in an array as second parameter with latin1 characters given in an array as
-- the third paramater
WITH test AS
	(
		SELECT 'ağbƵcğeƵ'::TEXT AS test_string
			, string_to_array('ğ,Ƶ'::TEXT, ',') AS to_replace
			, string_to_array('g,Z'::TEXT, ',') AS replacement
			, 0 AS zero
	)
SELECT
	CASE
		WHEN length(test_string) = length(replace_encoding(test_string, to_replace, replacement)) THEN
			TRUE
		ELSE
			(1 / test.zero)::BOOLEAN
	END AS res_1
	, CASE
		WHEN is_latin1(replace_encoding(test_string, to_replace, replacement)) THEN
			TRUE
		ELSE
			(1 / test.zero)::BOOLEAN
	END AS res_2
FROM test
;

ROLLBACK;

SELECT 'Test starting: function_replace_latin1' AS next_test;
/**
 * Test for the functions replace_latin1
 *
 * Every test does raise division by zero if it failes
 */
BEGIN;

-- Test if the function exists
WITH test AS
	(
		SELECT count(*) AS exist
		FROM pg_catalog.pg_proc
		WHERE proname = 'replace_latin1'
	)
SELECT 3 / test.exist = 1 AS res
FROM test
;

-- Test if all three implementations exists
WITH test AS
	(
		SELECT count(*) AS exist
			, 0 AS zero
		FROM pg_catalog.pg_proc
		WHERE proname = 'replace_latin1'
	)
SELECT
	CASE
		WHEN test.exist = 3 THEN
			TRUE
		ELSE
			(1 / test.zero)::BOOLEAN
	END AS res
FROM test
;

-- Test of the first implementation which replaces none latin1 characters with
-- empty strings
WITH test AS
	(
		SELECT 'ağbƵcğeƵ'::TEXT AS test_string
			, 0 AS zero
	)
SELECT
	CASE
		WHEN length(test_string) = 8 AND length(replace_latin1(test_string)) = 4 THEN
			TRUE
		ELSE
			(1 / test.zero)::BOOLEAN
		END AS res_1
	, CASE
		WHEN is_latin1(replace_latin1(test_string)) THEN
			TRUE
		ELSE
			(1 / test.zero)::BOOLEAN
	END AS res_2
FROM test
;

-- Test of the second implementation which replaces none latin1 characters with
-- second parameter
WITH test AS
	(
		SELECT 'ağbcğe'::TEXT AS test_string
			, 'g'::TEXT AS replacement
			, 0 AS zero
	)
SELECT
	CASE
		WHEN length(test_string) = length(replace_latin1(test_string, replacement)) THEN
			TRUE
		ELSE
			(1 / test.zero)::BOOLEAN
	END AS res_1
	, CASE
		WHEN is_latin1(replace_latin1(test_string, replacement)) THEN
			TRUE
		ELSE
			(1 / test.zero)::BOOLEAN
	END AS res_2
FROM test
;

-- Test of the third implementation which replaces given none latin1 characters
-- in an array as second parameter with latin1 characters given in an array as
-- the third paramater
WITH test AS
	(
		SELECT 'ağbƵcğeƵ'::TEXT AS test_string
			, string_to_array('ğ,Ƶ'::TEXT, ',') AS to_replace
			, string_to_array('g,Z'::TEXT, ',') AS replacement
			, 0 AS zero
	)
SELECT
	CASE
		WHEN length(test_string) = length(replace_latin1(test_string, to_replace, replacement)) THEN
			TRUE
		ELSE
			(1 / test.zero)::BOOLEAN
	END AS res_1
	, CASE
		WHEN is_latin1(replace_latin1(test_string, to_replace, replacement)) THEN
			TRUE
		ELSE
			(1 / test.zero)::BOOLEAN
	END AS res_2
FROM test
;

ROLLBACK;

SELECT 'Test starting: function_return_not_part_of_encoding' AS next_test;
/**
 * Test for the functions return_not_part_of_encoding
 *
 * Every test does raise division by zero if it failes
 */
BEGIN;

-- Test if the function exists
WITH test AS
	(
		SELECT count(*) AS exist
		FROM pg_catalog.pg_proc
		WHERE proname = 'return_not_part_of_encoding'
	)
SELECT 1 / test.exist = 1 AS res
FROM test
;

-- Test the returning result which should contain two array elements
WITH test AS
	(
		SELECT return_not_part_of_encoding('ağbƵcğeƵ', 'latin1') AS res
			, 0 AS zero
	)
SELECT
	CASE
		WHEN array_length(res, 1) = 2 THEN
			TRUE
		ELSE
			(1 / test.zero)::BOOLEAN
	END as res_1
	, CASE
		WHEN 'ğ' = ANY (res) AND 'Ƶ' = ANY (res) THEN
			TRUE
		ELSE
			(1 / test.zero)::BOOLEAN
	END as res_2
FROM test
;

ROLLBACK;

SELECT 'Test starting: aggregate_function_gab_fill' AS next_test;
/**
 * Test for the aggregate gap_fill
 *
 * Every test does raise division by zero if it failes
 */
BEGIN;

-- Test if the function exists
WITH test AS
	(
		SELECT count(*) AS exist
		FROM pg_catalog.pg_proc
		WHERE proname = 'gap_fill_internal'
	)
SELECT 1 / test.exist = 1 AS res
FROM test
;

-- Test if the aggregate exists
WITH test AS
	(
		SELECT count(*) AS exist
		FROM pg_catalog.pg_proc
		WHERE proname = 'gap_fill'
	)
SELECT 1 / test.exist = 1 AS res
FROM test
;

-- Create a table with some test values
CREATE TABLE test_gap_fill(id INTEGER, some_value VARCHAR);

INSERT INTO test_gap_fill(id, some_value) VALUES
	(1, 'value 1'),
	(1, NULL),
	(2, 'value 2'),
	(2, NULL),
	(2, NULL),
	(3, 'value 3')
;

-- Select the test data and return filled columns, the count of colums should be
-- the same number as the count of not empty ones
WITH t1 AS
	(
		SELECT id
			, gap_fill(some_value) OVER (ORDER BY id) AS some_value
		FROM test_gap_fill
	)
SELECT count(*) / count(*) FILTER (WHERE NOT some_value IS NULL) = 1 AS res
FROM t1
;

ROLLBACK;

SELECT 'Test starting: function_date_de' AS next_test;
/**
 * Test for function date_de
 *
 * Every test does raise division by zero if it failes
 */
BEGIN;

-- Test if the function exists
WITH test AS
	(
		SELECT COUNT(*) AS exist
		FROM pg_catalog.pg_proc
		WHERE proname = 'date_de'
	)
SELECT 1 / test.exist = 1 AS res
FROM test
;

-- Uses function is_date which is part of this repository
WITH test AS
	(
		SELECT date_de('2018-01-01') AS d_de
			, 0 AS zero
	)
SELECT
	CASE
		WHEN is_date(d_de, 'DD.MM.YYYY') THEN
			TRUE
		ELSE
			(1 / zero)::BOOLEAN
		END AS res
FROM test
;

ROLLBACK;

SELECT 'Test starting: function_datetime_de' AS next_test;
/**
 * Test for function datetime_de
 *
 * Every test does raise division by zero if it failes
 */
BEGIN;

-- Test if the function exists
WITH test AS
	(
		SELECT COUNT(*) AS exist
		FROM pg_catalog.pg_proc
		WHERE proname = 'datetime_de'
	)
SELECT 1 / test.exist = 1 AS res
FROM test
;

-- Uses function is_timestamp which is part of this repository
WITH test AS
	(
		SELECT datetime_de('2018-01-01 13:30:30 GMT') AS ts_de
			, 0 AS zero
	)
SELECT
	CASE
		WHEN is_timestamp(ts_de, 'DD.MM.YYYY HH24:MI:SS') THEN
			TRUE
		ELSE
			(1 / zero)::BOOLEAN
		END AS res
FROM test
;

ROLLBACK;

SELECT 'Test starting: function_to_unix_timestamp' AS next_test;
/**
 * Test for functions to_unix_timestamp
 *
 * Every test does raise division by zero if it failes
 */
BEGIN;

-- Test if the function exists
WITH test AS
	(
		SELECT COUNT(*) AS exist
		FROM pg_catalog.pg_proc
		WHERE proname = 'to_unix_timestamp'
	)
SELECT 2 / test.exist = 1 AS res
FROM test
;

-- Test with timestamp without time zone
WITH test AS
	(
		SELECT to_unix_timestamp('2018-01-01 00:00:00') AS unix_timestamp, 0 AS zero
	)
SELECT
	CASE
		WHEN unix_timestamp > 0 THEN
			TRUE
		ELSE
			(1 / zero)::BOOLEAN
	END AS res
FROM test
;

-- Test with timestamp with time zone
WITH test AS
	(
		SELECT to_unix_timestamp(now()) AS unix_timestamp, 0 AS zero
	)
SELECT
	CASE
		WHEN unix_timestamp > 0 THEN
			TRUE
		ELSE
			(1 / zero)::BOOLEAN
	END AS res
FROM test
;

ROLLBACK;

SELECT 'Test starting: function_is_empty' AS next_test;
/**
 * Test for function is_empty
 *
 * Every test does raise division by zero if it failes
 */
BEGIN;

-- Test if the function exists
WITH test AS
	(
		SELECT COUNT(*) AS exist
		FROM pg_catalog.pg_proc
		WHERE proname = 'is_empty'
	)
SELECT 1 / test.exist = 1 AS res
FROM test
;

-- Test not empty
WITH test AS
	(
		SELECT is_empty('abc') AS isempty, 0 AS zero
	)
SELECT
	CASE
		WHEN NOT isempty THEN
			TRUE
		ELSE
			(1 / zero)::BOOLEAN
	END AS res
FROM test
;

-- Test empty string
WITH test AS
	(
		SELECT is_empty('') AS isempty, 0 AS zero
	)
SELECT
	CASE
		WHEN isempty THEN
			TRUE
		ELSE
			(1 / zero)::BOOLEAN
	END AS res
FROM test
;

-- Test NULL
WITH test_data AS
	(
		SELECT NULL::TEXT AS test_value
	)
, test AS
	(
		SELECT is_empty(test_value) AS isempty, 0 AS zero
		FROM test_data
	)
SELECT
	CASE
		WHEN isempty THEN
			TRUE
		ELSE
			(1 / zero)::BOOLEAN
	END AS res
FROM test
;

ROLLBACK;

SELECT 'Test starting: function_array_max' AS next_test;
/**
 * Test for the functions array_max
 *
 * Every test does raise division by zero if it failes
 */
BEGIN;

-- Test if the function exists
WITH test AS
	(
		SELECT count(*) AS exist
		FROM pg_catalog.pg_proc
		WHERE proname = 'array_max'
	)
SELECT 7 / test.exist = 1 AS res
FROM test
;

-- Test if all seven implementations exists
WITH test AS
	(
		SELECT count(*) AS exist
			, 0 AS zero
		FROM pg_catalog.pg_proc
		WHERE proname = 'array_max'
	)
SELECT
	CASE
		WHEN test.exist = 7 THEN
			TRUE
		ELSE
			(1 / test.zero)::BOOLEAN
	END AS res
FROM test
;

-- Test of the first implementation
-- SMALLINT ARRAY
WITH test AS
	(
		SELECT array_max(ARRAY[45, 60, 43, 99]::SMALLINT[]) AS max_value
		, 0 AS zero
	)
SELECT
	CASE
		WHEN max_value = 99 THEN
			TRUE
		ELSE
			(1 / test.zero)::BOOLEAN
	END AS res_1
FROM test
;

-- Test of the second implementation
-- INTEGER ARRAY
WITH test AS
	(
		SELECT array_max(ARRAY[45, 60, 43, 99]::INTEGER[]) AS max_value
		, 0 AS zero
	)
SELECT
	CASE
		WHEN max_value = 99 THEN
			TRUE
		ELSE
			(1 / test.zero)::BOOLEAN
	END AS res_1
FROM test
;

-- Test of the third implementation
-- BIGINT ARRAY
WITH test AS
	(
		SELECT array_max(ARRAY[45, 60, 43, 99]::BIGINT[]) AS max_value
		, 0 AS zero
	)
SELECT
	CASE
		WHEN max_value = 99 THEN
			TRUE
		ELSE
			(1 / test.zero)::BOOLEAN
	END AS res_1
FROM test
;

-- Test of the fourth implementation
-- TEXT ARRAY
WITH test AS
	(
		SELECT array_max(ARRAY['def', 'abc', 'ghi']::TEXT[]) AS max_value
		, 0 AS zero
	)
SELECT
	CASE
		WHEN max_value = 'ghi' THEN
			TRUE
		ELSE
			(1 / test.zero)::BOOLEAN
	END AS res_1
FROM test
;

-- Test of the fifth implementation
-- REAL ARRAY
WITH test AS
	(
		SELECT array_max(ARRAY[45.6, 60.8, 43, 99.3]::REAL[]) AS max_value
		, 0 AS zero
	)
SELECT
	CASE
		WHEN max_value = 99.3 THEN
			TRUE
		ELSE
			(1 / test.zero)::BOOLEAN
	END AS res_1
FROM test
;

-- Test of the sixth implementation
-- DOUBLE PRECISION ARRAY
WITH test AS
	(
		SELECT array_max(ARRAY[45.6, 60.8, 43, 99.3]::DOUBLE PRECISION[]) AS max_value
		, 0 AS zero
	)
SELECT
	CASE
		WHEN max_value = 99.3 THEN
			TRUE
		ELSE
			(1 / test.zero)::BOOLEAN
	END AS res_1
FROM test
;

-- Test of the seventh implementation
-- NUMERIC ARRAY
WITH test AS
	(
		SELECT array_max(ARRAY[45.6, 60.8, 43, 99.3]::NUMERIC[]) AS max_value
		, 0 AS zero
	)
SELECT
	CASE
		WHEN max_value = 99.3 THEN
			TRUE
		ELSE
			(1 / test.zero)::BOOLEAN
	END AS res_1
FROM test
;

ROLLBACK;

SELECT 'Test starting: function_array_min' AS next_test;
/**
 * Test for the functions array_min
 *
 * Every test does raise division by zero if it failes
 */
BEGIN;

-- Test if the function exists
WITH test AS
	(
		SELECT count(*) AS exist
		FROM pg_catalog.pg_proc
		WHERE proname = 'array_min'
	)
SELECT 7 / test.exist = 1 AS res
FROM test
;

-- Test if all seven implementations exists
WITH test AS
	(
		SELECT count(*) AS exist
			, 0 AS zero
		FROM pg_catalog.pg_proc
		WHERE proname = 'array_min'
	)
SELECT
	CASE
		WHEN test.exist = 7 THEN
			TRUE
		ELSE
			(1 / test.zero)::BOOLEAN
	END AS res
FROM test
;

-- Test of the first implementation
-- SMALLINT ARRAY
WITH test AS
	(
		SELECT array_min(ARRAY[45, 60, 43, 99]::SMALLINT[]) AS min_value
		, 0 AS zero
	)
SELECT
	CASE
		WHEN min_value = 43 THEN
			TRUE
		ELSE
			(1 / test.zero)::BOOLEAN
	END AS res_1
FROM test
;

-- Test of the second implementation
-- INTEGER ARRAY
WITH test AS
	(
		SELECT array_min(ARRAY[45, 60, 43, 99]::INTEGER[]) AS min_value
		, 0 AS zero
	)
SELECT
	CASE
		WHEN min_value = 43 THEN
			TRUE
		ELSE
			(1 / test.zero)::BOOLEAN
	END AS res_1
FROM test
;

-- Test of the third implementation
-- BIGINT ARRAY
WITH test AS
	(
		SELECT array_min(ARRAY[45, 60, 43, 99]::BIGINT[]) AS min_value
		, 0 AS zero
	)
SELECT
	CASE
		WHEN min_value = 43 THEN
			TRUE
		ELSE
			(1 / test.zero)::BOOLEAN
	END AS res_1
FROM test
;

-- Test of the fourth implementation
-- TEXT ARRAY
WITH test AS
	(
		SELECT array_min(ARRAY['def', 'abc', 'ghi']::TEXT[]) AS min_value
		, 0 AS zero
	)
SELECT
	CASE
		WHEN min_value = 'abc' THEN
			TRUE
		ELSE
			(1 / test.zero)::BOOLEAN
	END AS res_1
FROM test
;

-- Test of the fifth implementation
-- REAL ARRAY
WITH test AS
	(
		SELECT array_min(ARRAY[45.6, 60.8, 43.7, 99.3]::REAL[]) AS min_value
		, 0 AS zero
	)
SELECT
	CASE
		WHEN min_value = 43.7 THEN
			TRUE
		ELSE
			(1 / test.zero)::BOOLEAN
	END AS res_1
FROM test
;

-- Test of the sixth implementation
-- DOUBLE PRECISION ARRAY
WITH test AS
	(
		SELECT array_min(ARRAY[45.6, 60.8, 43.7, 99.3]::DOUBLE PRECISION[]) AS min_value
		, 0 AS zero
	)
SELECT
	CASE
		WHEN min_value = 43.7 THEN
			TRUE
		ELSE
			(1 / test.zero)::BOOLEAN
	END AS res_1
FROM test
;

-- Test of the seventh implementation
-- NUMERIC ARRAY
WITH test AS
	(
		SELECT array_min(ARRAY[45.6, 60.8, 43.7, 99.3]::NUMERIC[]) AS min_value
		, 0 AS zero
	)
SELECT
	CASE
		WHEN min_value = 43.7 THEN
			TRUE
		ELSE
			(1 / test.zero)::BOOLEAN
	END AS res_1
FROM test
;

ROLLBACK;

SELECT 'Test starting: function_array_avg' AS next_test;
/**
 * Test for the functions array_avg
 *
 * Every test does raise division by zero if it failes
 */
BEGIN;

-- Test if the function exists
WITH test AS
	(
		SELECT count(*) AS exist
		FROM pg_catalog.pg_proc
		WHERE proname = 'array_avg'
		)
SELECT 6 / test.exist = 1 AS res
FROM test
;

-- Test if all six implementations exists
WITH test AS
	(
		SELECT count(*) AS exist
			, 0 AS zero
		FROM pg_catalog.pg_proc
		WHERE proname = 'array_avg'
	)
SELECT
	CASE
		WHEN test.exist = 6 THEN
			TRUE
		ELSE
			(1 / test.zero)::BOOLEAN
	END AS res
FROM test
;

-- Test of the first implementation
-- SMALLINT ARRAY
WITH test AS
	(
		SELECT array_avg(ARRAY[45, 60, 43, 99]::SMALLINT[]) AS avg_value
		, 0 AS zero
	)
SELECT
	CASE
		WHEN avg_value = 61.75 THEN
			TRUE
		ELSE
			(1 / test.zero)::BOOLEAN
		END AS res_1
FROM test
;

-- Test of the second implementation
-- INTEGER ARRAY
WITH test AS
	(
		SELECT array_avg(ARRAY[45, 60, 43, 99]::INTEGER[]) AS avg_value
			, 0 AS zero
	)
SELECT
	CASE
		WHEN avg_value = 61.75 THEN
			TRUE
		ELSE
			(1 / test.zero)::BOOLEAN
	END AS res_1
FROM test
;

-- Test of the third implementation
-- BIGINT ARRAY
WITH test AS
	(
		SELECT array_avg(ARRAY[45, 60, 43, 99]::BIGINT[]) AS avg_value
			, 0 AS zero
	)
SELECT
	CASE
		WHEN avg_value = 61.75 THEN
			TRUE
		ELSE
			(1 / test.zero)::BOOLEAN
	END AS res_1
FROM test
;

-- Test of the fourth implementation
-- REAL ARRAY
WITH test_data AS
	(
		SELECT 45.6::REAL AS val
		UNION ALL
		SELECT 60.8::REAL
		UNION ALL
		SELECT 43::REAL
		UNION ALL
		SELECT 99.3::REAL
	)
, test AS
	(
		SELECT array_avg(ARRAY[45.6, 60.8, 43, 99.3]::REAL[]) AS avg_value
			, 0 AS zero
			, avg(val) AS test_val
		FROM test_data
	)
SELECT
	CASE
		WHEN avg_value::NUMERIC = test_val::NUMERIC THEN
			TRUE
		ELSE
			(1 / test.zero)::BOOLEAN
		END AS res_1
FROM test
;

-- Test of the fifth implementation
-- DOUBLE PRECISION ARRAY
WITH test_data AS
	(
		SELECT 45.6::DOUBLE PRECISION AS val
		UNION ALL
		SELECT 60.8::DOUBLE PRECISION
		UNION ALL
		SELECT 43::DOUBLE PRECISION
		UNION ALL
		SELECT 99.3::DOUBLE PRECISION
	)
, test AS
	(
		SELECT array_avg(ARRAY[45.6, 60.8, 43, 99.3]::DOUBLE PRECISION[]) AS avg_value
			, 0 AS zero
			, avg(val) AS test_val
			FROM test_data
	)
SELECT
	CASE
		WHEN avg_value::NUMERIC = test_val::NUMERIC THEN
			TRUE
		ELSE
			(1 / test.zero)::BOOLEAN
	END AS res_1
FROM test
;

-- Test of the sicth implementation
-- NUMERIC ARRAY
WITH test_data AS
	(
		SELECT 45.6::NUMERIC AS val
		UNION ALL
		SELECT 60.8::NUMERIC
		UNION ALL
		SELECT 43::NUMERIC
		UNION ALL
		SELECT 99.3::NUMERIC
	)
, test AS
	(
		SELECT array_avg(ARRAY[45.6, 60.8, 43, 99.3]::NUMERIC[]) AS avg_value
			, 0 AS zero
			, avg(val) AS test_val
			FROM test_data
	)
SELECT
	CASE
		WHEN avg_value::NUMERIC = test_val::NUMERIC THEN
			TRUE
		ELSE
			(1 / test.zero)::BOOLEAN
	END AS res_1
FROM test
;

ROLLBACK;

SELECT 'Test starting: function_array_sum' AS next_test;
/**
 * Test for the functions array_sum
 *
 * Every test does raise division by zero if it failes
 */
BEGIN;

-- Test if the function exists
WITH test AS
	(
		SELECT count(*) AS exist
		FROM pg_catalog.pg_proc
		WHERE proname = 'array_sum'
	)
SELECT 6 / test.exist = 1 AS res
FROM test
;

-- Test if all six implementations exists
WITH test AS
	(
		SELECT count(*) AS exist
			, 0 AS zero
		FROM pg_catalog.pg_proc
		WHERE proname = 'array_sum'
		)
SELECT
	CASE
		WHEN test.exist = 6 THEN
			TRUE
		ELSE
			(1 / test.zero)::BOOLEAN
	END AS res
FROM test
;

-- Test of the first implementation
-- SMALLINT ARRAY
WITH test AS
	(
		SELECT array_sum(ARRAY[45, 60, 43, 99]::SMALLINT[]) AS sum_value
		, 0 AS zero
	)
SELECT
	CASE
		WHEN sum_value = 247 THEN
			TRUE
		ELSE
			(1 / test.zero)::BOOLEAN
	END AS res_1
FROM test
;

-- Test of the second implementation
-- INTEGER ARRAY
WITH test AS
	(
		SELECT array_sum(ARRAY[45, 60, 43, 99]::INTEGER[]) AS sum_value
		, 0 AS zero
	)
SELECT
	CASE
		WHEN sum_value = 247 THEN
			TRUE
		ELSE
			(1 / test.zero)::BOOLEAN
	END AS res_1
FROM test
;

-- Test of the third implementation
-- BIGINT ARRAY
WITH test AS
	(
		SELECT array_sum(ARRAY[45, 60, 43, 99]::BIGINT[]) AS sum_value
		, 0 AS zero
	)
SELECT
	CASE
		WHEN sum_value = 247 THEN
			TRUE
		ELSE
			(1 / test.zero)::BOOLEAN
	END AS res_1
FROM test
;

-- Test of the fourth implementation
-- REAL ARRAY
WITH test AS
	(
		SELECT array_sum(ARRAY[45.6, 60.8, 43.7, 99.3]::REAL[]) AS sum_value
		, 0 AS zero
	)
SELECT
	CASE
		WHEN sum_value = 249.4 THEN
			TRUE
		ELSE
			(1 / test.zero)::BOOLEAN
	END AS res_1
FROM test
;

-- Test of the fifth implementation
-- DOUBLE PRECISION ARRAY
WITH test AS
	(
		SELECT array_sum(ARRAY[45.6, 60.8, 43.7, 99.3]::DOUBLE PRECISION[]) AS sum_value
		, 0 AS zero
	)
SELECT
	CASE
		WHEN sum_value = 249.4 THEN
			TRUE
		ELSE
			(1 / test.zero)::BOOLEAN
	END AS res_1
FROM test
;

-- Test of the sixth implementation
-- NUMERIC ARRAY
WITH test AS
	(
		SELECT array_sum(ARRAY[45.6, 60.8, 43.7, 99.3]::NUMERIC[]) AS sum_value
		, 0 AS zero
	)
SELECT
	CASE
		WHEN sum_value = 249.4 THEN
			TRUE
		ELSE
			(1 / test.zero)::BOOLEAN
	END AS res_1
FROM test
;

ROLLBACK;

SELECT 'Test starting: view_pg_active_locks' AS next_test;
/**
 * Test for view pg_active_locks
 *
 * Every test does raise division by zero if it failes
 */
BEGIN;

-- Test if the function exists
WITH test AS
	(
		SELECT COUNT(*) AS exist
		FROM pg_catalog.pg_views
		WHERE viewname = 'pg_active_locks'
	)
SELECT 1 / test.exist = 1 AS res
FROM test
;

-- Test if the view runs without errors
WITH test AS
	(
	SELECT count(*) as key_count
			, 0 AS zero
	FROM pg_active_locks
	)
SELECT
	CASE
		WHEN key_count >= 0 THEN
			TRUE
		ELSE
			(1 / zero)::BOOLEAN
	END AS res
FROM test
;

ROLLBACK;