Postgresql trigger to write snapshots to a history table

versioning_trigger.sql

CREATE OR REPLACE FUNCTION versioning() RETURNS TRIGGER AS $$
    DECLARE
    hist_tbl text := TG_TABLE_NAME || '_history';

    BEGIN
        IF (TG_OP = 'UPDATE') THEN
            NEW.sys_period = tstzrange(CURRENT_TIMESTAMP, NULL);
            OLD.sys_period = tstzrange(lower(OLD.sys_period), CURRENT_TIMESTAMP);
            EXECUTE format('INSERT INTO %I SELECT ($1).*', hist_tbl) USING OLD;
            RETURN NEW;
        ELSIF (TG_OP = 'DELETE') THEN
            OLD.sys_period = tstzrange(lower(OLD.sys_period), CURRENT_TIMESTAMP);
            EXECUTE format('INSERT INTO %I SELECT ($1).*', hist_tbl) USING OLD;
            RETURN OLD;
        ELSE
            RETURN NULL;
        END IF;
    END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER versioning_trigger
    BEFORE UPDATE OR DELETE ON campaigns
    FOR EACH ROW
    EXECUTE PROCEDURE versioning();


-- Current version
SELECT * FROM campaigns WHERE id = 1;
-- Version at a point in time (@> is range contains)
SELECT * FROM campaigns_history WHERE id = 1 AND sys_period  @> '2020-11-13 14:54:10';
-- Previous version
SELECT * FROM campaigns_history WHERE id = 1 ORDER BY sys_period DESC LIMIT 1;
-- All versions for a date range (&& is range overlap)
SELECT * FROM campaigns_history WHERE id = 1 AND sys_period && tstzrange('2020-11-13 14:54:00', '2020-11-13 14:54:16');