On 01/10/2012 15:36, Moshe Jacobson wrote:
> I am working on an audit logging trigger that gets called for every row
> inserted, updated or deleted on any table.
> For this, I need to store a couple of temporary session variables such as
> the ID of the user performing the change, which can be set at the start of
> the session.
> Until now I have been using a permanent table to store the session
> variables, but it has been difficult to wipe the data properly at the end
> of the session.
Do you know about session variables? I did something similar to what you
are describing and it ended up much simpler than using tables, temporary
or not.
You need to configure them in postgresql.conf, e.g.:
custom_variable_classes = 'myapp'
Then in the application code:
SET myapp.uid = 42;
And in the pl/pgsql function:
CREATE OR REPLACE FUNCTION dblog() RETURNS TRIGGER AS $$
DECLARE
uid INTEGER;
BEGIN
BEGIN
SELECT current_setting('myapp.uid') INTO uid;
EXCEPTION
WHEN undefined_object THEN
uid = null;
WHEN data_exception THEN
uid = null;
END;
...
END;
$$ LANGUAGE plpgsql;
The major benefit here is that it doesn't touch the table engines,
temporary or not.
Attachment:
signature.asc
Description: OpenPGP digital signature
