Thread: Setting a variable for current session (set, define or something) for auditing

Hello,

we are using a kind of auditing like describes in the manual:

http://www.postgresql.org/docs/8.1/static/plpgsql-trigger.html

But instead of the example we are using a structure like:

CREATE TABLE emp (
    empname           text NOT NULL,
    stamp             timestamp NOT NULL,
    userid            integer      NOT NULL,
    salary            integer
);

CREATE TABLE emp_audit(
    increment_me bigserial,
    audit_stamp             timestamp NOT NULL,
    audit_userid            integer      NOT NULL,
    operation         char(1)   NOT NULL,
    stamp             timestamp NOT NULL,
    userid            intger      NOT NULL,
    empname           text      NOT NULL,
    salary integer
);




When there is an insert in emp, our after-trigger will set in the emp_audit:
audit_stamp = new.stamp
audit_userid = new.userid
stamp = new.stamp
userid = new.userid
Operation = 'I'

When there is a update:
audit_stamp = new.stamp
audit_userid = new.userid
stamp = old.stamp
userid = old.userid
Operation = 'U'

When there is a delete:
audit_stamp = ?
audit_userid = ?
stamp = old.stamp
userid = old.userid
Operation = 'D'

So we are always logging the old row and the userid from the person that was
making the update/insert/delete.

That will work well for insert and an update because we are submitting the
userid and stamp values.
But on a delete. What should we do?

The userid is a number that came from our application, so we need to set it
before a delete statement will be fired.

But how can we do that?

Is there a way to just make a query like:
set my_userid = 1234

And then make the delete command?
How would you solve this problem, maybe our logging design is not good?
We also need that we set such a variable (if it is possible), that it is not
available for the other connections.
I think that we have to turn off connection pooling and connect and
disconnect on every query so there will be no influence from other threads
that are using different my_userid?

What should we do?

Thank you

Christian