/******************************************************************************** The PostgreSQL License

Copyright © 2014, Binod Nepal, Mix Open Foundation (mixof.org).

Permission to use, copy, modify, and distribute this software and its documentation for any purpose, without fee, and without a written agreement is hereby granted, provided that the above copyright notice and this paragraph and the following two paragraphs appear in all copies.

IN NO EVENT SHALL MIX OPEN FOUNDATION BE LIABLE TO ANY PARTY FOR DIRECT, INDIRECT, SPECIAL, INCIDENTAL, OR CONSEQUENTIAL DAMAGES, INCLUDING LOST PROFITS, ARISING OUT OF THE USE OF THIS SOFTWARE AND ITS DOCUMENTATION, EVEN IF MIX OPEN FOUNDATION HAS BEEN ADVISED OF THE POSSIBILITY OF SUCH DAMAGE.

MIX OPEN FOUNDATION SPECIFICALLY DISCLAIMS ANY WARRANTIES, INCLUDING, BUT NOT LIMITED TO, THE IMPLIED WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE. THE SOFTWARE PROVIDED HEREUNDER IS ON AN “AS IS” BASIS, AND MIX OPEN FOUNDATION HAS NO OBLIGATIONS TO PROVIDE MAINTENANCE, SUPPORT, UPDATES, ENHANCEMENTS, OR MODIFICATIONS. ***********************************************************************************/

CREATE SCHEMA IF NOT EXISTS assert; CREATE SCHEMA IF NOT EXISTS unit_tests;

DO $$ BEGIN

IF NOT EXISTS 
(
    SELECT * FROM pg_type
    WHERE 
        typname ='test_result'
    AND 
        typnamespace = 
        (
            SELECT oid FROM pg_namespace 
            WHERE nspname ='public'
        )
) THEN
    CREATE DOMAIN public.test_result AS text;
END IF;

END $$ LANGUAGE plpgsql;

DROP TABLE IF EXISTS unit_tests.test_details CASCADE; DROP TABLE IF EXISTS unit_tests.tests CASCADE; DROP TABLE IF EXISTS unit_tests.dependencies CASCADE; CREATE TABLE unit_tests.tests (

test_id                                 SERIAL NOT NULL PRIMARY KEY,
started_on                              TIMESTAMP WITHOUT TIME ZONE NOT NULL DEFAULT(CURRENT_TIMESTAMP AT TIME ZONE 'UTC'),
completed_on                            TIMESTAMP WITHOUT TIME ZONE NULL,
total_tests                             integer NULL DEFAULT(0),
failed_tests                            integer NULL DEFAULT(0),
skipped_tests                           integer NULL DEFAULT(0)

);

CREATE INDEX unit_tests_tests_started_on_inx ON unit_tests.tests(started_on);

CREATE INDEX unit_tests_tests_completed_on_inx ON unit_tests.tests(completed_on);

CREATE INDEX unit_tests_tests_failed_tests_inx ON unit_tests.tests(failed_tests);

CREATE TABLE unit_tests.test_details (

id                                      BIGSERIAL NOT NULL PRIMARY KEY,
test_id                                 integer NOT NULL REFERENCES unit_tests.tests(test_id),
function_name                           text NOT NULL,
message                                 text NOT NULL,
ts                                      TIMESTAMP WITHOUT TIME ZONE NOT NULL DEFAULT(CURRENT_TIMESTAMP AT TIME ZONE 'UTC'),
status                                  boolean NOT NULL,
executed                                boolean NOT NULL

);

CREATE INDEX unit_tests_test_details_test_id_inx ON unit_tests.test_details(test_id);

CREATE INDEX unit_tests_test_details_status_inx ON unit_tests.test_details(status);

CREATE TABLE unit_tests.dependencies (

dependency_id                           BIGSERIAL NOT NULL PRIMARY KEY,
dependent_ns                            text,
dependent_function_name                 text NOT NULL,
depends_on_ns                           text,
depends_on_function_name                text NOT NULL

);

CREATE INDEX unit_tests_dependencies_dependency_id_inx ON unit_tests.dependencies(dependency_id);

DROP FUNCTION IF EXISTS assert.fail(message text); CREATE FUNCTION assert.fail(message text) RETURNS text AS $$ BEGIN

IF $1 IS NULL OR trim($1) = '' THEN
    message := 'NO REASON SPECIFIED';
END IF;

RAISE WARNING 'ASSERT FAILED : %', message;
RETURN message;

END $$ LANGUAGE plpgsql IMMUTABLE STRICT;

DROP FUNCTION IF EXISTS assert.pass(message text); CREATE FUNCTION assert.pass(message text) RETURNS text AS $$ BEGIN

RAISE NOTICE 'ASSERT PASSED : %', message;
RETURN '';

END $$ LANGUAGE plpgsql IMMUTABLE STRICT;

DROP FUNCTION IF EXISTS assert.ok(message text); CREATE FUNCTION assert.ok(message text) RETURNS text AS $$ BEGIN

RAISE NOTICE 'OK : %', message;
RETURN '';

END $$ LANGUAGE plpgsql IMMUTABLE STRICT;

DROP FUNCTION IF EXISTS assert.is_equal(IN have anyelement, IN want anyelement, OUT message text, OUT result boolean); CREATE FUNCTION assert.is_equal(IN have anyelement, IN want anyelement, OUT message text, OUT result boolean) AS $$ BEGIN

IF($1 IS NOT DISTINCT FROM $2) THEN
    message := 'Assert is equal.';
    PERFORM assert.ok(message);
    result := true;
    RETURN;
END IF;

message := E'ASSERT IS_EQUAL FAILED.\n\nHave -> ' || COALESCE($1::text, 'NULL') || E'\nWant -> ' || COALESCE($2::text, 'NULL') || E'\n';    
PERFORM assert.fail(message);
result := false;
RETURN;

END $$ LANGUAGE plpgsql IMMUTABLE;

DROP FUNCTION IF EXISTS assert.are_equal(VARIADIC anyarray, OUT message text, OUT result boolean); CREATE FUNCTION assert.are_equal(VARIADIC anyarray, OUT message text, OUT result boolean) AS $$

DECLARE count integer=0;
DECLARE total_items bigint;
DECLARE total_rows bigint;

BEGIN

result := false;

WITH counter
AS
(
    SELECT *
    FROM explode_array($1) AS items
)
SELECT
    COUNT(items),
    COUNT(*)
INTO
    total_items,
    total_rows
FROM counter;

IF(total_items = 0 OR total_items = total_rows) THEN
    result := true;
END IF;

IF(result AND total_items > 0) THEN
    SELECT COUNT(DISTINCT $1[s.i])
    INTO count
    FROM generate_series(array_lower($1,1), array_upper($1,1)) AS s(i)
    ORDER BY 1;

    IF count <> 1 THEN
        result := FALSE;
    END IF;
END IF;

IF(NOT result) THEN
    message := 'ASSERT ARE_EQUAL FAILED.';  
    PERFORM assert.fail(message);
    RETURN;
END IF;

message := 'Asserts are equal.';
PERFORM assert.ok(message);
result := true;
RETURN;

END $$ LANGUAGE plpgsql IMMUTABLE;

DROP FUNCTION IF EXISTS assert.is_not_equal(IN already_have anyelement, IN dont_want anyelement, OUT message text, OUT result boolean); CREATE FUNCTION assert.is_not_equal(IN already_have anyelement, IN dont_want anyelement, OUT message text, OUT result boolean) AS $$ BEGIN

IF($1 IS DISTINCT FROM $2) THEN
    message := 'Assert is not equal.';
    PERFORM assert.ok(message);
    result := true;
    RETURN;
END IF;

message := E'ASSERT IS_NOT_EQUAL FAILED.\n\nAlready Have -> ' || COALESCE($1::text, 'NULL') || E'\nDon''t Want   -> ' || COALESCE($2::text, 'NULL') || E'\n';   
PERFORM assert.fail(message);
result := false;
RETURN;

END $$ LANGUAGE plpgsql IMMUTABLE;

DROP FUNCTION IF EXISTS assert.are_not_equal(VARIADIC anyarray, OUT message text, OUT result boolean); CREATE FUNCTION assert.are_not_equal(VARIADIC anyarray, OUT message text, OUT result boolean) AS $$

DECLARE count integer=0;
DECLARE count_nulls bigint;

BEGIN

SELECT COUNT(*)
INTO count_nulls
FROM explode_array($1) AS items
WHERE items IS NULL;

SELECT COUNT(DISTINCT $1[s.i]) INTO count
FROM generate_series(array_lower($1,1), array_upper($1,1)) AS s(i)
ORDER BY 1;

IF(count + count_nulls <> array_upper($1,1) OR count_nulls > 1) THEN
    message := 'ASSERT ARE_NOT_EQUAL FAILED.';  
    PERFORM assert.fail(message);
    RESULT := FALSE;
    RETURN;
END IF;

message := 'Asserts are not equal.';
PERFORM assert.ok(message);
result := true;
RETURN;

END $$ LANGUAGE plpgsql IMMUTABLE;

DROP FUNCTION IF EXISTS assert.is_null(IN anyelement, OUT message text, OUT result boolean); CREATE FUNCTION assert.is_null(IN anyelement, OUT message text, OUT result boolean) AS $$ BEGIN

IF($1 IS NULL) THEN
    message := 'Assert is NULL.';
    PERFORM assert.ok(message);
    result := true;
    RETURN;
END IF;

message := E'ASSERT IS_NULL FAILED. NULL value was expected.\n\n\n';    
PERFORM assert.fail(message);
result := false;
RETURN;

END $$ LANGUAGE plpgsql IMMUTABLE;

DROP FUNCTION IF EXISTS assert.is_not_null(IN anyelement, OUT message text, OUT result boolean); CREATE FUNCTION assert.is_not_null(IN anyelement, OUT message text, OUT result boolean) AS $$ BEGIN

IF($1 IS NOT NULL) THEN
    message := 'Assert is not NULL.';
    PERFORM assert.ok(message);
    result := true;
    RETURN;
END IF;

message := E'ASSERT IS_NOT_NULL FAILED. The value is NULL.\n\n\n';  
PERFORM assert.fail(message);
result := false;
RETURN;

END $$ LANGUAGE plpgsql IMMUTABLE;

DROP FUNCTION IF EXISTS assert.is_true(IN boolean, OUT message text, OUT result boolean); CREATE FUNCTION assert.is_true(IN boolean, OUT message text, OUT result boolean) AS $$ BEGIN

IF($1) THEN
    message := 'Assert is true.';
    PERFORM assert.ok(message);
    result := true;
    RETURN;
END IF;

message := E'ASSERT IS_TRUE FAILED. A true condition was expected.\n\n\n';  
PERFORM assert.fail(message);
result := false;
RETURN;

END $$ LANGUAGE plpgsql IMMUTABLE;

DROP FUNCTION IF EXISTS assert.is_false(IN boolean, OUT message text, OUT result boolean); CREATE FUNCTION assert.is_false(IN boolean, OUT message text, OUT result boolean) AS $$ BEGIN

IF(NOT $1) THEN
    message := 'Assert is false.';
    PERFORM assert.ok(message);
    result := true;
    RETURN;
END IF;

message := E'ASSERT IS_FALSE FAILED. A false condition was expected.\n\n\n';    
PERFORM assert.fail(message);
result := false;
RETURN;

END $$ LANGUAGE plpgsql IMMUTABLE;

DROP FUNCTION IF EXISTS assert.is_greater_than(IN x anyelement, IN y anyelement, OUT message text, OUT result boolean); CREATE FUNCTION assert.is_greater_than(IN x anyelement, IN y anyelement, OUT message text, OUT result boolean) AS $$ BEGIN

IF($1 > $2) THEN
    message := 'Assert greater than condition is satisfied.';
    PERFORM assert.ok(message);
    result := true;
    RETURN;
END IF;

message := E'ASSERT IS_GREATER_THAN FAILED.\n\n X : -> ' || COALESCE($1::text, 'NULL') || E'\n is not greater than Y:   -> ' || COALESCE($2::text, 'NULL') || E'\n';    
PERFORM assert.fail(message);
result := false;
RETURN;

END $$ LANGUAGE plpgsql IMMUTABLE;

DROP FUNCTION IF EXISTS assert.is_less_than(IN x anyelement, IN y anyelement, OUT message text, OUT result boolean); CREATE FUNCTION assert.is_less_than(IN x anyelement, IN y anyelement, OUT message text, OUT result boolean) AS $$ BEGIN

IF($1 < $2) THEN
    message := 'Assert less than condition is satisfied.';
    PERFORM assert.ok(message);
    result := true;
    RETURN;
END IF;

message := E'ASSERT IS_LESS_THAN FAILED.\n\n X : -> ' || COALESCE($1::text, 'NULL') || E'\n is not less than Y:   -> ' || COALESCE($2::text, 'NULL') || E'\n';  
PERFORM assert.fail(message);
result := false;
RETURN;

END $$ LANGUAGE plpgsql IMMUTABLE;

DROP FUNCTION IF EXISTS assert.function_exists(function_name text, OUT message text, OUT result boolean); CREATE FUNCTION assert.function_exists(function_name text, OUT message text, OUT result boolean) AS $$ BEGIN

IF NOT EXISTS
(
    SELECT  1
    FROM    pg_catalog.pg_namespace n
    JOIN    pg_catalog.pg_proc p
    ON      pronamespace = n.oid
    WHERE replace(nspname || '.' || proname || '(' || oidvectortypes(proargtypes) || ')', ' ' , '')::text=$1
) THEN
    message := format('The function %s does not exist.', $1);
    PERFORM assert.fail(message);

    result := false;
    RETURN;
END IF;

message := format('Ok. The function %s exists.', $1);
PERFORM assert.ok(message);
result := true;
RETURN;

END $$ LANGUAGE plpgsql;

DROP FUNCTION IF EXISTS assert.if_functions_compile(VARIADIC _schema_name text[], OUT message text, OUT result boolean); CREATE OR REPLACE FUNCTION assert.if_functions_compile (

VARIADIC _schema_name text[],
OUT message text, 
OUT result boolean

) AS $$

DECLARE all_parameters              text;
DECLARE current_function            RECORD;
DECLARE current_function_name       text;
DECLARE current_type                text;
DECLARE current_type_schema         text;
DECLARE current_parameter           text;
DECLARE functions_count             smallint := 0;
DECLARE current_parameters_count    int;
DECLARE i                           int;
DECLARE command_text                text;
DECLARE failed_functions            text;

BEGIN

FOR current_function IN 
    SELECT proname, proargtypes, nspname 
    FROM pg_proc 
    INNER JOIN pg_namespace 
    ON pg_proc.pronamespace = pg_namespace.oid 
    WHERE pronamespace IN 
    (
        SELECT oid FROM pg_namespace 
        WHERE nspname = ANY($1) 
        AND nspname NOT IN
        (
            'assert', 'unit_tests', 'information_schema'
        ) 
        AND proname NOT IN('if_functions_compile')
    ) 
LOOP
    current_parameters_count := array_upper(current_function.proargtypes, 1) + 1;

    i := 0;
    all_parameters := '';

    LOOP
    IF i < current_parameters_count THEN
        IF i > 0 THEN
            all_parameters := all_parameters || ', ';
        END IF;

        SELECT 
            nspname, typname 
        INTO 
            current_type_schema, current_type 
        FROM pg_type 
        INNER JOIN pg_namespace 
        ON pg_type.typnamespace = pg_namespace.oid
        WHERE pg_type.oid = current_function.proargtypes[i];

        IF(current_type IN('int4', 'int8', 'numeric', 'integer_strict', 'money_strict','decimal_strict', 'integer_strict2', 'money_strict2','decimal_strict2', 'money','decimal', 'numeric', 'bigint')) THEN
            current_parameter := '1::' || current_type_schema || '.' || current_type;
        ELSIF(substring(current_type, 1, 1) = '_') THEN
            current_parameter := 'NULL::' || current_type_schema || '.' || substring(current_type, 2, length(current_type)) || '[]';
        ELSIF(current_type in ('date')) THEN            
            current_parameter := '''1-1-2000''::' || current_type;
        ELSIF(current_type = 'bool') THEN
            current_parameter := 'false';            
        ELSE
            current_parameter := '''''::' || quote_ident(current_type_schema) || '.' || quote_ident(current_type);
        END IF;

        all_parameters = all_parameters || current_parameter;

        i := i + 1;
    ELSE
        EXIT;
    END IF;
END LOOP;

BEGIN
    current_function_name := quote_ident(current_function.nspname)  || '.' || quote_ident(current_function.proname);
    command_text := 'SELECT * FROM ' || current_function_name || '(' || all_parameters || ');';

    EXECUTE command_text;
    functions_count := functions_count + 1;

    EXCEPTION WHEN OTHERS THEN
        IF(failed_functions IS NULL) THEN 
            failed_functions := '';
        END IF;

        IF(SQLSTATE IN('42702', '42704')) THEN
            failed_functions := failed_functions || E'\n' || command_text || E'\n' || SQLERRM || E'\n';                
        END IF;
END;

END LOOP;

IF(failed_functions != '') THEN
    message := E'The test if_functions_compile failed. The following functions failed to compile : \n\n' || failed_functions;
    result := false;
    PERFORM assert.fail(message);
    RETURN;
END IF;

END; $$ LANGUAGE plpgsql VOLATILE;

DROP FUNCTION IF EXISTS assert.if_views_compile(VARIADIC _schema_name text[], OUT message text, OUT result boolean); CREATE FUNCTION assert.if_views_compile (

VARIADIC _schema_name text[],
OUT message text, 
OUT result boolean

) AS $$

DECLARE message                     test_result;
DECLARE current_view                RECORD;
DECLARE current_view_name           text;
DECLARE command_text                text;
DECLARE failed_views                text;

BEGIN

FOR current_view IN 
    SELECT table_name, table_schema 
    FROM information_schema.views
    WHERE table_schema = ANY($1) 
LOOP

BEGIN
    current_view_name := quote_ident(current_view.table_schema)  || '.' || quote_ident(current_view.table_name);
    command_text := 'SELECT * FROM ' || current_view_name || ' LIMIT 1;';

    RAISE NOTICE '%', command_text;

    EXECUTE command_text;

    EXCEPTION WHEN OTHERS THEN
        IF(failed_views IS NULL) THEN 
            failed_views := '';
        END IF;

        failed_views := failed_views || E'\n' || command_text || E'\n' || SQLERRM || E'\n';                
END;

END LOOP;

IF(failed_views != '') THEN
    message := E'The test if_views_compile failed. The following views failed to compile : \n\n' || failed_views;
    result := false;
    PERFORM assert.fail(message);
    RETURN;
END IF;

RETURN;

END; $$ LANGUAGE plpgsql VOLATILE;

DROP FUNCTION IF EXISTS unit_tests.add_dependency(p_dependent text, p_depends_on text); CREATE FUNCTION unit_tests.add_dependency(p_dependent text, p_depends_on text)

RETURNS void

AS

$$
  DECLARE dependent_ns text;
  DECLARE dependent_name text;
  DECLARE depends_on_ns text;
  DECLARE depends_on_name text;
  DECLARE arr text[];

BEGIN

IF p_dependent LIKE '%.%' THEN
    SELECT regexp_split_to_array(p_dependent, E'\\.') INTO arr;
    SELECT arr[1] INTO dependent_ns;
    SELECT arr[2] INTO dependent_name;
ELSE
    SELECT NULL INTO dependent_ns;
    SELECT p_dependent INTO dependent_name;
END IF;
IF p_depends_on LIKE '%.%' THEN
    SELECT regexp_split_to_array(p_depends_on, E'\\.') INTO arr;
    SELECT arr[1] INTO depends_on_ns;
    SELECT arr[2] INTO depends_on_name;
ELSE
    SELECT NULL INTO depends_on_ns;
    SELECT p_depends_on INTO depends_on_name;
END IF;
INSERT INTO unit_tests.dependencies (dependent_ns, dependent_function_name, depends_on_ns, depends_on_function_name)
VALUES (dependent_ns, dependent_name, depends_on_ns, depends_on_name);

END $$ LANGUAGE plpgsql STRICT;

DROP FUNCTION IF EXISTS unit_tests.begin(verbosity integer, format text); CREATE FUNCTION unit_tests.begin(verbosity integer DEFAULT 9, format text DEFAULT '') RETURNS TABLE(message text, result character(1)) AS $$

DECLARE this                    record;
DECLARE _function_name          text;
DECLARE _sql                    text;
DECLARE _failed_dependencies    text[];
DECLARE _num_of_test_functions  integer;
DECLARE _should_skip            boolean;
DECLARE _message                text;
DECLARE _error                  text;
DECLARE _context                text;
DECLARE _result                 character(1);
DECLARE _test_id                integer;
DECLARE _status                 boolean;
DECLARE _total_tests            integer                         = 0;
DECLARE _failed_tests           integer                         = 0;
DECLARE _skipped_tests          integer                         = 0;
DECLARE _list_of_failed_tests   text;
DECLARE _list_of_skipped_tests  text;
DECLARE _started_from           TIMESTAMP WITHOUT TIME ZONE;
DECLARE _completed_on           TIMESTAMP WITHOUT TIME ZONE;
DECLARE _delta                  integer;
DECLARE _ret_val                text                            = '';
DECLARE _verbosity              text[]                          =
                                ARRAY['debug5', 'debug4', 'debug3', 'debug2', 'debug1', 'log', 'notice', 'warning', 'error', 'fatal', 'panic'];

BEGIN

_started_from := clock_timestamp() AT TIME ZONE 'UTC';

IF(format='teamcity') THEN
    RAISE INFO '##teamcity[testSuiteStarted name=''Plpgunit'' message=''Test started from : %'']', _started_from;
ELSE
    RAISE INFO 'Test started from : %', _started_from;
END IF;

IF($1 > 11) THEN
    $1 := 9;
END IF;

EXECUTE 'SET CLIENT_MIN_MESSAGES TO ' || _verbosity[$1];
RAISE WARNING 'CLIENT_MIN_MESSAGES set to : %' , _verbosity[$1];

SELECT nextval('unit_tests.tests_test_id_seq') INTO _test_id;

INSERT INTO unit_tests.tests(test_id)
SELECT _test_id;

DROP TABLE IF EXISTS temp_test_functions;
CREATE TEMP TABLE temp_test_functions AS
SELECT
    nspname AS ns_name,
    proname AS function_name,
    p.oid as oid
FROM    pg_catalog.pg_namespace n
JOIN    pg_catalog.pg_proc p
ON      pronamespace = n.oid
WHERE
    prorettype='test_result'::regtype::oid;

SELECT count(*) INTO _num_of_test_functions FROM temp_test_functions;

DROP TABLE IF EXISTS temp_dependency_levels;
CREATE TEMP TABLE temp_dependency_levels AS
WITH RECURSIVE dependency_levels(ns_name, function_name, oid, level) AS (
  -- select functions without any dependencies
  SELECT ns_name, function_name, tf.oid, 0 as level
  FROM temp_test_functions tf
  LEFT OUTER JOIN unit_tests.dependencies d ON tf.ns_name = d.dependent_ns AND tf.function_name = d.dependent_function_name
  WHERE d.dependency_id IS NULL
  UNION
  -- add functions which depend on the previous level functions
  SELECT d.dependent_ns, d.dependent_function_name, tf.oid, level + 1
  FROM dependency_levels dl
  JOIN unit_tests.dependencies d ON dl.ns_name = d.depends_on_ns AND dl.function_name LIKE d.depends_on_function_name
  JOIN temp_test_functions tf ON d.dependent_ns = tf.ns_name AND d.dependent_function_name = tf.function_name
  WHERE level < _num_of_test_functions -- don't follow circles for too long
  )
  SELECT ns_name, function_name, oid, max(level) as max_level
  FROM dependency_levels
  GROUP BY ns_name, function_name, oid;

IF (SELECT count(*) < _num_of_test_functions FROM temp_dependency_levels) THEN
  SELECT array_to_string(array_agg(tf.ns_name || '.' || tf.function_name || '()'), ', ')
  INTO _error
  FROM temp_test_functions tf
  LEFT OUTER JOIN temp_dependency_levels dl ON tf.oid = dl.oid
    WHERE dl.oid IS NULL;
  RAISE EXCEPTION 'Cyclic dependencies detected. Check the following test functions: %', _error;
END IF;

IF exists(SELECT * FROM temp_dependency_levels WHERE max_level = _num_of_test_functions) THEN
  SELECT array_to_string(array_agg(ns_name || '.' || function_name || '()'), ', ')
    INTO _error
    FROM temp_dependency_levels
    WHERE max_level = _num_of_test_functions;
  RAISE EXCEPTION 'Cyclic dependencies detected. Check the dependency graph including following test functions: %', _error;
END IF;

FOR this IN
  SELECT ns_name, function_name, max_level
    FROM temp_dependency_levels
    ORDER BY max_level, oid
LOOP
    BEGIN
        _status := false;
        _total_tests := _total_tests + 1;

        _function_name = this.ns_name|| '.' || this.function_name || '()';

        SELECT array_agg(td.function_name)
            INTO _failed_dependencies
            FROM unit_tests.dependencies d
            JOIN unit_tests.test_details td on td.function_name LIKE d.depends_on_ns || '.' || d.depends_on_function_name || '()'
            WHERE d.dependent_ns = this.ns_name AND d.dependent_function_name = this.function_name
            AND test_id = _test_id AND status = false;

        SELECT _failed_dependencies IS NOT NULL INTO _should_skip;
        IF NOT _should_skip THEN
            _sql := 'SELECT ' || _function_name || ';';

            RAISE NOTICE 'RUNNING TEST : %.', _function_name;

            IF(format='teamcity') THEN
                RAISE INFO '##teamcity[testStarted name=''%'' message=''%'']', _function_name, _started_from;
            ELSE
                RAISE INFO 'Running test % : %', _function_name, _started_from;
            END IF;

            EXECUTE _sql INTO _message;

            IF _message = '' THEN
                _status := true;

                IF(format='teamcity') THEN
                    RAISE INFO '##teamcity[testFinished name=''%'' message=''%'']', _function_name, clock_timestamp() AT TIME ZONE 'UTC';
                ELSE
                    RAISE INFO 'Passed % : %', _function_name, clock_timestamp() AT TIME ZONE 'UTC';
                END IF;
            ELSE
                IF(format='teamcity') THEN
                    RAISE INFO '##teamcity[testFailed name=''%'' message=''%'']', _function_name, _message;
                    RAISE INFO '##teamcity[testFinished name=''%'' message=''%'']', _function_name, clock_timestamp() AT TIME ZONE 'UTC';
                ELSE
                    RAISE INFO 'Test failed % : %', _function_name, _message;
                END IF;
            END IF;
        ELSE
            -- skipped test
            _status := true;
            _message = 'Failed dependencies: ' || array_to_string(_failed_dependencies, ',');
            IF(format='teamcity') THEN
                RAISE INFO '##teamcity[testSkipped name=''%''] : %', _function_name, clock_timestamp() AT TIME ZONE 'UTC';
            ELSE
                RAISE INFO 'Skipped % : %', _function_name, clock_timestamp() AT TIME ZONE 'UTC';
            END IF;
        END IF;

        INSERT INTO unit_tests.test_details(test_id, function_name, message, status, executed, ts)
        SELECT _test_id, _function_name, _message, _status, NOT _should_skip, clock_timestamp();

        IF NOT _status THEN
            _failed_tests := _failed_tests + 1;
            RAISE WARNING 'TEST % FAILED.', _function_name;
            RAISE WARNING 'REASON: %', _message;
        ELSIF NOT _should_skip THEN
            RAISE NOTICE 'TEST % COMPLETED WITHOUT ERRORS.', _function_name;
        ELSE
            _skipped_tests := _skipped_tests + 1;
            RAISE WARNING 'TEST % SKIPPED, BECAUSE A DEPENDENCY FAILED.', _function_name;
        END IF;

    EXCEPTION WHEN OTHERS THEN
        GET STACKED DIAGNOSTICS _context = PG_EXCEPTION_CONTEXT;
        _message := 'ERR: [' || SQLSTATE || ']: ' || SQLERRM || E'\n    ' || split_part(_context, E'\n', 1);
        INSERT INTO unit_tests.test_details(test_id, function_name, message, status, executed)
        SELECT _test_id, _function_name, _message, false, true;

        _failed_tests := _failed_tests + 1;

        RAISE WARNING 'TEST % FAILED.', _function_name;
        RAISE WARNING 'REASON: %', _message;

        IF(format='teamcity') THEN
            RAISE INFO '##teamcity[testFailed name=''%'' message=''%'']', _function_name, _message;
            RAISE INFO '##teamcity[testFinished name=''%'' message=''%'']', _function_name, clock_timestamp() AT TIME ZONE 'UTC';
        ELSE
            RAISE INFO 'Test failed % : %', _function_name, _message;
        END IF;
    END;
END LOOP;

_completed_on := clock_timestamp() AT TIME ZONE 'UTC';
_delta := extract(millisecond from _completed_on - _started_from)::integer;

UPDATE unit_tests.tests
SET total_tests = _total_tests, failed_tests = _failed_tests, skipped_tests = _skipped_tests, completed_on = _completed_on
WHERE test_id = _test_id;

IF format='junit' THEN
    SELECT
        '<?xml version="1.0" encoding="UTF-8"?>'||
        xmlelement
        (
            name testsuites,
            xmlelement
            (
                name                    testsuite,
                xmlattributes
                (
                    'plpgunit'          AS name,
                    t.total_tests       AS tests,
                    t.failed_tests      AS failures,
                    0                   AS errors,
                    EXTRACT
                    (
                        EPOCH FROM t.completed_on - t.started_on
                    )                   AS time
                ),
                xmlagg
                (
                    xmlelement
                    (
                        name testcase,
                        xmlattributes
                        (
                            td.function_name
                                        AS name,
                            EXTRACT
                            (
                                EPOCH FROM td.ts - t.started_on
                            )           AS time
                        ),
                        CASE
                            WHEN td.status=false
                            THEN
                                xmlelement
                                (
                                    name failure,
                                    td.message
                                )
                            END
                    )
                )
            )
        ) INTO _ret_val
    FROM unit_tests.test_details td, unit_tests.tests t
    WHERE
        t.test_id=_test_id
    AND
        td.test_id=t.test_id
    GROUP BY t.test_id;
ELSE
    WITH failed_tests AS
    (
        SELECT row_number() OVER (ORDER BY id) AS id,
            unit_tests.test_details.function_name,
            unit_tests.test_details.message
        FROM unit_tests.test_details
        WHERE test_id = _test_id
        AND status= false
    )
    SELECT array_to_string(array_agg(f.id::text || '. ' || f.function_name || ' --> ' || f.message), E'\n') INTO _list_of_failed_tests
    FROM failed_tests f;

    WITH skipped_tests AS
    (
        SELECT row_number() OVER (ORDER BY id) AS id,
            unit_tests.test_details.function_name,
            unit_tests.test_details.message
        FROM unit_tests.test_details
        WHERE test_id = _test_id
        AND executed = false
    )
    SELECT array_to_string(array_agg(s.id::text || '. ' || s.function_name || ' --> ' || s.message), E'\n') INTO _list_of_skipped_tests
    FROM skipped_tests s;

    _ret_val := _ret_val ||  'Test completed on : ' || _completed_on::text || E' UTC. \nTotal test runtime: ' || _delta::text || E' ms.\n';
    _ret_val := _ret_val || E'\nTotal tests run : ' || COALESCE(_total_tests, '0')::text;
    _ret_val := _ret_val || E'.\nPassed tests    : ' || (COALESCE(_total_tests, '0') - COALESCE(_failed_tests, '0') - COALESCE(_skipped_tests, '0'))::text;
    _ret_val := _ret_val || E'.\nFailed tests    : ' || COALESCE(_failed_tests, '0')::text;
    _ret_val := _ret_val || E'.\nSkipped tests   : ' || COALESCE(_skipped_tests, '0')::text;
    _ret_val := _ret_val || E'.\n\nList of failed tests:\n' || '----------------------';
    _ret_val := _ret_val || E'\n' || COALESCE(_list_of_failed_tests, '<NULL>')::text;
    _ret_val := _ret_val || E'.\n\nList of skipped tests:\n' || '----------------------';
    _ret_val := _ret_val || E'\n' || COALESCE(_list_of_skipped_tests, '<NULL>')::text;
    _ret_val := _ret_val || E'\n' || E'End of plpgunit test.\n\n';
END IF;

IF _failed_tests > 0 THEN
    _result := 'N';

    IF(format='teamcity') THEN
        RAISE INFO '##teamcity[testStarted name=''Result'']';
        RAISE INFO '##teamcity[testFailed name=''Result'' message=''%'']', REPLACE(_ret_val, E'\n', ' |n');
        RAISE INFO '##teamcity[testFinished name=''Result'']';
        RAISE INFO '##teamcity[testSuiteFinished name=''Plpgunit'' message=''%'']', REPLACE(_ret_val, E'\n', '|n');
    ELSE
        RAISE INFO '%', _ret_val;
    END IF;
ELSE
    _result := 'Y';

    IF(format='teamcity') THEN
        RAISE INFO '##teamcity[testSuiteFinished name=''Plpgunit'' message=''%'']', REPLACE(_ret_val, E'\n', '|n');
    ELSE
        RAISE INFO '%', _ret_val;
    END IF;
END IF;

SET CLIENT_MIN_MESSAGES TO notice;

RETURN QUERY SELECT _ret_val, _result;

END $$ LANGUAGE plpgsql;

DROP FUNCTION IF EXISTS unit_tests.begin_junit(verbosity integer); CREATE FUNCTION unit_tests.begin_junit(verbosity integer DEFAULT 9) RETURNS TABLE(message text, result character(1)) AS $$ BEGIN

RETURN QUERY 
SELECT * FROM unit_tests.begin($1, 'junit');

END $$ LANGUAGE plpgsql;

– version of begin that will raise if any tests have failed – this will cause psql to return nonzeo exit code so the build/script can be halted CREATE OR REPLACE FUNCTION unit_tests.begin_psql(verbosity integer default 9, format text default '') RETURNS VOID AS $$

DECLARE
    _msg text;
    _res character(1);
BEGIN
    SELECT * INTO _msg, _res
        FROM unit_tests.begin(verbosity, format)
    ;
    IF(_res != 'Y') THEN
        RAISE EXCEPTION 'Tests failed [%]', _msg;
    END IF;
END

$$ LANGUAGE plpgsql;