setting custom session variables? - Mailing list pgsql-novice

Hi,

Can I set my own postgresql session variables?
My idea is the following: I have a web based app, working with a postgresql db.
The app connects to the postgresql with user db_user. All users of the web app have to
authenticate with a login and password.

I want to be able to know which "app user" did which database changes. For that, I can
tweak all SQL queries issued to the DB server to set a column 'user' to the logged in user.
I thought that if I could set a session variable, I wouldn't even have to change the queries
but simply use a trigger that simply set the new.user to the corresponding session variable:

pg_session_set('app_user', 'app-user-logged-in');
update customers set name='new_name' where customer_id=123;
This last query fires the trigger that set the user value for customer 123 to
'app-user-logged-in':

CREATE TRIGGER "set_user_customers" before UPDATE on "customers" for each row execute procedure "set_user" ();

CREATE FUNCTION "set_user" () RETURNS opaque AS '
 BEGIN
        new.user=pg_session_get('app_user');
        return new;
 END;
 ' LANGUAGE 'plpgsql';


Is that possible or a good idea to begin with?

I've found doc on predefined session variables
(http://www.be.postgresql.org/docs/7.4/static/functions-misc.html#FUNCTIONS-MISC-SESSION-TABLE)
but nothing about user defined session vars.

thanks

Raph