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.
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<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');
- 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; 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);
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);
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);
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);
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');
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();
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);
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);
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);
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[]);
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');
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');
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');
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();
Returns the code version of the extension, currently '3.3'.
| arguments | ||
|---|---|---|
| none | ||
| Returns | ||
| text | The code version of the session_variable extension. | |
| Exceptions | ||
| none | ||
Example:
select session_variable.get_session_variable_version();
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 | ||
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;
$$;
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".
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().
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.
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.
Added functions get_stable() and get_constant().
Removed the .so file extension from the function definitions so they might work on Windows as well.
Some textual changes in the session_variable.c file to keep cppcheck fund bug hapy. See: #5
Adapted the new Postgres rule that extensions shalt not use 'create of not exists' and
'create or replace' constructs.
Discontinued support for session_variables version 1.0.