Getting the only updated field using a postgreSQL trigger?

Question

Is there any way to use a trigger which can get the only updated field from a record? I am just testing the audit trail log so it can save the field name alongside its other information when it is updated. I do not want to use IF statement because I may have more than 200 fields in my table.

Answer 1

If you are using the latest versions of PostgreSQL, you can use the JSON(B) functions and operators to your advantage. Although this is not (yet) a full solution, check to see if it mimics what you're trying to achieve:

-- The 'new' and 'old' entries will simulate the 'old' and 'new' 
-- values for a row that you can use in a trigger function
WITH 
new(id, changed_column, integer_changed_column, not_changed,
 array_changed_column, changed_null) AS
(
 VALUES (12, text 'Value', 1234, text 'unchanged', 
 array [1, 2], cast(null as text))
),
old(id, changed_column, integer_changed_column, not_changed,
 array_changed_column, changed_null) AS
(
 VALUES (12, text 'New value', 1235, text 'unchanged', 
 array [1, 3], text 'not-null')
)
-- And we get a setof records with the changes
SELECT
 *
FROM
 (
 SELECT
 column_name, 
 (row_to_json(new)->column_name #>> '{}') AS new_value, 
 (row_to_json(old)->column_name #>> '{}') AS old_value
 FROM
 new, old, (
 SELECT
 json_object_keys(row_to_json(new)) AS column_name
 FROM
 new
 ) AS cc
 ) AS s0
WHERE
 new_value IS DISTINCT FROM old_value
ORDER BY
 column_name ;

The result that you'll get shows you all the updated columns (=fields). I have assumed that more than one can be updated at once:

 column_name | old_value | new_value
 ------------------------+-----------+-----------
 array_changed_column | [1,2] | [1,3] 
 changed_column | Value | New value 
 changed_null | | not-null 
 integer_changed_column | 1234 | 1235 

NOTE: all values are converted to text, because it is the type that all others can be converted to.

Answer 2

Another way is to exploit JSON/JSONB functions that come in recent versions of PostgreSQL. It has the advantage of working both with anything that can be converted to a JSON object (rows or any other structured data), and you don't even need to know the record type.

See my original StackOverflow post with appropriate examples.