The session_variable database extension

The session_variable extension provides a way to create and maintain session scoped variables and constants. This extension could be used as an alternative for Oracle's global constants and variables.

The session_variable extension registers variables and constants. But internally they are intermixed and treated as the same. There is just a boolean that indicates whether or not the session_variable.set(variable_name, value) can be invoked. So variable names and constant names must be unique within both types. For the remaining text where variables are mentioned, constants are meant as well.

Variables (and constants) are defined (created) on the database level. Each user session will get a local copy of all defined variables on first invocation of any of the session_variable functions. Invocations of session_variable.set(variable_name, value) will ONLY alter the content of the session local copy of the variable. Other sessions will not be affected in any way - they have their own copy at their disposal.

The session_variable.init() function reloads all defined variables from the session_variable.variables table. This function will be invoked when a session starts, and can be invoked at any time. All variables will be reverted to their initial state.

Variables can be defined using the session_variable.create_variable(variable_name, variable_type), session_variable.create_variable(variable_name, variable_type, initial_value) or session_variable.create_constant(constant_name, constant_type, value) administrator functions. The initial value can be null - even the value of a constant (the profit of this is disputable).

The initial value or the constant value can be altered using the session_variable.alter_value(variable_or_constant_name, value) administrator function. The administrator who invokes the alter_value() function will see the altered value immediately, but all existing sessions will remain working with the old value or the value that they set themselves. Any new session will see the altered value. Invocation of the session_variable.init() function will make the altered value available on the session in which it is invoked.

A variable can be removed using the session_variable.drop(variable_or_constant_name) administrator function. And here again existing sessions will not notice any change unless they invoke the session_variable.init() function.

  -- First create a variable
  select session_variable.create_variable('my_variable', 'text'::regtype, 'initial text'::text);    

  -- Checked if that worked
  select session_variable.get('my_variable', null::text);

  -- Change the content of the variable
  -- Notice that the prior content is returned
  select session_variable.set('my_variable', 'changed text'::text);

  -- Used in a bit of plpgsql code
  do $$
  declare
      my_field text;
  begin
      my_field := session_variable.get('my_variable', my_field);
      raise notice 'the content of my_field is "%"', my_field;
  end
  $$ language plpgsql;

  -- cleanup
  select session_variable.drop('my_variable');

Functions

session_variable.create_variable(variable_name, variable_type)

session_variable.create_variable(variable_name, variable_type, initial_value)

session_variable.create_constant(constant_name, constant_type, value)

session_variable.alter_value(variable_or_constant_name, value)

session_variable.drop(variable_or_constant_name)

Removes the specified constant or variable.

The constant or variable will be available any more in the current session and in sessions that are created after the committed invocation of session_variable.drop(variable_or_constant_name). Existing sessions do not see the altered situation unless they invoke the session_variable.init() function.

Example:
select session_variable.drop('my_environment_constant');

session_variable.init()

Reloads all variables and constants in the current session

All variables that have been changed using session_variable.set(variable_name, value) invocations will be undone. The effect is visible in the current session only. All other sessions are left untouched.

Beware that the get_constant() function may return cached content.

Example:
select session_variable.init();

session_variable.set(variable_name, value)

session_variable.get(variable_or_constant_name, just_for_result_type)

session_variable.get_stable(variable_or_constant_name, just_for_result_type)

Does excactly the same as the get() function. But the get_stable() function is marked "STABLE" (see: https://www.postgresql.org/docs/current/sql-createfunction.html). So the result of the function may be cached during the execution of a statement. This behaviour will be right for practically all invocations. Only when the value of a variable is altered within the execution of a statement, for example in trigger code, then unexpected results may occur.

Example:
select session_variable.get_stable('my_variable', null::text);

session_variable.get_constant(constant_name, just_for_result_type)

BEWARE! this function is marked as "IMMUTABLE" (see: https://www.postgresql.org/docs/current/sql-createfunction.html). This means that te database is allowed to cache the function result for a given combination of arguments. This is a good optimisation in normal operation. But when altering the content of constants make sure that you use the get() function instead of get_constant() as there is a chance that you get a cached result when invoking get_constant().

Example:
select session_variable.get_constant('my_constant', null::text);

session_variable.exists(variable_or_constant_name)

session_variable.type_of(variable_or_constant_name)

Returns the type of the variable or constant

Example:
select session_variable.type_of('my_variable');

session_variable.is_constant(variable_or_constant_name)

Returns true if "variable_or_constant_name" happens to be a constant or false if it is a session variable

Example:
select session_variable.type_of('my_variable');

session_variable.get_session_variable_version()

Returns the code version of the extension, currently '3.1'.

Example:
select session_variable.get_session_variable_version();

session_variable.dump(do_truncate)

Generates a 'script' that may be used as backup.

Take care when using PSQL's \copy command. It will double all backslash (\) characters.

Example:
select session_variable.dump();

session_variable.is_executing_variable_initialisation()

Returns true if a user-provided function called session_variable.variable_initialisation() is currently being invoked on behalf of session_variable initialisation. Thus the session_variable.variable_initialisation() function can check if it not illegally invoked outside session_variable initialisation code.

session_variable.variable_initialisation()

This function is NOT provided by the database extension, but might be created by you!

If the function exists, it will be invoked by the session_variable initialisation code just after all values with their default values are loaded from the session_variable.variables table, but before any other action takes place.

During the execution for the session_variable.variable_initialisation() function on behalf of session variable initialisation, also values of constants can be set.

Example:

create or replace function session_variable.variable_initialisation()
   returns void
   language plpgsql
   as $$
begin
   if not session_variable.is_executing_variable_initialisation()
   then
       raise sqlstate '55099' using message =
          'This function can only be invoked as part of session_variable initialisation';
   end if;
   perform session_variable.set('headline_of_the_day',
      'we have nice weather today'::varchar);
exception
   when sqlstate '55099' then
      raise;
  when others then
     raise log 'error occurred in session_variable.variable_initialisation(), sqlstate=%, sqlerrm=%',
               sqlstate, sqlerrm;
end;
$$;

Security

Usage of session_variable.create_variable(variable_name, variable_type), session_variable.create_variable(variable_name, variable_type, initial_value), session_variable.create_constant(constant_name, constant_type, value), session_variable.alter_value(variable_or_constant_name, value), session_variable.drop(variable_or_constant_name) and session_variable.dump() is proteced by the "session_variable_administrator_role".

The remaining functions are protected by the "session_variable_user_role".

The "session_variable_administrator_role" includes the "session_variable_user_role".