Get Help

PostgreSQL

Results into a PostgreSQL table, or through a procedure — the scripts and what is specific to PostgreSQL.

When to use it

When results should be kept across runs and reported on — see Database outputs for what every database output does: one row per test as it finishes, columns matched by name, objects created when CAT may. This page has what is specific to PostgreSQL: the scripts and the procedure form. The target is a Postgres@1 data source.

How to set it 

Data sources:
- Name: DWH
  Provider: Postgres@1
  Connection string: "%DWH_CONNECTION_STRING%"

Output:
- Database: DWH
  Table: public.cat_test_result

or, calling a procedure for every result:

Output:
- Database: DWH
  Procedure: public.save_cat_test_result

The account needs INSERT on the table, or EXECUTE on the procedure. If the object does not exist and the account may create it, CAT creates it — the procedure together with a public.cat_test_result table.

What it looks like

One row per test, the columns of your table — see the rows of the sample run on Database outputs.

Details

Table

Any subset of these columns works; custom columns must be nullable or have a default. The table may live in any schema — name it in Table.

CREATE TABLE public.cat_test_result
(
    test_result_id INT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    execution_guid UUID NOT NULL,
    project VARCHAR(200) NOT NULL,
    full_name VARCHAR(700) NOT NULL,
    test_result VARCHAR(30) NOT NULL,
    started_on TIMESTAMP NOT NULL,
    finished_on TIMESTAMP NOT NULL,
    raw_message TEXT NULL,
    exception TEXT NULL,
    description TEXT NULL,
    first_data_source VARCHAR(200) NOT NULL,
    first_query TEXT NOT NULL,
    second_data_source VARCHAR(200) NULL,
    second_query TEXT NULL,
    expectation VARCHAR(50) NOT NULL,
    key VARCHAR(200) NULL,
    metadata VARCHAR(300) NULL,
    tags VARCHAR(1000) NULL,
    suite VARCHAR(200) NULL,
    test_case VARCHAR(200) NULL,
    "order" INT NULL,
    "name" VARCHAR(300) NULL,
    timeout INT NULL,
    maximum_errors_logged INT NULL,
    thread_number INT NOT NULL,
    message TEXT NULL,
    number_of_errors INT NULL,
    log_number_of_errors BOOLEAN NULL
);

Procedure

Called with CALL; parameters matched by name (optionally prefixed C_), any subset — CAT reads the procedure’s parameters before the run and passes only those.

create procedure public.save_cat_test_result
(
    c_execution_guid UUID,
    c_project VARCHAR(200),
    c_full_name VARCHAR(700),
    c_test_result VARCHAR(30),
    c_started_on TIMESTAMP,
    c_finished_on TIMESTAMP,
    c_raw_message TEXT,
    c_exception TEXT,
    c_description TEXT,
    c_first_data_source VARCHAR(200),
    c_first_query TEXT,
    c_second_data_source VARCHAR(200),
    c_second_query TEXT,
    c_expectation VARCHAR(50),
    c_key VARCHAR(200),
    c_metadata VARCHAR(300),
    c_tags VARCHAR(1000),
    c_suite VARCHAR(200),
    c_test_case VARCHAR(200),
    c_order INT,
    c_name VARCHAR(300),
    c_timeout INT,
    c_maximum_errors_logged INT,
    c_thread_number INT,
    c_message TEXT,
    c_number_of_errors INT,
    c_log_number_of_errors BOOLEAN
)
language plpgsql
as
$$
begin
    insert into "public"."cat_test_result"
    (
        execution_guid, project, full_name, test_result, started_on, finished_on, raw_message, exception, description,
        first_data_source, first_query, second_data_source, second_query, expectation,
        "key", "metadata", tags, suite, test_case, "order", "name",
        "timeout", maximum_errors_logged, thread_number, "message", number_of_errors, log_number_of_errors
    )
    values (
        c_execution_guid, c_project, c_full_name, c_test_result, c_started_on, c_finished_on, c_raw_message, c_exception, c_description,
        c_first_data_source, c_first_query, c_second_data_source, c_second_query, c_expectation,
        c_key, c_metadata, c_tags, c_suite, c_test_case, c_order, c_name,
        c_timeout, c_maximum_errors_logged, c_thread_number, c_message, c_number_of_errors, c_log_number_of_errors
    );
end
$$;

Specifics

  • Each thread of the run holds its own connection; results are written as the tests finish.
  • started_on and finished_on are TIMESTAMP, UTC; execution_guid is a UUID; the long texts are TEXT.
← SQL Server
Oracle →