Postgres function to show new column values after update as JSON

Question

I am trying to write a postgres function for AFTER UPDATE trigger that creates a JSON object with column names and their updated values for only the columns that changed. I am trying to create a generalized function that I can use for any table hence using Record as function argument. The old and new are coming from trigger's OLD and NEW variables. The end goal is to save the returned JSON into a JSON field in an audit table.

CREATE OR REPLACE FUNCTION row_updates(old RECORD, new RECORD) RETURNS JSON AS
$$
DECLARE
    updates JSON;
BEGIN
    WITH columns AS (
        SELECT json_object_keys(row_to_json(new)) "column"
    )
    SELECT
        json_object_agg("column", new_value) INTO updates
    FROM (
        SELECT 
            "column",
            (row_to_json(new)->"column" #>> '{}') as new_value,
            (row_to_json(old)->"column" #>> '{}') as old_value
        FROM
            columns
    ) changes
    WHERE 
        new_value IS DISTINCT FROM old_value;

    RETURN updates;
END;
$$ LANGUAGE plpgsql;

CREATE OR REPLACE FUNCTION audit_change() RETURNS TRIGGER AS
$$
DECLARE
    updates JSON;
BEGIN
    IF (TG_OP = 'INSERT') THEN
        raise NOTICE 'Logging insert on relation (%.%) %', TG_TABLE_SCHEMA, TG_TABLE_NAME;

        RETURN NEW;
    ELSIF (TG_OP = 'UPDATE') THEN
        updates := row_updates(OLD, NEW)
        raise NOTICE 'Logging update on relation (%.%) %', TG_TABLE_SCHEMA, TG_TABLE_NAME, updates;
        RETURN NEW;
    ELSIF (TG_OP = 'DELETE') THEN
        raise NOTICE 'Logging delete on relation (%.%) %', TG_TABLE_SCHEMA, TG_TABLE_NAME;
        RETURN OLD;
    END IF;
END;
$$ LANGUAGE plpgsql;

I am getting this error: ERROR: PL/pgSQL functions cannot accept type record.

Is there a better way of accomplishing this? Obviously I want to do it in non trigger function so I can reuse it in different triggers for different tables. Also I may want to adapt it for AFTER INSERT and AFTER DELETE triggers if possible so I don't want to branch off TG_OP in trigger function and repeat the logic shown in SQL above.

End goal:

=> select * from org_user;

 id | org_id | user_id 
----+--------+---------
  1 |      1 |       1
 23 |      1 |       3

=> update org_user set org_id=3, user_id = 4 where id = 23; 

-- trigger creates following row in audit table

 id | relation  | record_id | updates 
----+-----------+-----------+--------------
  1 |  org_user |     23    | {'org_id': 3, 'user_id': 4} 

Answer 1

Create a function that compares two JSONB values:

create or replace function jsonb_diff(jsonb, jsonb)
returns jsonb language sql immutable as $$
    select jsonb_object_agg(n.key, n.value)
    from jsonb_each($1) o
    join jsonb_each($2) n on o.key = n.key
    where o.value <> n.value;
$$;

and use it in your trigger function:

    updates := jsonb_diff(to_jsonb(OLD), to_jsonb(NEW));
    raise NOTICE 'Logging update on relation (%.%) %', TG_TABLE_SCHEMA, TG_TABLE_NAME, updates;
    RETURN NEW;

By the way, in Postgres 11+ you can use function arguments of the record type.