PGXN

Contents

To get all changes per audit_id of one transaction as one row of JSONB you can use the pgmemento.jsonb_merge function as an aggregate or window function. When combining it with an ordering by the row_log ID it is possible to see the first or the last changes per field.

SELECT
  r.audit_id,
  pgmemento.jsonb_merge(r.old_data ORDER BY r.id) AS first_changes,
  pgmemento.jsonb_merge(r.old_data ORDER BY r.id DESC) AS last_changes
FROM
  pgmemento.row_log r
JOIN
  pgmemento.table_event_log e 
  ON e.event_key = r.event_key
WHERE
  e.transaction_id = 1000000
GROUP BY
  r.audit_id;