Session wide variables for PostgreSQL
The pg_variables extension provides functions to work with variables of
various types. Created variables live only in the current user session.
By default, created variables are not transactional (i.e. they are not affected
by BEGIN
, COMMIT
or ROLLBACK
statements). This, however, is customizable
by argument is_transactional
of pgv_set()
:
SELECT pgv_set('vars', 'int1', 101);
BEGIN;
SELECT pgv_set('vars', 'int2', 102);
ROLLBACK;
SELECT * FROM pgv_list() order by package, name;
package | name | is_transactional
---------+------+------------------
vars | int1 | f
vars | int2 | f
But when variable is created with flag is_transactional:
BEGIN;
SELECT pgv_set('vars', 'trans_int', 101, true);
SAVEPOINT sp1;
SELECT pgv_set('vars', 'trans_int', 102, true);
ROLLBACK TO sp1;
COMMIT;
SELECT pgv_get('vars', 'trans_int', NULL::int);
pgv_get
---------
101
You can aggregate variables into packages. This is done to be able to have variables with different names or to quickly remove the whole batch of variables. If the package becomes empty, it is automatically deleted.
This extension available under the license similar to PostgreSQL.
Typical installation procedure may look like this:
$ cd pg_variables
$ make USE_PGXS=1
$ sudo make USE_PGXS=1 install
$ make USE_PGXS=1 installcheck
$ psql DB -c "CREATE EXTENSION pg_variables;"
The functions provided by the pg_variables extension are shown in the tables below.
To use pgv_get() function required package and variable must exist. It is necessary to set variable value with pgv_set() function to use pgv_get() function.
If a package does not exist you will get the following error:
SELECT pgv_get('vars', 'int1', NULL::int);
ERROR: unrecognized package "vars"
If a variable does not exist you will get the following error:
SELECT pgv_get('vars', 'int1', NULL::int);
ERROR: unrecognized variable "int1"
pgv_get() function checks the variable type. If the variable type does not match with the function type you will get the following error:
SELECT pgv_get('vars', 'int1', NULL::text);
ERROR: variable "int1" requires "integer" value
Function | Returns |
---|---|
pgv_set(package text, name text, value anynonarray, is_transactional bool default false) |
void |
pgv_get(package text, name text, var_type anynonarray, strict bool default true) |
anynonarray |
Function | Returns |
---|---|
pgv_set(package text, name text, value anyarray, is_transactional bool default false) |
void |
pgv_get(package text, name text, var_type anyarray, strict bool default true) |
anyarray |
pgv_set
arguments:
package
- name of the package, it will be created if it does not exist.name
- name of the variable, it will be created if it does not exist.
pgv_set
fails if the variable already exists and its transactionality doesn't
match is_transactional
argument.value
- new value for the variable. pgv_set
fails if the variable already
exists and its type does not match new value's type.is_transactional
- transactionality of the newly created variable. By
default it is set to false.pgv_get
arguments:
package
- name of the existing package. If the package does not exist result
depends on strict
argument: if it is false then pgv_get
returns NULL,
otherwise it fails.name
- name of the the existing variable. If the variable doesn't exist
result depends on strict
argument: if it is false then pgv_get
returns NULL,
otherwise it fails.var_type
- type of the existing variable. It is necessary to pass it to
determine correct return type.strict
- pass false if pgv_get
shouldn't raise an error if requested
variable or package do not exist. By default strict
is set to true.Function | Returns |
---|---|
pgv_set_int(package text, name text, value int, is_transactional bool default false) |
void |
pgv_get_int(package text, name text, strict bool default true) |
int |
Function | Returns |
---|---|
pgv_set_text(package text, name text, value text, is_transactional bool default false) |
void |
pgv_get_text(package text, name text, strict bool default true) |
text |
Function | Returns |
---|---|
pgv_set_numeric(package text, name text, value numeric, is_transactional bool default false) |
void |
pgv_get_numeric(package text, name text, strict bool default true) |
numeric |
Function | Returns |
---|---|
pgv_set_timestamp(package text, name text, value timestamp, is_transactional bool default false) |
void |
pgv_get_timestamp(package text, name text, strict bool default true) |
timestamp |
Function | Returns |
---|---|
pgv_set_timestamptz(package text, name text, value timestamptz, is_transactional bool default false) |
void |
pgv_get_timestamptz(package text, name text, strict bool default true) |
timestamptz |
Function | Returns |
---|---|
pgv_set_date(package text, name text, value date, is_transactional bool default false) |
void |
pgv_get_date(package text, name text, strict bool default true) |
date |
Function | Returns |
---|---|
pgv_set_jsonb(package text, name text, value jsonb, is_transactional bool default false) |
void |
pgv_get_jsonb(package text, name text, strict bool default true) |
jsonb |
The following functions are provided by the extension to work with collections of any type.
Function | Returns |
---|---|
pgv_set_elem(package text, name text, key int, value anyelement, is_transactional bool default false) |
void |
pgv_set_elem(package text, name text, key text, value anyelement, is_transactional bool default false) |
void |
pgv_get_elem(package text, name text, key int, val_type anyelement) |
anyelement |
pgv_get_elem(package text, name text, key text, val_type anyelement) |
anyelement |
pgv_remove_elem(package text, name text, key int) |
void |
pgv_remove_elem(package text, name text, key text) |
void |
pgv_set_elem
arguments:
package
- name of the package, it will be created if it does not exist.name
- name of the collection variable, it will be created if it does not exist.
pgv_set_elem
fails if the variable already exists and its transactionality doesn't
match is_transactional
argument.key
- key of collection element. Its type can be either int
or text
.
Inside one collection only keys of the same type are allowed (meaning there
can't exist a collection with some int
and some text
keys).
If an element with specified key already exists set its value to a new one.value
- value of collection element. pgv_set_elem
fails if the collection
already exists and its value type does not match new value's type.is_transactional
- transactionality of the newly created variable. By
default it is set to false.pgv_get_elem
arguments:
package
- name of the existing package. If the package does not exist, this
function fails.name
- name of the the existing variable. If the variable does not exist,
this function fails. If the specified variable is not a collection,
this function fails.key
- key of a required element. If there is no element with specified key
in this collections returns NULL.val_type
- required element type. It is necessary to pass it to
determine correct return type.pgv_remove_elem
arguments:
package
- name of the existing package. If the package does not exist, this
function fails.name
- name of the existing variable. If the variable does not exist, this
function fails.key
- key of required element. If there is no element with specified key
in this collection does nothing.Important! |
---|
Collections initialized with pgv_set_elem
and with pgv_insert
(described
below) SHOULD NOT be considered compitable.
The following functions are provided by the extension to work with collections of record types.
To use pgv_update(), pgv_delete() and pgv_select() required package and variable must exist. Otherwise an error will be raised. It is necessary to set variable with pgv_insert() function to use these functions.
pgv_update(), pgv_delete() and pgv_select() functions check the variable type. If the variable type is not a record or a composite type with the same columns an error will be raised.
Function | Returns | Description |
---|---|---|
pgv_insert(package text, name text, r record, is_transactional bool default false) |
void |
Inserts a record to the variable collection. If package or variable do not exist they will be created. The first column of r will be primary key. If a record with the same primary key exists an error will be raised. If this collection variable has different structure from passed value an error will be raised. |
pgv_update(package text, name text, r record) |
boolean |
Updates a record with the corresponding primary key (the first column of r is primary key). Returns true if a record was found. If this collection variable has different structure from passed value an error will be raised. |
pgv_delete(package text, name text, value anynonarray) |
boolean |
Deletes a record with the corresponding primary key. Returns true if a record was found and false otherwise. |
pgv_select(package text, name text) |
set of record |
Returns all records from collection variable. |
pgv_select(package text, name text, value anynonarray) |
record |
Returns record with the corresponding primary key. |
pgv_select(package text, name text, value anyarray) |
set of record |
Returns records with the corresponding primary keys. |
The following functions are provided by the extension to use iterator to traverse
collection variables. These functions work with collections initialized with both
pgv_set_elem
and pgv_insert
.
Function | Returns | Description |
---|---|---|
pgv_first(package text, name text, key_type anyelement) |
anyelement |
Returns first key from collection variable. Collections are sorted by key in ascending order. Key_type is needed to determine return value (like in pgv_get ). If passed name of non-collection variable, this function will fail. |
pgv_last(package text, name text, key_type anyelement) |
anyelement |
Returns last key from collection variable. Collections are sorted by key in ascending order. Key_type is needed to determine return value (like in pgv_get ). If passed name of non-collection variable, this function will fail. |
pgv_next(package text, name text, key anyelement) |
anyelement |
Returns next key from collection variable. Passed key may not exist in collection. Returns NULL if used on the last key in collection. Collections are sorted by key in ascending order. If passed name of non-collection variable, this function will fail. |
pgv_prior(package text, name text, key anyelement) |
anyelement |
Returns prior key from collection variable. Passed key may not exist in collection. Returns NULL if used on the first key in collection. Collections are sorted by key in ascending order. If passed name of non-collection variable, this function will fail. |
pgv_count(package text, name text) |
integer |
Returns amount of elements in collection. If passed name of non-collection variable, this function will fail. |
Function | Returns | Description |
---|---|---|
pgv_exists(package text, name text) |
bool |
Returns true if package and variable exists and false otherwise. |
pgv_exists(package text) |
bool |
Returns true if package exists and false otherwise. |
pgv_remove(package text, name text) |
void |
Removes the variable with the corresponding name. Required package and variable must exist, otherwise an error will be raised. |
pgv_remove(package text) |
void |
Removes the package with the corresponding name and all its variables. Required package must exist, otherwise an error will be raised. |
pgv_free() |
void |
Removes all packages and variables. |
pgv_list() |
table(package text, name text, is_transactional bool) |
Returns a set of records of existing packages and variables. |
pgv_stats() |
table(package text, allocated_memory bigint) |
Returns a list of existing packages and used memory in bytes. |
Note that pgv_stats() works only with the PostgreSQL 9.6 and newer.
As was said in general collection section, collections are stored in ascending
order. In case of text
keys you need some collation to determine the order of
elements. If no collation is specified when inserting first element of collection,
then the default collation will be used. Otherwise, the specified collation will
be used.
All set retunrning functions except for pgv_select('package', 'variable')
fix their return results at first FETCH from cursor and are not affected by
further data manipulation.
The results of pgv_select('package', 'variable')
are received dynamically
and are affected by transacions/changes in collection. Cursors for pgv_select
for a transactional collectional look at a snapshot of collection when
first FETCH was executed, consider changes that were made in the same transacion
and in committed subtransactions.
It is easy to use functions to work with scalar and array variables:
SELECT pgv_set('vars', 'int1', 101);
SELECT pgv_set('vars', 'text1', 'text variable'::text);
SELECT pgv_get('vars', 'int1', NULL::int);
pgv_get_int
-------------
101
SELECT SELECT pgv_get('vars', 'text1', NULL::text);
pgv_get
---------------
text variable
SELECT pgv_set('vars', 'arr1', '{101,102}'::int[]);
SELECT pgv_get('vars', 'arr1', NULL::int[]);
pgv_get
-----------
{101,102}
Let's assume we have a tab table:
CREATE TABLE tab (id int, t varchar);
INSERT INTO tab VALUES (0, 'str00'), (1, 'str11');
Then you can use functions to work with collection variables:
SELECT pgv_insert('vars', 'r1', tab) FROM tab;
SELECT pgv_select('vars', 'r1');
pgv_select
------------
(1,str11)
(0,str00)
SELECT pgv_select('vars', 'r1', 1);
pgv_select
------------
(1,str11)
SELECT pgv_select('vars', 'r1', 0);
pgv_select
------------
(0,str00)
SELECT pgv_select('vars', 'r1', ARRAY[1, 0]);
pgv_select
------------
(1,str11)
(0,str00)
SELECT pgv_delete('vars', 'r1', 1);
SELECT pgv_select('vars', 'r1');
pgv_select
------------
(0,str00)
You can get a list of all packages and variables:
SELECT * FROM pgv_list() order by package, name;
package | name | is_transactional
---------+-------+------------------
vars | arr1 | f
vars | int1 | f
vars | r1 | f
vars | text1 | f
And get used memory in bytes:
SELECT * FROM pgv_stats() order by package;
package | allocated_memory
---------+------------------
vars | 49152
You can delete variables or whole packages:
SELECT pgv_remove('vars', 'int1');
SELECT pgv_remove('vars');
You can delete all packages and variables:
SELECT pgv_free();
If you want variables with support of transactions and savepoints, you should
add flag is_transactional = true
as the last argument in functions pgv_set()
or pgv_insert()
.
Following use cases show behavior of transactional variables:
SELECT pgv_set('pack', 'var_text', 'before transaction block'::text, true);
BEGIN;
SELECT pgv_set('pack', 'var_text', 'before savepoint'::text, true);
SAVEPOINT sp1;
SELECT pgv_set('pack', 'var_text', 'savepoint sp1'::text, true);
SAVEPOINT sp2;
SELECT pgv_set('pack', 'var_text', 'savepoint sp2'::text, true);
RELEASE sp2;
SELECT pgv_get('pack', 'var_text', NULL::text);
pgv_get
---------------
savepoint sp2
ROLLBACK TO sp1;
SELECT pgv_get('pack', 'var_text', NULL::text);
pgv_get
------------------
before savepoint
ROLLBACK;
SELECT pgv_get('pack', 'var_text', NULL::text);
pgv_get
--------------------------
before transaction block
If you create a transactional variable after BEGIN
or SAVEPOINT
statements
and then rollback to previous state - variable will not exist:
BEGIN;
SAVEPOINT sp1;
SAVEPOINT sp2;
SELECT pgv_set('pack', 'var_int', 122, true);
RELEASE SAVEPOINT sp2;
SELECT pgv_get('pack', 'var_int', NULL::int);
pgv_get
---------
122
ROLLBACK TO sp1;
SELECT pgv_get('pack','var_int', NULL::int);
ERROR: unrecognized variable "var_int"
COMMIT;
You can undo removal of a transactional variable by ROLLBACK
, but if you remove
a whole package, all regular variables will be removed permanently:
SELECT pgv_set('pack', 'var_reg', 123);
SELECT pgv_set('pack', 'var_trans', 456, true);
BEGIN;
SELECT pgv_free();
SELECT * FROM pgv_list();
package | name | is_transactional
---------+------+------------------
(0 rows)
-- Memory is allocated yet
SELECT * FROM pgv_stats();
package | allocated_memory
---------+------------------
pack | 24576
ROLLBACK;
SELECT * FROM pgv_list();
package | name | is_transactional
---------+-----------+------------------
pack | var_trans | t
If you created transactional variable once, you should use flag is_transactional
every time when you want to change variable value by functions pgv_set()
,
pgv_insert()
and deprecated setters (i.e. pgv_set_int()
). If you try to
change this option, you'll get an error:
SELECT pgv_insert('pack', 'var_record', row(123::int, 'text'::text), true);
SELECT pgv_insert('pack', 'var_record', row(456::int, 'another text'::text));
ERROR: variable "var_record" already created as TRANSACTIONAL
Functions pgv_update()
and pgv_delete()
do not require this flag.
Some examples of using collection variables and iterator functions:
SELECT pgv_set_elem('pack', 'var', 1, 1);
SELECT pgv_set_elem('pack', 'var', 5, 5);
SELECT pgv_set_elem('pack', 'var', 10, 10);
SELECT pgv_first('pack', 'var', NULL::int);
pgv_first
-----------
1
SELECT pgv_last('pack', 'var', NULL::int);
pgv_last
----------
10
SELECT pgv_next('pack', 'var', pgv_first('pack', 'var', NULL::int));
pgv_next
----------
5
SELECT pgv_prior('pack', 'var', pgv_last('pack', 'var', NULL::int));
pgv_prior
-----------
5
SELECT pgv_prior('pack', 'var', pgv_first('pack', 'var', NULL::int));
pgv_prior
-----------
SELECT pgv_next('pack', 'var', pgv_last('pack', 'var', NULL::int));
pgv_prior
-----------
SELECT pgv_next('pack', 'var', 3);
pgv_next
----------
5
SELECT pgv_prior('pack', 'var', 3);
pgv_prior
-----------
1
SELECT pgv_get_elem('pack', 'var', pgv_last('pack', 'var', NULL::int), NULL::int);
pgv_get_elem
--------------
10
SELECT pgv_remove_elem('pack', 'var', pgv_last('pack', 'var', NULL::int));
pgv_remove_elem
-----------------
SELECT pgv_get_elem('pack', 'var', pgv_last('pack', 'var', NULL::int), NULL::int);
pgv_get_elem
--------------
5
(1 row)
Some examples of how does collation affect order of elements in collection. These also show how to iterate over whole collection with pl/pgsql loops.
SELECT pgv_set_elem('pack', 'var1', 'а' COLLATE "ru_RU", 'а'::text);
SELECT pgv_set_elem('pack', 'var1', 'д' COLLATE "ru_RU", 'д'::text);
SELECT pgv_set_elem('pack', 'var1', 'е' COLLATE "ru_RU", 'е'::text);
SELECT pgv_set_elem('pack', 'var1', 'ё' COLLATE "ru_RU", 'ё'::text);
SELECT pgv_set_elem('pack', 'var1', 'ж' COLLATE "ru_RU", 'ж'::text);
SELECT pgv_set_elem('pack', 'var1', 'я' COLLATE "ru_RU", 'я'::text);
DO
$$
DECLARE
iter text;
BEGIN
iter := pgv_first('pack', 'var1', NULL::text);
WHILE iter IS NOT NULL LOOP
RAISE NOTICE '%', pgv_get_elem('pack', 'var1', iter, NULL::text);
iter := pgv_next('pack', 'var1', iter);
END LOOP;
END;
$$;
NOTICE: а
NOTICE: д
NOTICE: е
NOTICE: ё
NOTICE: ж
NOTICE: я
SELECT pgv_set_elem('pack', 'var2', 'а' COLLATE "C", 'а'::text);
SELECT pgv_set_elem('pack', 'var2', 'д' COLLATE "C", 'д'::text);
SELECT pgv_set_elem('pack', 'var2', 'е' COLLATE "C", 'е'::text);
SELECT pgv_set_elem('pack', 'var2', 'ё' COLLATE "C", 'ё'::text);
SELECT pgv_set_elem('pack', 'var2', 'ж' COLLATE "C", 'ж'::text);
SELECT pgv_set_elem('pack', 'var2', 'я' COLLATE "C", 'я'::text);
DO
$$
DECLARE
iter text;
BEGIN
iter := pgv_first('pack', 'var2', NULL::text);
WHILE iter IS NOT NULL LOOP
RAISE NOTICE '%', pgv_get_elem('pack', 'var2', iter, NULL::text);
iter := pgv_next('pack', 'var2', iter);
END LOOP;
END;
$$;
NOTICE: а
NOTICE: д
NOTICE: е
NOTICE: ж
NOTICE: я
NOTICE: ё