Getting view of data how it looked at specific event

[pgmemento]

Getting view of data how it looked at specific event

[FxKu]

Thank. Uh, I think I will need some more time to figure out your query...
I've created a function to produce a jsonb object for a given audit_id and transaction_id. It's called generate_log_entry. You can feed this jsonb-Object into jsonb_poplulate_record function from Postgres (and pass your table as template) and you will receive the single columns. The function is quiet slow at the moment, but for a single audit_id / event_id it should be fine.

WITH get_log_entries AS (
  SELECT
    pgmemento.generate_log_entry(e.transaction_id,r.audit_id,'customer','public') AS entry
  FROM pgmemento.row_log r
  JOIN pgmemento.table_event_log e ON e.id = r.event_id
    WHERE r.audit_id = (select audit_id from public.customer where id = 1)
        AND e.id = 7
    ORDER BY t.txid DESC
)
SELECT j.* FROM get_log_entries i
JOIN LATERAL ( 
  SELECT * FROM jsonb_populate_record(null::public.customer, i.entry)
) j ON (true);

[rene98c]

Great , thanks, just what i was looking for, my script had the same idea but this one seems better and it works.