We're using a trigger to track changes in various database tables. This works fine. Now we want to be able to track the changes back to the web request that caused them.
I'd like to be able to use
SET LOCAL for this, because it will also serve as a useful check that a transaction has been properly created. So the idea is to insert a row in a requests table and then use
SET LOCAL pubsubpull.request_id to set the ID for the request which can then be used in the
INSERT statement in the update log table generated in the trigger (if available).
I can see what the value is using
SHOW pubsubpull.request_id, but how do I capture that value so it can be used in the
SHOW doesn't seem to be legal in
SELECT in any way. These user defined options don't seem to show up in the
pg_settings view so I can't select it from there.
I have looked at using
txid_current() but would prefer not to need to.