Dynamically get columns names using old in triggers

Question

I want to write a generic trigger function(Postgres Procedure). There are many main tables like TableA, TableB, etc. and their corresponding audit tables TableA_Audit, TableB_Audit, respectively. The structure is given below.

TableA and TableA_Audit has columns aa integer, ab integer.

TableB and TableB_Audit has columns ba integer.

Similarly there can be many main tables along with their audit tables.

The requirement is that is any of the main table gets updated then their entries should be inserted in their respective audit Table.

eg:- If TableA has entries like this

                ---------------------
                |     **TableA**   |
                ---------------------
                |aa      | ab       |
                |--------|----------|
                |    5   |  10      |
                ---------------------

and then i write an update like

update TableA set aa= aa+15,

then the old values for TableA should be inserted in the TableA_Audit Table like below

TableA_audit contains:-

                ---------------------
                |  **TableA_Audit** |
                ---------------------
                |aa      | ab       |
                |--------|----------|
                |    5   |  10      |
                ---------------------

To faciliate the above scenario i have written a generic function called insert_in_audit. Whenever there is any update in any of the main table, the function insert_in_audit should be called. The function should achive the following:-

Dynamically insert entries in corresponding audit_table using main table. If there is update in Table B then entries should be inserted only in TableB_Audit.

Till now what i am able to do so. I have got the names of all the columns of the main table where update happened. eg: for the query - update TableA set aa= aa+15, i am able to get all the columns name in TableA in a varchar array.

column_names varchar[ ] := '{"aa", "ab"}';

My question is that how to get old values of column aa and ab. I tried doing like this

foreach i in array  column_names
loop
 raise notice '%', old.i;
end loop;

But the above gave me error :- record "old" has no field "i". Can anyone help me to get old values.

@Abhishek please only tag questions with the appropriate SQL variety. — Nick, Sep 24 '19 at 10:43
@jarlh ah... didn't realise it was a rhetorical question... apologies... — Nick, Sep 24 '19 at 10:44
If you want a generic audit trigger why not use many of the solutions out there. e.g. [here](https://www.cybertec-postgresql.com/en/tracking-changes-in-postgresql/) or [here](http://okbob.blogspot.co.uk/2015/01/most-simply-implementation-of-history.html) or [here](https://www.garysieling.com/blog/auditing-data-changes-postgres) — , Sep 24 '19 at 10:45
@a_horse_with_no_name in all the links which you have sent, they have hardcoded the columns names in the procedure itself. They have assumed that only one trigger function calls the procedure. My requirement is that the procedure is called by all the main tables whenever there is any change in the main table. So many trigger calls the procedure itself. So i cannot hardcode the column names in procedure — Abhishek, Sep 24 '19 at 11:01
The only column names that are hardcoded with those solutions are those for the audit table, not for the base table on which the triggers are created. The audit table simply stores the old and new data in JSONB columns where the keys are the column names. — , Sep 24 '19 at 11:04
https://stackoverflow.com/questions/26354558 or https://stackoverflow.com/questions/55245353/ or https://stackoverflow.com/questions/45230881/ — , Sep 24 '19 at 11:05
@a_horse_with_no_name but i dont have any requirement to store the old data in JSONB format. I want to store the data individually in audit tables — Abhishek, Sep 24 '19 at 11:07
But it would make the whole auditing approach a **lot** easier and more flexible. There is a reason why most solutions with generic triggers do it that way — , Sep 24 '19 at 11:12
Method for accessing record type variable (`NEW` and `OLD` should fit here) with dynamically generated column names is described here: https://dba.stackexchange.com/questions/21473/dynamic-access-to-record-column-in-plpgsql-function — Łukasz Kamiński, Sep 24 '19 at 11:17
@ŁukaszKamiński yes i want to have individual audit tables for each main table. But can u specify what is json/hstaore and how it works. Can we combine everything in one single audit table instead of having different audit tables — Abhishek, Sep 24 '19 at 14:21

score 0 · Answer 1 · answered Sep 25 '19 at 21:17

Here is a code sample how you can dynamically extract values from OLD in PL/pgSQL:

CREATE FUNCTION dynamic_col() RETURNS trigger
   LANGUAGE plpgsql AS
$$DECLARE
   v_col name;
   v_val text;
BEGIN
   FOREACH v_col IN ARRAY TG_ARGV
   LOOP
      EXECUTE format('SELECT (($1).%I)::text', v_col)
         USING OLD
         INTO v_val;
      RAISE NOTICE 'OLD.% = %', v_col, v_val;
   END LOOP;

   RETURN OLD;
END;$$;

CREATE TABLE trigtest (
   id integer,
   val text
);

INSERT INTO trigtest VALUES
   (1, 'one'), (2, 'two');

CREATE TRIGGER dynamic_col AFTER DELETE ON trigtest
   FOR EACH ROW EXECUTE FUNCTION dynamic_col('id', 'val');

DELETE FROM trigtest WHERE id = 1;

NOTICE:  OLD.id = 1
NOTICE:  OLD.val = one

Dynamically get columns names using old in triggers

1 Answers1