 SELECT *
 FROM tx_history;
 
-

CREATE EXTENSION IF NOT EXISTS tuid;

DROP TABLE IF EXISTS tx_history CASCADE;
DROP TABLE IF EXISTS test CASCADE;

BEGIN
  -- history of table data for test
  EXECUTE FORMAT('
  DROP TABLE IF EXISTS %I.%I;
  CREATE TABLE %I.%I (
    entry JSONB
  ) INHERITS (%I.tx_history);',
    schema_name, table_name || '_h' ,
    schema_name, table_name || '_h',
    schema_name);

  EXECUTE FORMAT('
    CREATE INDEX %I ON %I.%I (id, entry);
  ',
    table_name || '_hi', schema_name, table_name || '_h'
  );

  -- don't allow any edits to history
  EXECUTE FORMAT('
    DROP TRIGGER IF EXISTS %I ON %I.%I;
    CREATE TRIGGER %I
    BEFORE UPDATE OR DELETE OR TRUNCATE
    ON %I.%I
    EXECUTE PROCEDURE prevent_change();
  ', table_name || '_no', schema_name, table_name || '_h', table_name || '_no', schema_name, table_name || '_h');

  EXECUTE FORMAT($FUNC$
  DROP FUNCTION IF EXISTS %I.%I();
  CREATE OR REPLACE FUNCTION %I.%I()
    RETURNS TRIGGER
  LANGUAGE plpgsql
  AS $X$
  DECLARE
    who VARCHAR;
    txid BIGINT;
  BEGIN
    SELECT current_setting('audit.user')
    INTO who;
    IF who IS NULL OR who = ''
    THEN
      RAISE EXCEPTION 'audit.user is not set.';
    END IF;

    txid = txid_current_if_assigned();
    IF txid IS NOT NULL
    THEN
      txid = TXID_SNAPSHOT_XMIN(txid_current_snapshot());
    ELSE
      txid = txid_current();
    END IF;

    IF tg_op = 'UPDATE'
    THEN
      IF (OLD.%I != NEW.%I)
      THEN
        RAISE EXCEPTION 'id cannot be changed';
      END IF;

      INSERT INTO %I.%I (id, table_schema, table_name, txid, who, op, entry) VALUES (NEW.%I, TG_TABLE_SCHEMA, TG_TABLE_NAME, txid, who, 'U', to_jsonb(NEW));
      RETURN NEW;
    END IF;

    IF tg_op = 'INSERT'
    THEN
      INSERT INTO %I.%I (id, table_schema, table_name, txid, who, op, entry) VALUES (NEW.%I, TG_TABLE_SCHEMA, TG_TABLE_NAME, txid, who, 'I', to_jsonb(NEW));
      RETURN NEW;
    END IF;

    IF tg_op = 'DELETE'
    THEN
      INSERT INTO %I.%I (id, table_schema, table_name, txid, who, op, entry) VALUES (OLD.%I, TG_TABLE_SCHEMA, TG_TABLE_NAME, txid, who, 'D', to_jsonb(OLD));
      RETURN OLD;
    END IF;

    RETURN NULL;
  END;
  $X$;
  $FUNC$,
    schema_name, table_name || '_htrig',
    schema_name, table_name || '_htrig',
    id_column_name, id_column_name,
    schema_name, table_name || '_h', id_column_name,
    schema_name, table_name || '_h', id_column_name,
    schema_name, table_name || '_h', id_column_name
  );

  -- hook up the trigger
  EXECUTE FORMAT('
  DROP TRIGGER IF EXISTS %I ON %I.%I;
  CREATE TRIGGER %I
    BEFORE UPDATE OR DELETE OR INSERT
    ON %I.%I
    FOR EACH ROW EXECUTE PROCEDURE %I.%I();
  ', table_name || '_tracked', schema_name, table_name || '_htrig',
    table_name || '_tracked',
    schema_name, table_name,
    schema_name, table_name || '_htrig');
END;
$$;

