Soft deletes vs. Journal tables

Örnek

create_journal_table() metodu şöyledir. Bu metod EVENT TRIGGER HANDLER içindir.

CREATE OR REPLACE FUNCTION create_journal_table()
RETURNS event_trigger
LANGUAGE plpgsql
AS $$
DECLARE
    obj record;
    schema_name text;
    table_name text;
    journal_table_name text;
BEGIN
    FOR obj IN
        SELECT * FROM pg_event_trigger_ddl_commands()
    LOOP
        IF obj.command_tag = 'CREATE TABLE' AND obj.object_type = 'table' THEN
            schema_name := split_part(obj.object_identity, '.', 1);
            table_name  := split_part(obj.object_identity, '.', 2);

            schema_name := trim(both '"' from schema_name);
            table_name  := trim(both '"' from table_name);

            -- Skip journal tables to avoid recursion
            IF right(table_name, 8) = '_journal' THEN
                CONTINUE;
            END IF;

            journal_table_name := format('%I.%I_journal', schema_name, table_name);

            EXECUTE format('CREATE TABLE IF NOT EXISTS %s (
                id bigserial PRIMARY KEY,
                action text NOT NULL,
                changed_at timestamptz DEFAULT now(),
                old_data jsonb,
                new_data jsonb
            )', journal_table_name);

            EXECUTE format(
                'CREATE TRIGGER %I_to_journal
                 AFTER INSERT OR UPDATE OR DELETE ON %I.%I
                 FOR EACH ROW
                 EXECUTE FUNCTION log_to_journal()',
                table_name, schema_name, table_name
            );
        END IF;
    END LOOP;
END;
$$;  

Bu metodu register ederiz. Metod şöyledir. Bu metod CREATE EVENT TRIGGER içindir.

CREATE EVENT TRIGGER create_journal_after_table
ON ddl_command_end
WHEN TAG IN ('CREATE TABLE')
EXECUTE FUNCTION create_journal_table();

log_to_journal() metodu şöyledir. Bu metod row level logging yapar

CREATE OR REPLACE FUNCTION log_to_journal()
RETURNS trigger
LANGUAGE plpgsql
AS $$
DECLARE
    journal_table text;
BEGIN
    journal_table := format('%I.%I_journal', TG_TABLE_SCHEMA, TG_TABLE_NAME);

    IF TG_OP = 'INSERT' THEN
        EXECUTE format(
            'INSERT INTO %s (action, old_data, new_data) VALUES ($1, $2::jsonb, $3::jsonb)',
            journal_table
        )
        USING TG_OP, NULL, to_jsonb(NEW);

    ELSIF TG_OP = 'UPDATE' THEN
        EXECUTE format(
            'INSERT INTO %s (action, old_data, new_data) VALUES ($1, $2::jsonb, $3::jsonb)',
            journal_table
        )
        USING TG_OP, to_jsonb(OLD), to_jsonb(NEW);

    ELSIF TG_OP = 'DELETE' THEN
        EXECUTE format(
            'INSERT INTO %s (action, old_data, new_data) VALUES ($1, $2::jsonb, $3::jsonb)',
            journal_table
        )
        USING TG_OP, to_jsonb(OLD), NULL;
    END IF;

    RETURN NULL;  -- AFTER trigger
END;
$$;