Compare all values of OLD and NEW and store only changed values in audit table-CodePudding

My end user is going to enter query like this:

INSERT INTO course(
    id, column_1, column_2, column_3, column_4, column_5)
    VALUES ('f34-gr5-t46','ABC', '2022-10-18 07:19:29', 2, 'false', now())
    ON CONFLICT (id) DO UPDATE
SET (column_1, column_2, column_3, column_4, column_5)
    = (EXCLUDED.column_1, EXCLUDED.column_2, EXCLUDED.column_3, EXCLUDED.column_4, EXCLUDED.column_5);

User is going to either create new record or update using this same query. Whenever there is an update I need to store only updated column's name, old value, new value and changed time in my audit table.

I have created a function and a trigger like this:

CREATE OR REPLACE FUNCTION log_changes()
  RETURNS TRIGGER 
  LANGUAGE PLPGSQL
  AS
$$
BEGIN
IF NEW.column_1 != OLD.column_1 THEN
INSERT INTO course_history(id, course_id, field_name, action_type, old_value, new_value,  changed_time)
VALUES(uuid_generate_v4(), OLD.id, 'column_1', 'UPDATE', OLD.column_1, NEW.column_1, new.changed_time);

ELSEIF NEW.column_2 != OLD.column_2 THEN
INSERT INTO course_history(id, course_id, field_name, action_type, old_value, new_value,  changed_time)
VALUES(uuid_generate_v4(), OLD.id, 'column_2', 'UPDATE', OLD.column_2, NEW.column_2, new.changed_time);

// and so on for all columns that could be changed in course table

END IF;
RETURN NEW;
END;
$$

CREATE TRIGGER course_changes
  BEFORE UPDATE
  ON course
  FOR EACH ROW
  EXECUTE PROCEDURE log_changes();

This function creates new rows for each updated fields in single update. I am trying to reduce repeating code of if condition for each column. How can I achieve that?

Is there a way to combine updates of multiple fields into single row? so that if user updated only two columns in single query, I will have single row showing those updates in history table.

CodePudding user response：

To answer your second question : you can insert only one row with updates on several columns, by using a jsonb column in your table course_history in place of fields field_name, action_type, old_value, new_value, and put only one INSERT statement in your trigger function :

INSERT INTO course_history(id, course_id, changes_jsonb, changed_time)
SELECT uuid_generate_v4(), OLD.id, jsonb_agg(l.change_jsonb) FILTER (WHERE l.change_jsonb IS NOT NULL), new.changed_time)
  FROM (SELECT CASE WHEN OLD.column_1 != NEW.column_1 THEN jsonb_build_object('field_name', 'column_1', 'action_type', 'UPDATE', 'old_value', OLD.column_1, 'new_value', NEW.column_1) ELSE NULL END AS change_jsonb
       UNION ALL
       SELECT CASE WHEN OLD.column_2 != NEW.column_2 THEN jsonb_build_object('field_name', 'column_2', 'action_type', 'UPDATE', 'old_value', OLD.column_2, 'new_value', NEW.column_2) ELSE NULL END
       UNION ALL
       SELECT CASE WHEN OLD.column_3 != NEW.column_3 THEN jsonb_build_object('field_name', 'column_3', 'action_type', 'UPDATE', 'old_value', OLD.column_3, 'new_value', NEW.column_3) ELSE NULL END
       UNION ALL ... ) AS l

To answer your first question : you can try using a dynamic sql command in you trigger function :

DECLARE
  col text ;
  txt text ;
BEGIN
  FOR EACH col IN ARRAY array['column_1', 'column_2', 'column_3', ...]
  LOOP
    IF txt <> '' 
    THEN txt = txt || ' UNION ALL ' ;
    END IF ;
    txt = txt || 'SELECT CASE WHEN OLD.' || col || ' != NEW.' || col || E' THEN jsonb_build_object(\'field_name\', ' || col || E', \'action_type\', \'UPDATE\', \'old_value\', OLD.' || col || E', \'new_value\', NEW.' || col || ') ELSE NULL END AS change_jsonb'
  END LOOP ;
  EXECUTE 'INSERT INTO course_history(id, course_id, changes_jsonb, changed_time)
    SELECT uuid_generate_v4(), OLD.id, jsonb_agg(l.change_jsonb) FILTER (WHERE l.change_jsonb IS NOT NULL), new.changed_time) FROM (' || txt || ') AS l' ;
  RETURN NEW ;
END ;

CodePudding user response：

You can get the column names from information_schema.columns. To compare the values for a column, you can use code like:

DECLARE
   is_distinct boolean;
   col_name text;
BEGIN
   /* get col_name FROM the metadata */
   EXECUTE format('SELECT $1.%1I IS DISTINCT FROM $2.%1I', col_name)
      INTO is_distinct USING OLD, NEW;
   IF is_distinct THEN
      /* whatever */
   END IF;
END;