Postgres - Track changes-CodePudding

I want to track the history changes from my table, for that I created one history table, right now is saving old and new values, but I want to save in one column the fk from my regular table.

This is my history table:

CREATE TABLE IF NOT EXISTS sales.history (
    history_id integer PRIMARY KEY NOT NULL,
    mytable_id integer,
    table_name varchar(30),
    new_val json,
    old_val json,
    operation varchar(10),
    CONSTRAINT "fk sales.mytable_id to history.mytable_id" FOREIGN KEY (mytable_id) REFERENCES sales.sales (mytable_id)
) TABLESPACE pg_default;

I created a trigger to track update,insert and delete

CREATE TRIGGER t_history BEFORE INSERT OR UPDATE OR DELETE ON sales.sales
 
        FOR EACH ROW EXECUTE PROCEDURE core.func_store_history_changes();

Now my function

CREATE OR REPLACE FUNCTION core.func_store_history_changes() RETURNS trigger AS $$ 
        BEGIN 
                IF      TG_OP = 'INSERT'
                THEN
                        INSERT INTO sales.history (tabname, schemaname, operation, new_val)
                                VALUES (TG_RELNAME, TG_TABLE_SCHEMA, TG_OP, row_to_json(NEW));
                        RETURN NEW;
                ELSIF   TG_OP = 'UPDATE'
                THEN 
                        INSERT INTO sales.history (tabname, schemaname, operation, new_val, old_val)
 
                                VALUES (TG_RELNAME, TG_TABLE_SCHEMA, TG_OP,
 
                                        row_to_json(NEW), row_to_json(OLD));
                        RETURN NEW;
                ELSIF   TG_OP = 'DELETE'
                THEN
                        INSERT INTO sales.history (tabname, schemaname, operation, old_val)
                                VALUES (TG_RELNAME, TG_TABLE_SCHEMA, TG_OP, row_to_json(OLD));
                        RETURN OLD;
                END IF;
        END;
$$ LANGUAGE 'plpgsql' SECURITY DEFINER;

As you can see my history table has mytable_id which is a foreign key and primary key from sales table, how i can get that value and insert into my history table?

Regards

CodePudding user response：

For you specific problem, I guess that you could use NEW.mytable_id and use trigger AFTER INSERT ...

Here is a example, I've wrote only the first if (INSERT).

CREATE TABLE IF NOT EXISTS sales.history (
    history_id serial PRIMARY KEY NOT NULL,
    mytable_id integer,
    table_name varchar(30),
    new_val json,
    old_val json,
    operation varchar(10),
    CONSTRAINT "fk sales.mytable_id to history.mytable_id" FOREIGN KEY (mytable_id) REFERENCES sales.sales (mytable_id)
) TABLESPACE pg_default;


CREATE TRIGGER t_history AFTER INSERT OR UPDATE OR DELETE ON sales.sales
 
        FOR EACH ROW EXECUTE PROCEDURE core.func_store_history_changes();


CREATE OR REPLACE FUNCTION core.func_store_history_changes() RETURNS trigger AS $$ 
        BEGIN 
                IF      TG_OP = 'INSERT'
                THEN
                        INSERT INTO sales.history (table_name, operation,mytable_id, new_val)
                                VALUES (TG_RELNAME, TG_OP, NEW.mytable_id, row_to_json(NEW));
                        RETURN NEW;
                ELSIF   TG_OP = 'UPDATE'
                THEN 
                        INSERT INTO sales.history (tabname, schemaname, operation, new_val, old_val)
 
                                VALUES (TG_RELNAME, TG_TABLE_SCHEMA, TG_OP,
 
                                        row_to_json(NEW), row_to_json(OLD));
                        RETURN NEW;
                ELSIF   TG_OP = 'DELETE'
                THEN
                        INSERT INTO sales.history (tabname, schemaname, operation, old_val)
                                VALUES (TG_RELNAME, TG_TABLE_SCHEMA, TG_OP, row_to_json(OLD));
                        RETURN OLD;
                END IF;
        END;
$$ LANGUAGE 'plpgsql' SECURITY DEFINER;

Result:

# insert into sales.sales (value) values ('2.3');
INSERT 0 1
# select * from sales.sales;
 mytable_id | value 
------------ -------          
          5 |  2.30
(2 rows)

# select * from sales.history;
 history_id | mytable_id | table_name |            new_val            | old_val | operation 
------------ ------------ ------------ ------------------------------- --------- -----------          
          3 |          5 | sales      | {"mytable_id":5,"value":2.30} |         | INSERT
(2 rows)

But, maybe, you could try to use this generic trigger for any table.

https://github.com/2ndQuadrant/audit-trigger