0

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 INSERT table? 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.

2

You want current_setting:

SELECT current_setting('pubsubpull.request_id');
  • Thanks -- Google is next to useless for searching for a keyword like SHOW :) – KayEss May 20 '15 at 4:15

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.