Getting view of data how it looked at specific event

[rene98c]

Nice work you have done.

Not really an issue but i hope i am on a right track.
Will this work (customer with 4 fields)

CREATE TABLE public.customer
(
name text,
email text,
phone text,
address text,
id serial
)

-- all the events that occured
select event_id from pgmemento.row_log where audit_id = ( select audit_id from public.customer where id = 1)

-- and now i want to see how data looked at specific event (7)

`SELECT
event_id,
first_value(name) over (partition by name_partition order by event_id) as name,
first_value(email) over (partition by email_partition order by event_id) as email,
first_value(phone) over (partition by phone_partition order by event_id) as phone,
first_value(address) over (partition by address_partition order by event_id) as address
FROM (
select
event_id,
rl.changes->>'name' as name,
sum(case when (rl.changes->'name')::text is null then 0 else 1 end) over (order by event_id) as name_partition,
rl.changes->>'email' as email,
sum(case when (rl.changes->'email')::text is null then 0 else 1 end) over (order by event_id) as email_partition,
rl.changes->>'phone' as phone,
sum(case when (rl.changes->'phone')::text is null then 0 else 1 end) over (order by event_id) as phone_partition,
rl.changes->>'address' as address,
sum(case when (rl.changes->'address')::text is null then 0 else 1 end) over (order by event_id) as address_partition
from row_log rl
inner join public.customer target
on target.audit_id = rl.audit_id
where
rl. event_id = 7
) as q

[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.