Creating and inserting into audit table. WIP

audit_table_slow_dynamic.sql

-- psql -h localhost -U postgres
-- CREATE DATABASE test_db;
-- \c test_db

CREATE OR REPLACE FUNCTION create_or_update(schema_name_val varchar, tabel_name_val varchar) RETURNS void AS
$$
DECLARE
    log_trigger_name      varchar = 'log_trigger_' || tabel_name_val;
    full_table_name       varchar = schema_name_val || '.' || tabel_name_val;
    audit_table_name      varchar = tabel_name_val || '_log';
    audit_schema_name     varchar = schema_name_val || '_audit';
    full_audit_table_name varchar = audit_schema_name || '.' || audit_table_name;
    create_audit_table    varchar= '';
    create_trigger        varchar='';
BEGIN
    DROP trigger if exists log_trigger_name on full_table_name;

    IF NOT audit_table_present(audit_schema_name, audit_table_name)
    THEN
        create_audit_table = 'CREATE TABLE ' || full_audit_table_name || ' (log_id serial); ';
    END IF;

    create_trigger = format('CREATE TRIGGER %I
        BEFORE INSERT OR UPDATE OR DELETE
        ON %I
        FOR EACH ROW
    EXECUTE PROCEDURE change_trigger(%L, %L); ', log_trigger_name, tabel_name_val, audit_schema_name, audit_table_name);

    EXECUTE (create_audit_table || create_trigger);

END;
$$ LANGUAGE 'plpgsql';

-- Trigger create START
CREATE OR REPLACE FUNCTION change_trigger() RETURNS trigger AS
$$
DECLARE
    audit_schema_name     varchar = TG_ARGV[0];
    audit_table_name      varchar = TG_ARGV[1];
    columns               varchar = '';
    values                varchar = '';
    full_audit_table_name varchar = (audit_schema_name || '.' || audit_table_name);
    alter_columns_clauses varchar = '';
BEGIN
    IF TG_OP IN ('INSERT', 'UPDATE')
    THEN
        declare
            column_name   text;
            val           text;
            delimiter_val varchar = '';
            column_type   varchar;
        begin
            for column_name, val in
                select * from json_each_text(row_to_json(NEW))
                loop
                    delimiter_val = delimiter(columns);
                    column_type = column_type(TG_TABLE_SCHEMA, TG_TABLE_NAME, column_name);
                    columns = columns || delimiter_val || column_name;
                    values = values || value_with_type_cast(column_type, val, delimiter_val);
                    alter_columns_clauses = alter_columns_clauses ||
                                            add_columns_clause_if_missing(audit_schema_name, audit_table_name, column_name, column_type);
                end loop;
        end;
        EXECUTE alter_columns_clauses
                    || '; INSERT INTO ' || full_audit_table_name || ' (' || columns || ') VALUES (' || values || ');';
        RETURN NEW;
    END IF;
END;

$$ LANGUAGE 'plpgsql' SECURITY DEFINER;
-- Trigger create END


-- Helper functions start

CREATE OR REPLACE FUNCTION audit_table_present(schema_name_val varchar, tabel_name_val varchar) RETURNS bool AS
$$
BEGIN
    RETURN (select exists(
                           select
                           from information_schema.columns
                           where table_name = tabel_name_val
                             and table_schema = schema_name_val));
END;

$$ LANGUAGE 'plpgsql';
CREATE OR REPLACE FUNCTION add_columns_clause_if_missing(schema_name_val varchar, tabel_name_val varchar, column_name varchar,
                                                         column_type varchar) RETURNS varchar AS
$$
DECLARE
    full_history_table varchar = (schema_name_val || '.' || tabel_name_val);
BEGIN
    IF NOT history_table_does_have_column(schema_name_val, tabel_name_val, column_name)
    THEN
        RETURN add_column(full_history_table, column_name, column_type);
    END IF;
    RETURN '';
END;

$$ LANGUAGE 'plpgsql';

CREATE OR REPLACE FUNCTION add_column(full_history_table varchar, column_name varchar, column_type varchar) RETURNS varchar AS
$$
BEGIN
    return 'ALTER TABLE ' || full_history_table || ' ADD COLUMN ' || column_name || ' ' || column_type || ';';
END;

$$ LANGUAGE 'plpgsql';

CREATE OR REPLACE FUNCTION history_table_does_have_column(schema_name_val varchar, tabel_name_val name, column_name_val varchar) RETURNS boolean AS
$$
BEGIN
    RETURN (select exists(
                           select
                           from information_schema.columns
                           where column_name = column_name_val
                             and table_name = tabel_name_val
                             and table_schema = schema_name_val));
END;

$$ LANGUAGE 'plpgsql';
CREATE OR REPLACE FUNCTION delimiter(columns varchar) RETURNS varchar AS
$$
BEGIN
    IF (columns = '') THEN
        RETURN '';
    ELSE
        RETURN ', ';
    END IF;
END;

$$ LANGUAGE 'plpgsql';

CREATE OR REPLACE FUNCTION column_type(schema_name_val name, tabel_name_val name, column_name_val varchar) RETURNS varchar AS
$$
BEGIN
    RETURN (select udt_name
            from information_schema.columns
            where column_name = column_name_val
              and table_name = tabel_name_val
              and table_schema = schema_name_val);
END;
$$ LANGUAGE 'plpgsql';

CREATE OR REPLACE FUNCTION value_with_type_cast(column_type varchar, val varchar, delimiter_val varchar) RETURNS varchar AS
$$
BEGIN
    RETURN delimiter_val || E'\'' || val || E'\'::' || column_type;
END;
$$ LANGUAGE 'plpgsql';
-- Helper functions END