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.
2 Answers
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.
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.