Skip to content
The session_variable Postgres database extension provides a way to create and maintain session scoped variables and constants, more or less like Oracle's global variables.
Branch: master
Clone or download
Fetching latest commit…
Cannot retrieve the latest commit at this time.
Permalink
Type Name Latest commit message Commit time
Failed to load latest commit information.
expected
sql
.gitignore
LICENSE
Makefile
README.md
session_variable--1.0--2.0.sql
session_variable--1.0.sql
session_variable--2.0--3.0.sql
session_variable--2.0.control
session_variable--2.0.sql
session_variable--3.0.control
session_variable--3.0.sql
session_variable.c
session_variable.control
session_variable.css
session_variable.h
session_variable.html

README.md

session_variable

The session_variable Postgres database extension provides a way to create and maintain session scoped variables and constants. This extension can be part of a solution to mimic Oracle's global constants and variables.

Introduction

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.

Example:

-- 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<br>
-- Notice that the prior content is returned
select session_variable.set('my_variable', 'changed text'::text);

-- Used in a bit of plpgsql code
do $$<br>
declare<br>
    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');

Postgres versions

The session_variable database extension has been tested on Postgres versions 9.5, 9.6, 10, 11 and 12.

Installation

Install as a normal Posrgres database extension:
- Make sure pg_config points to the right places
- execute make
- execute sudo make install installcheck
and then in the Postgres database execute:
- create extension session_variable;

Functions

session_variable.create_variable(variable_name, variable_type)

The create_variable function creates a new variable with initial value null.

The created variable will be available in the current session and in sessions that are created after the committed invocation of session_variable.create_variable(variable_name, variable_type). Existing sessions do not see the altered situation unless they invoke the session_variable.init() function.

Arguments
name type description
variable_name text Name of the variable to be created
variable_type regtype The datatype that can be stored in the variable
Returns
  boolean true if ok
Exceptions
  22004 variable name must be filled
  22004 variable type must be filled
  2200F variable name must be filled
  23505 Variable "<variable name>" already exists

Example:
select session_variable.create_variable('my_variable', 'text'::regtype);

session_variable.create_variable(variable_name, variable_type, initial_value)

The create_variable function creates a new variable with the specified initial value.

The created variable will be available in the current session and in sessions that are created after the committed invocation of session_variable.create_variable(variable_name, variable_type, initial_value). Existing sessions do not see the altered situation unless they invoke the session_variable.init() function.

Arguments
name type description
variable_name text Name of the variable to be created
variable_type regtype The datatype that can be stored in the variable
initial_value anyelement The initial value that will be loaded on session start and to which the variable will be reverted when the session_variable.init() function is invoked.

The value must have the type specified by variable_type.
Returns
  boolean true if ok
Exceptions
  22004 variable name must be filled
  22004 variable type must be filled
  2200F variable name must be filled
  22023 value must be of type <variable_type>, but is of type <the actual type>
  23505 Variable "<variable_name>" already exists

Example:
select session_variable.create_variable('my_date_variable', 'date'::regtype, '2015-07-16'::date);

session_variable.create_constant(constant_name, constant_type, value)

The create_constant function creates a new constant with the specified value.

A constant is just a variable, but it's content cannot be changed by a set(variable_name, value) function invocation.

The created constant will be available in the current session and in sessions that are created after the committed invocation of session_variable.create_constant(constant_name, constant_type, value). Existing sessions do not see the altered situation unless they invoke the session_variable.init() function.

Arguments
name type description
constant_name text Name of the constant to be created
constant_type regtype The datatype that will be stored in this constant
value anyelement The value that will be loaded on session start or inocation of the session_variable.init() function.

The value must have the type specified by constant_type.
Returns
  boolean true if ok
Exceptions
  22004 constant name must be filled
  22004 constant type must be filled
  2200F constant name must be filled
  22023 value must be of type <constant_type>, but is of type <the actual type>
  23505 Variable "<variable_name>" already exists

Example:
select session_variable.create_constant('my_environment_constant', 'text'::regtype, 'Production'::text);

session_variable.alter_value(variable_or_constant_name, value)

Alters the value of the contstant or the initial value of the variable.

The altered value will be available in the current session and in sessions that are created after the committed invocation of session_variable.alter_value(variable_or_constant_name, value). Existing sessions do not see the altered situation unless they invoke the session_variable.init() function.

Arguments
name type description
variable_or_constant_name text Name of the variable or constant of which the value is to be changed
value anyelement The value new (initial) value for the specified variable or constant

The value must have the type that was specified when the variable or constant was created.
Returns
  boolean true if ok
Exceptions
  02000 variable or constant "<variable_or_constant_name>" does not exist
  22004 variable or constant name must be filled
  2200F variable or constant name must be filled
  22023 value must be of type <type>, but is of type <the actual type>

Example:
select session_variable.alter_value('my_environment_constant', 'Development'::text);

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.

arguments
name type description
variable_or_constant_name text Name of the variable or constant to be removed
Returns
  boolean true if ok
Exceptions
  02000 variable or constant "<variable_or_constant_name>" does not exist
  22004 variable or constant name must be filled
  2200F variable or constant name must be filled

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.

No arguments
Returns
  integer the number of variabes and constants that are loaded
No exceptions

Example:
select session_variable.init();

session_variable.set(variable_name, value)

The set function changes the content of a variable.

The changed content will be visible in the current session only. The session_variable.set(variable_name, value) function will no affect any other session in any way. Invocation of the session_variable.init() function will undo the effect of any previously invoked session_variable.set(variable_name, value) function call.

Arguments
name type description
variable_name text Name of the variable to update
value anyelement The new content for the variable.

The value must have the type specified for the variable.
Returns
  boolean true if ok
Exceptions
  02000 variable "<variable_name>" does not exist
  0A000 constant "<variable_name>" cannot be set
  22004 variable name must be filled
  2200F variable name must be filled
  22023 value must be of type <variable_type>, but is of type <the actual type>

Example:
select session_variable.set('my_variable', 'a bit of text for my variable'::text);

session_variable.get(variable_or_constant_name, just_for_result_type)

Returns the session local content of the named variable or constant.

Arguments
name type description
variable_or_constant_name text Name of the variable or constant
just_for_result_type anyelement In postgres, a function can only return anyelement if it has got an anyelement argument. The type of the anyelement argument will be the same as the anyelement returntype. So we need an argument here with the type of the variable or constant.

The value must have the type specified for the variable or constant.
Returns
  anyelement The content of the variable or constant
Exceptions
  02000 variable or constant "<variable_or_constant_name>" does not exist
  22004 variable name must be filled
  22023 please invoke as session_variable.get(<variable_or_constant_name>, null::<type>)

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

session_variable.get_stable(variable_or_constant_name, just_for_result_type)

Does excactly the same as the session_variable.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.

Arguments
name type description
variable_or_constant_name text Name of the variable or constant
just_for_result_type anyelement In postgres, a function can only return anyelement if it has got an anyelement argument. The type of the anyelement argument will be the same as the anyelement returntype. So we need an argument here with the type of the variable or constant.

The value must have the type specified for the variable or constant.
Returns
  anyelement The content of the variable or constant
Exceptions
  02000 variable or constant "<variable_or_constant_name>" does not exist
  22004 variable name must be filled
  22023 please invoke as session_variable.get(<variable_or_constant_name>, null::<type>)

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

session_variable.get_constant(constant_name, just_for_result_type)

Returns the session local content of the named constant.

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().

Arguments
name type description
variable_or_constant_name text Name of the variable or constant
just_for_result_type anyelement In postgres, a function can only return anyelement if it has got an anyelement argument. The type of the anyelement argument will be the same as the anyelement returntype. So we need an argument here with the type of the constant.

The value must have the type specified for the variable or constant.
Returns
  anyelement The (cached) content of the constant
Exceptions
  02000 variable or constant "<variable_or_constant_name>" does not exist
  22004 constant name must be filled
  22023 please invoke as session_variable.get_constant(<constant_name>, null::<type>)
  42809 <constant_name> is not a constant

Example:
select session_variable.get_constant('my_constant', null::varchar[]);

session_variable.exists(variable_or_constant_name)

Returns the specified variable exists in the local session.

Arguments
name type description
Returns
  boolean true if the variable or constant exists in the current session.
Exceptions
  22004 variable name must be filled
  22023 please invoke as session_variable.exists(<variable_or_constant_name>)

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

session_variable.type_of(variable_or_constant_name)

Returns the type of the variable or constant

arguments
name type description
variable_or_constant_name text Name of the variable or constant
Returns
  regtype The type of the specified variable or constant
Exceptions
  02000 variable or constant "<variable_or_constant_name>" does not exist
  22004 variable or constant name must be filled

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

arguments
name type description
variable_or_constant_name text Name of the variable or constant
Returns
  regtype The type of the specified variable or constant
Exceptions
  02000 variable or constant "<variable_or_constant_name>" does not exist
  22004 variable or constant name must be filled

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

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.

arguments
name type description
do_truncate boolean Optional argument, default true.

If true then the first line returned will be "truncate table session_variable.variables;". If false then the truncate statement will not be returned and all definitions will be appended with " where not session_variable.exists()"
Returns
  setof text The lines that together form the script.
Exceptions
none

Example:
select session_variable.dump();

session_variable.get_session_variable_version()

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

arguments
none
Returns
  text The code version of the session_variable extension.
Exceptions
none

Example:
select session_variable.get_session_variable_version();

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.

arguments
none
Returns
  boolean true if a function called session_variable.variable_initialisation() is currently being invoked on behalf of session_vairable initialisation code. In all other cases the return will be false.
Exceptions
none

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.

arguments
none
Returns
  void Or anything you like. The result will be ignored by session_variable initialisation code
Exceptions
  Make sure you don't throw any!

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 protected 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".

Save / restore

Session variables are stored in the session_variable.variables table, which can be saved and restored as any other table. Restored values will be visible to all sessions that started after the restore committed. Sessions that were started before the restore will still see the old (session local!) content unless they invoke session_variable.init().

Release notes

version 2

In version 1, the initial values of variables and constants were stored in the session_variable.variables table in a bytea representing a memory image of the content. This appeared problematic when copying data from one database to another as in array types and composite types oids are present in the memory image. So in version 2 the initial_value column is altered to a text column and serialization and deserialization is now routed via the typinput and typoutput functions. So now a database dump is portable (provided that the receiving database has got all user defined types available).

Returning the previous value in the set() function and the alter() function proved not very useful and did impose some overhead. So in version 2 these functions return just a boolean, which will be 'true' in all cases.

upgrade to version 2

On the command line:

git pull
make clean
make
sudo make install

Then in a new database session

alter extension session_variable update;

Ps.
The database will keep using the version 1 implementation of the extension until the "alter extension session_variable update;" command is executed. Make sure you do not restore any dump of the session_variable.variables table that was created before the "alter extension session_variable update;" into a database that already executes version 2.

You can’t perform that action at this time.