Re: setting custom session variables?

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

Raphael Bauduin <raphael(dot)bauduin(at)be(dot)easynet(dot)net> writes:
> Can I set my own postgresql session variables?

Not in existing releases. There is a feature in 8.0 that you could
use (or misuse?) that way.

regards, tom lane

Tom Lane wrote:
> Raphael Bauduin <raphael(dot)bauduin(at)be(dot)easynet(dot)net> writes:
>>Can I set my own postgresql session variables?
>
> Not in existing releases. There is a feature in 8.0 that you could
> use (or misuse?) that way.
>

Also see myfunc_setvar(), myfunc_getvar, and myfunc_rmvar() in:
http://www.joeconway.com/myfunc.tgz
for an example of how you could make your own. There is an article that
briefly describes these functions here:
http://www.onlamp.com/pub/a/onlamp/2004/06/28/postgresql_extensions.html

HTH,

Joe

Joe Conway wrote:
> Tom Lane wrote:
>
>> Raphael Bauduin <raphael(dot)bauduin(at)be(dot)easynet(dot)net> writes:
>>
>>> Can I set my own postgresql session variables?
>>
>>
>> Not in existing releases. There is a feature in 8.0 that you could
>> use (or misuse?) that way.
>>
>
> Also see myfunc_setvar(), myfunc_getvar, and myfunc_rmvar() in:
> http://www.joeconway.com/myfunc.tgz
> for an example of how you could make your own. There is an article that
> briefly describes these functions here:
> http://www.onlamp.com/pub/a/onlamp/2004/06/28/postgresql_extensions.html
>

I finally had the time to test it and it does exactly what I need.
That saves us a lot of work, just as tablefunc does.

Thanks a lot!

Raph

>
> HTH,
>
> Joe