Set session variable in postgresql

Before version 9.2, you needed to add your custom class variable to custom_variable_classes parameter in postgresql.conf, like:

custom_variable_classes = 'myapp'

In 9.2, this requirement has been removed:

Remove the custom_variable_classes parameter (Tom Lane).
The checking provided by this setting was dubious. Now any setting can be prefixed by any class name.

So, since 9.2 you can just set your custom class variable as you are doing currently, no need to worry about changing postgresql.conf.

-- set_config(var_name, value, is_only_for_local_transaction)
-- if local_transaction is false, then var is setted for entire connection/session
-- Also:  set session "myapp.user" = 'admin';
SELECT set_config('myapp.user', 'admin', FALSE);
-- get var:
SELECT current_setting('myapp.user');

With SET LOCAL (Only in transaction):
Specifies that the command takes effect for only the current transaction. After COMMIT or ROLLBACK, the session-level setting takes effect again. Issuing this outside of a transaction block emits a warning and otherwise has no effect.

-- Also:  set LOCAL "myapp.user" = '22';
SELECT set_config('myapp.user', 'admin', TRUE);
-- get var:
SELECT current_setting('myapp.user');

Source:
https://www.postgresql.org/docs/9.5/static/functions-admin.html#FUNCTIONS-ADMIN-SET-TABLE
http://dba.stackexchange.com/questions/97095/set-session-custom-variable-to-store-user-id
https://www.postgresql.org/docs/current/static/sql-set.html

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s