Hello everybody. Writing a trigger on plpgsql, I am trying to loop through the old and new values ​​of each column in the table. The problem is that I don’t know how to correctly use this variable in an expression like OLD.current_column_name and NEW.current_column_name .

 CREATE FUNCTION my_function() RETURNS TRIGGER AS $emp_stamp$ DECLARE current_column_name text; BEGIN FOR current_column_name IN SELECT column_name FROM information_schema.Columns WHERE table_schema = TG_TABLE_SCHEMA AND table_name = TG_TABLE_NAME LOOP IF (OLD."current_column_name" <> NEW."current_column_name") then ... END IF; END LOOP; RETURN NEW; END; $emp_stamp$ LANGUAGE plpgsql; CREATE TRIGGER my_trigger AFTER UPDATE ON my_table FOR EACH ROW EXECUTE PROCEDURE my_function();

I get the following error: ERROR: record "old" has no field "current_column_name" I tried to experiment with quotes in different ways, but nothing happened. As a result, I need to write the changed values ​​from the table in the following form: column_name , old_value and new_value . Tell me, please, how to do it correctly.

    3 answers 3

    Since NEW and OLD are RECORD variables, you can refer to their fields as array elements when using plv8 :

     CREATE FUNCTION foo_fn() RETURNS trigger AS $$ for (var key in OLD) { NEW[key]=OLD[key]; } return NEW; $$ LANGUAGE plv8;

    By the way, sometimes useful function :-)

      You cannot refer to the column name via a variable in plpgsql. Historical answer - use other embedded languages. There are a lot of them.

      But for the task "to find the difference and pledge" you can not be tied to the record type, but use JSON. The row_to_json function that accepts a record and gives json to itself has long existed from a distant postgresql 9.2, where only jaris array_to_json and row_to_json were from the whole json processing. True, these functions will not be enough for the task of determining the difference between the lines. So pg fresher or rewritten using the hstore functions is hstore - there is also an option to create hstore from a record and for a long time many data processing functions.

       CREATE or replace FUNCTION my_function() RETURNS TRIGGER AS $emp_stamp$ DECLARE rowdiff json; BEGIN select json_object_agg(key, json_build_object('old', o.value, 'new', n.value)) into rowdiff from json_each(row_to_json(NEW.*)) n full outer join json_each(row_to_json(OLD.*)) o using(key) where o.value::text != n.value::text; raise notice '%', rowdiff; RETURN NEW; END; $emp_stamp$ LANGUAGE plpgsql;

      As a result, rowdiff will have a json object of differences between lines like this:

       update test set val=4 where id=1; NOTICE: { "val" : {"old" : 3, "new" : 4} } UPDATE 1

      I only had postgresql 9.4 on hand, which still lacks the jsonb_object_agg , jsonb_build_object , so the example uses the text json.

        The hstore extension will help you . It should be something like this (not tested).

         FOR _attr IN SELECT o.key AS key , o.value AS old_value , n.value AS new_value FROM each(hstore(OLD)) o INNER JOIN each(hstore(NEW)) n WHERE o.value <> n.value LOOP RAISE NOTICE 'Изменилась колонка % с ''%'' на ''%''', _attr.key, _attr.old_value, _attr.new_value; END LOOP;

        Do not forget that this is an extension that needs to be installed (although it is very easy and fast). How to do it, it is said, for example, here