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.
To find the differences between any two rows/records, you can use this little hack:
SELECT pre.key AS columname, pre.value AS prevalue, post.value AS postvalue
FROM jsonb_each(to_jsonb(OLD)) AS pre
CROSS JOIN jsonb_each(to_jsonb(NEW)) AS post
WHERE pre.key = post.key AND pre.value IS DISTINCT FROM post.value
Where OLD and NEW are the built-in records found in trigger functions representing the pre and after state respectively of the changed record. Note that I have used the table aliases pre and post instead of old and new to avoid collision with the OLD and NEW built-in objects. Note also the use of IS DISTINCT FROM instead of a simple != or <> to handle NULL values appropriately.
Of course, this will also work with any ROW constructor such as ROW(1,2,3,...) or its short-hand (1,2,3,...). It will also work with any two JSONB objects that have the same keys.
For example, consider an example with two rows (already converted to JSONB for the purposes of the example):
SELECT pre.key AS columname, pre.value AS prevalue, post.value AS postvalue
FROM jsonb_each('{"col1": "same", "col2": "prediff", "col3": 1, "col4": false}') AS pre
CROSS JOIN jsonb_each('{"col1": "same", "col2": "postdiff", "col3": 1, "col4": true}') AS post
WHERE pre.key = post.key AND pre.value IS DISTINCT FROM post.value
The query will show the columns that have changed values:
columname | prevalue | postvalue
-----------+-----------+------------
col2 | "prediff" | "postdiff"
col4 | false | true
The cool thing about this approach is that it is trivial to filter by column. For example, imagine you ONLY want to detect changes in columns col1 and col2:
SELECT pre.key AS columname, pre.value AS prevalue, post.value AS postvalue
FROM jsonb_each('{"col1": "same", "col2": "prediff", "col3": 1, "col4": false}') AS pre
CROSS JOIN jsonb_each('{"col1": "same", "col2": "postdiff", "col3": 1, "col4": true}') AS post
WHERE pre.key = post.key AND pre.value IS DISTINCT FROM post.value
AND pre.key IN ('col1', 'col2')
The new results will exclude col3 from the results even if it's value has changed:
columname | prevalue | postvalue
-----------+-----------+------------
col2 | "prediff" | "postdiff"
It is easy to see how this approach can be extended in many ways. For example, say you want to throw an exception if certain columns are updated. You can achieve this with a universal trigger function, that is, one that can be applied to any/all tables, without having to know the table type:
CREATE OR REPLACE FUNCTION yourschema.yourtriggerfunction()
RETURNS TRIGGER AS
$$
DECLARE
immutable_cols TEXT[] := ARRAY['createdon', 'createdby'];
BEGIN
IF TG_OP = 'UPDATE' AND EXISTS(
SELECT 1
FROM jsonb_each(to_jsonb(OLD)) AS pre, jsonb_each(to_jsonb(NEW)) AS post
WHERE pre.key = post.key AND pre.value IS DISTINCT FROM post.value
AND pre.key = ANY(immutable_cols)
) THEN
RAISE EXCEPTION 'Error 12345 updating table %.%. Cannot alter these immutable cols: %.',
TG_TABLE_SCHEMA, TG_TABLE_NAME, immutable_cols;
END IF;
END
$$
LANGUAGE plpgsql VOLATILE
You would then register the above trigger function to any and all tables you want to control via:
CREATE TRIGGER yourtiggername
BEFORE UPDATE ON yourschema.yourtable
FOR EACH ROW EXECUTE PROCEDURE yourschema.yourtriggerfunction();
