Re: Audit Trigger puzzler

On Wed, Sep 02, 2009 at 11:44:20PM -0500, Adam Rich wrote:
- In Oracle, the way we handle audit triggers is by using Package
- Variables. We emulate some of that functionality in postgresql by
- adding a custom variable to the configuration file:
-
- custom_variable_classes = 'mysess'
-
- Then, whenever a user logs into the application, my login procedure
- calls this function:
-
- CREATE OR REPLACE FUNCTION begin_sess(staffid character varying)
- RETURNS void AS $BODY$ BEGIN
- PERFORM set_config('mysess.curr_user', coalesce(staffid,''), false);
- END; $BODY$ LANGUAGE 'plpgsql' VOLATILE;
-
- This makes the current application user automatically available to every
- function, including triggers. Then, in your triggers, you can do
- this:
-
- DECLARE
- curr_user staff.staff_id%TYPE;
- BEGIN
- SELECT current_setting('mysess.curr_user') INTO curr_user;
-
-
- In your trigger, you could check that this variable was unset, and fall
- back to the database user.
-

Thanks! that does seem slick, but will it work with connection pooling?

Dave