2

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.

5

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.

|improve this 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.

|improve this answer|||||

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.