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.