How to set some context variable for a user/connection

Question

I currently use Firebird SQL as the db backend of my shareware, would like to support also PG 9.3+.

In FB I use set/get_context to do this:

set-context

get-context

e.g. I would do this after a user logs in:

select rdb$set_context('USER_SESSION', 'LANGUAGE_ID', %s) \
        from rdb$database" % appobj.loggedInUser.language.id

In some of my views I would then use:

... AND t.fk_language_id=rdb$get_context('USER_SESSION', 'LANGUAGE_ID')

I searched through PG doc and did some googling but didn't find a solution yet.

Would appreciate any tips. Werner

Artiodactyl

If this is always per session (=connection), you could use a temporary table – Blubberhead

Answer 1

You can use a session variables in custom schema:

postgres=# set myvars.language_id = 10;
SET
postgres=# show myvars.language_id;
 myvars.language_id 
--------------------
 10
(1 row)

or via functions (http://www.postgresql.org/docs/9.4/static/functions-admin.html):

postgres=# select set_config('myvars.language_id', '20', false);
 set_config 
------------
 20
(1 row)

postgres=# select current_setting('myvars.language_id');
 current_setting 
-----------------
 20
(1 row)

Massacre

That looks good, will give it a try when a have a moment and report back. – Artiodactyl

Doesn't this set the environment for an entire DB? Not just for the particular DB connection? – Rhys

this setting is only for session – Massacre

Answer 2

Expanding on answer so context() function can be used as both, getter and setter,

CREATE OR REPLACE FUNCTION context(text, text default null)
 RETURNS text
 LANGUAGE sql
AS $function$

    select case when $2 is null
        then current_setting($1)
        else set_config($1, $2, false)
    end
    ;
$function$
;

Usage:

-- set
select context('USER_SESSION.LANGUAGE_ID', 'lang id');

and later,

-- get
select context('USER_SESSION.LANGUAGE_ID');

Discomposure

There is an exception if retrieving value for a key which wasn't set before in the current session. – Discomposure