Re: What's faster? BEGIN ... EXCEPTION or CREATE TEMP TABLE IF NOT EXISTS?

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.