I'm a very happy user of supa_audit library. This very small sql library allowed leverages some old postgres common knowledge about triggers and audits in a very nicely packed form, so you can track down changes to tables or rows in tables.
You can easily track changes in table "foo" just by running
SELECT audit.enable_tracking('foo'::regclass)
I started with very naive way of tracking down objects, by identifying them via `table_name`+`record->>'id'`. That sort of works, but it doesn't scale at all, requiring full scans for everything, and detoasting `record` when the record is big enough.
supa_audit provides this `to_record_id` function that generates the indexed record_id value for a given row. What happens if we want to run it for 2 ad-hoc values?
Well, there's `unnest`, which is very cool. It takes an array, and converts it to a table-like relation, so you can use it as a `from`. That way, we could process N values in one sql go with the following:
select audit.to_record_id('tablename'::oid,'{id}', jsonb_build_object('id',id))::uuid as record_id from unnest(?)
Nice trick, right?
Entradas
